Output parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Output parameter

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 12:52:55

I have created a what I thought was a parameter that I would build in my stored procedure and it would output to my report based on different selections the user selected. I am just testing right now so not complete but the output parameter is "@RptTitle and I reference it in my first if statement.

My problem is I am using this stored procedure through Crystal Reports. When the report runs it asks me for in input for the @RptTitle parameter when its supposed to build it based on the other two parameters. Can someone tell me where I am going wrong?

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as

/*if both are null return all tasks*/
if @TaskLookup5 is null and @TaskLookup6 is null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
select @Rpttitle = 'All Combinations'

/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where tasklookup5 = @TaskLookup5 and tasklookup6 = @TaskLookup6

/*if just tasklookup5 has a filter then only filter on 5.*/
if @TaskLookup5 is not null and @TaskLookup6 is null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where tasklookup5 = @TaskLookup5

/*If just tasklookup6 has a filter then only filter on 6.*/
if @TaskLookup6 is not null and @TaskLookup5 is null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where tasklookup6 = @TaskLookup6

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 12:58:04

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as



select @Rpttitle = 'All Combinations'


/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this

declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 13:00:43

I have redundant code because there are so many different options the user can select and they are not dependent on each other. If they select task 5 option and not task 6 or vise versa I need to handle this and change the title of the report. When they actually select tasks I am going to put the names of the tasks they select in the parameter. Don't I need to keep it all separate so it names the report based on what options the user selects?

quote:

Originally posted by visakh16

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as



select @Rpttitle = 'All Combinations'


/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this

declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 13:02:29

quote:
Originally posted by sross81

I have redundant code because there are so many different options the user can select and they are not dependent on each other. If they select task 5 option and not task 6 or vise versa I need to handle this and change the title of the report. When they actually select tasks I am going to put the names of the tasks they select in the parameter. Don't I need to keep it all separate so it names the report based on what options the user selects?

quote:
Originally posted by visakh16

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as

select @Rpttitle = 'All Combinations'

/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this
declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

Thanks in Advance!
Sherri

but you're setting name only at 1 place werent you?

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 13:05:28

Yes I was but I mentioned that I was only testing it on that one but it wasn't done yet. I just wanted to see if I could get anything returned to begin with....

When I link to my stored procedure through crystal it just asks me for a value for the output parameter and I wasn't sure why since its supposed to output based on the other parameters the user selects.

This is what happens when I run
exec GetITTaskRpt null, null

Msg 201, Level 16, State 4, Procedure GetITTaskRpt, Line 0
Procedure or function 'GetITTaskRpt' expects parameter '@RptTitle', which was not supplied.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

I have redundant code because there are so many different options the user can select and they are not dependent on each other. If they select task 5 option and not task 6 or vise versa I need to handle this and change the title of the report. When they actually select tasks I am going to put the names of the tasks they select in the parameter. Don't I need to keep it all separate so it names the report based on what options the user selects?

quote:
Originally posted by visakh16

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as

select @Rpttitle = 'All Combinations'

/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this
declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

Thanks in Advance!
Sherri

but you're setting name only at 1 place werent you?

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 13:12:02

quote:
Originally posted by sross81

Yes I was but I mentioned that I was only testing it on that one but it wasn't done yet. I just wanted to see if I could get anything returned to begin with....

When I link to my stored procedure through crystal it just asks me for a value for the output parameter and I wasn't sure why since its supposed to output based on the other parameters the user selects.

This is what happens when I run
exec GetITTaskRpt null, null

Msg 201, Level 16, State 4, Procedure GetITTaskRpt, Line 0
Procedure or function 'GetITTaskRpt' expects parameter '@RptTitle', which was not supplied.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

I have redundant code because there are so many different options the user can select and they are not dependent on each other. If they select task 5 option and not task 6 or vise versa I need to handle this and change the title of the report. When they actually select tasks I am going to put the names of the tasks they select in the parameter. Don't I need to keep it all separate so it names the report based on what options the user selects?

quote:
Originally posted by visakh16

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as

select @Rpttitle = 'All Combinations'

/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this
declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

Thanks in Advance!
Sherri

but you're setting name only at 1 place werent you?

Thanks in Advance!
Sherri

why do you want it as an output parameter. is it better to return it as an extra field and use it in reports?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 13:15:38

something like this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30)--,
--@RptTitle varchar(100) OUTPUT) as


