Print Page | Close Window

Call a stored procedure in crystal report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18038
Printed Date: 18 May 2024 at 11:45am


Topic: Call a stored procedure in crystal report
Posted By: Frankn
Subject: Call a stored procedure in crystal report
Date Posted: 20 Nov 2012 at 9:04am
Hi,
I have a stored procedure which I need to pass a parameter from CR but when I call it by
{call "datatest"."dbo"."ZZ_SPECCHANGE";1('''dbo.zzxdzgnh.design_num''')}

it gives me error: "incorrect syntax near the keyword exec"

USE Datatest
GO
CREATE PROCEDURE DBO.ZZ_SPECCHANGE
@idesign_num varchar(30))
RETURNS varchar(75)
AS
BEGIN
DECLARE @FR NUMERIC(8, 2)
DECLARE @WB NUMERIC(8, 2)
DECLARE @FR_WB NUMERIC(8, 2)
DECLARE @output VARCHAR(75)
...

select @FR=(CASE d.SAMPLE_BK
               WHEN 1 THEN d.SIZE01
               WHEN 2 THEN d.SIZE02
               WHEN 3 THEN d.SIZE03
...
               WHEN 23 THEN d.SIZE23
               ELSE d.SIZE24 END)
FROM zzxdzpom d join zzxdzgnh h
on d.DZHDRFKEY = h.pkey and h.design_num = @idesign_num and d.pom_code like 'FRTR%'
if @FR is null set @FR = '0'

select @WB=(CASE d.SAMPLE_BK
               WHEN 1 THEN d.SIZE01
               WHEN 2 THEN d.SIZE02
               WHEN 3 THEN d.SIZE03
...
               WHEN 23 THEN d.SIZE23
               ELSE d.SIZE24 END)
FROM zzxdzpom d join zzxdzgnh h
on d.DZHDRFKEY = h.pkey and h.design_num = @idesign_num and d.pom_code like 'WAIST%'
if @WB is null set @WB = '0'

SET @FR_WB = @FR + @WB
SET @wholenumber = CAST(@FR_WB AS INT)
SET @decimal = @FR_WB - @wholenumber
SET @multiple =
[covert decimal to fraction.....]
ELSE
BEGIN
        SET @output = convert(varchar(75),@wholenumber)
END

RETURN (@output);
END

and also I could not insert this formula on SQL Expression, Any help or suggestion please?

Thanks


Replies:
Posted By: lockwelle
Date Posted: 21 Nov 2012 at 3:19am
I have never tried to manipulate the call in CR.  I tell CR that I want to use a stored proc, it then prompts me for parameters, and then it sets up the call...all I need to do is change the text for the parameter screen....
so I'm confused as to what you are asking CR to do...
why are tell CR to pass something to it...since what you are passing is not a parameter (it doesn't vary, and there is no user input), why not just have the sp grab the value you want and remove the parameter?
 
 

Posted By: Frankn
Date Posted: 27 Nov 2012 at 5:42am
Thanks for reply, in fact what I need to do is:
Extracting a size within a table base on contents of a field for example on field sample_size I will read size 23, than on column 23 I will read the content which is decimal(2.5), I need to convert that number to fraction
(2 1/2) and then populate it on my crystal report, what would be the best solution to do this?

Posted By: lockwelle
Date Posted: 27 Nov 2012 at 7:28am
in all honesty, I don't know.
 
you are just displaying the value 2 1/2, as opposed to setting a control to that size, right?
 
as a rule of thumb, I try to do as much as possible in the stored proc, since it gives much more power and versatility than CR.

Posted By: Frankn
Date Posted: 27 Nov 2012 at 7:49am
Yes I need to populate value 2 1/2 on CR, on the first post I've made the SP and it works fine on SSRS but my problem is how to call it on CR, on CR I have design_num and base on design numbers I should show what are the sizes(ex. 2 1/2), I've just confused how to pass design_num to SP and return Size to CR.

regards


Print Page | Close Window