/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6,
case when @TaskLookup5 is not null and @TaskLookup6 is not null
     then value1
      when...
      then value2
...
end as reporttitle
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

and then use this field in reports?

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 13:18:26

I actually only had ever used output parameters before and was not aware I could return as an extra field in the report. I guess I must have used the output parameter in a different way than I am trying to use it now.

Is there a way I can return extra fields in my stored procedure within those if statements. For example in the first one where both task lookup5 and 6 are null I want the title to say all combinations but if they select just something for task lookup 5 and nothing for 6 like in if statement 2 I was just going to make the title say whatever the option they seleced for 5. I know how to concatenate and build the title but I just am not sure how to set it up so something will return.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

Yes I was but I mentioned that I was only testing it on that one but it wasn't done yet. I just wanted to see if I could get anything returned to begin with....

When I link to my stored procedure through crystal it just asks me for a value for the output parameter and I wasn't sure why since its supposed to output based on the other parameters the user selects.

This is what happens when I run
exec GetITTaskRpt null, null

Msg 201, Level 16, State 4, Procedure GetITTaskRpt, Line 0
Procedure or function 'GetITTaskRpt' expects parameter '@RptTitle', which was not supplied.

quote:
Originally posted by visakh16

quote:
Originally posted by sross81

I have redundant code because there are so many different options the user can select and they are not dependent on each other. If they select task 5 option and not task 6 or vise versa I need to handle this and change the title of the report. When they actually select tasks I am going to put the names of the tasks they select in the parameter. Don't I need to keep it all separate so it names the report based on what options the user selects?

quote:
Originally posted by visakh16

there are lot of redundant code. you just need only this

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30),
@RptTitle varchar(100) OUTPUT) as

select @Rpttitle = 'All Combinations'

/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

then invoke it like this
declare @retval varchar(100)
exec GetITTaskRpt tasklookup5value,tasklookup6value,@retval OUT
select @retval

this will give you back returned value

Thanks in Advance!
Sherri

but you're setting name only at 1 place werent you?

Thanks in Advance!
Sherri

why do you want it as an output parameter. is it better to return it as an extra field and use it in reports?

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 13:26:56

is this what you want?

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30)--,
--@RptTitle varchar(100) OUTPUT) as


/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6,
case when @TaskLookup5 is not null and @TaskLookup6 is not null
     then 'TaskLookup5: ' + @TaskLookup5 + ' TaskLookup6: '+@TaskLookup6
      when @TaskLookup5 is not null and @TaskLookup6 is null
      then 'TaskLookup5: ' + @TaskLookup5
      when @TaskLookup5 is null and @TaskLookup6 is not  null
      then 'TaskLookup6: '+@TaskLookup6
      when @TaskLookup5 is null and @TaskLookup6 is null
      then 'All Combinations'
end as reporttitle
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

it would be even more easier if you do this inside crystal reports. i'vent worked with crystal reports but sql reporting have facility to access parameters inside report expressions so i think there would similar ways in crystal report also.

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-23 : 13:36:30

Thanks I will try this. I just got pulled off on another project so I will have to return to this later today.

I was trying to do it in crystal and it wasn't working right. I may attempt that more as well or check with crystal support.

Thanks for your help.

quote:

Originally posted by visakh16

is this what you want?

alter procedure GetITTaskRpt (@TaskLookup5 varchar(30),@TaskLookup6 varchar(30)--,
--@RptTitle varchar(100) OUTPUT) as


/*if both have values to filter on filter on both values*/
if @TaskLookup5 is not null and @TaskLookup6 is not null
select wo_num,task,opendate,clsddate,respons,tasklookup5,tasklookup6,
case when @TaskLookup5 is not null and @TaskLookup6 is not null
     then 'TaskLookup5: ' + @TaskLookup5 + ' TaskLookup6: '+@TaskLookup6
      when @TaskLookup5 is not null and @TaskLookup6 is null
      then 'TaskLookup5: ' + @TaskLookup5
      when @TaskLookup5 is null and @TaskLookup6 is not  null
      then 'TaskLookup6: '+@TaskLookup6
      when @TaskLookup5 is null and @TaskLookup6 is null
      then 'All Combinations'
end as reporttitle
from tasks
where (tasklookup5 = @TaskLookup5 or @TaskLookup5 is null)
and (tasklookup6 = @TaskLookup6 or @TaskLookup6 is null)

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources