Stored Procedure to Select AND Return a Value?

[peggasus88]

Hi,

Newbie here. Can someone please show me how to SELECT a value and RETURN that value in the same stored procedure?

My stored procedure:
Alter PROCEDURE spGetQuarter_byDate
@Date datetime,
@Quarter varchar(20) Output
AS
Set @Quarter = (SELECT Quarter FROM Quarters WHERE @Date BETWEEN StartDate AND EndDate)
RETURN @Quarter


I'm getting the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '2002 Q2' to a column of data type int.

I have no idea where the 'data type int' is coming from since I have no ints in my table!

Please help. I don't want to be thinking about this when I'm watching Star Wars!

~*Peggy*~

 

[bperry]

'Return' in an SP can only return an integer.
So it is trying to convert @quarter from a varchar to an int and failing.

But you do not need to 'return' it. Since @quarter is already set up in your SP as an output parameter, the SP will pass the @quarter value back to the calling software. (Note that when your client program calls the SP, it must do it in a way to receive that output parm back from the SP.)

rgrds, etc
bperry

 

[bperry]

P.S.
So in your SP, just have
'RETURN',
not 'RETURN @quarter'
bperry

 

[peggasus88]

Hi 'bperry',

I'm not getting the error message anymore, but I am getting an unexpected return value of '0'. In my code, I am simply trying to set a variable value to the return value of the SP. I might be calling the SP wrong? Can you please show me some code snippets to call a SP that returns a non-int value? I am using Visual InterDev 6.0 and I have successfully returned an int before, but perhaps it is different for a non-int?

Thanks for your help. I really appreciate it.

-- Peggy --

 

[tlbroadbent]

bperry is correct. You don't use Return to obtain a parameter value. You execute the SP with an output parameter just as you create the SP with an output parameter.

Declare @Quarter varchar(20)

Exec spGetQuarter_byDate
'2002-05-12',
@Quarter Output

Print @Quarter

Note the output parameter name doesn't need to be the same name declared in the SP. The follwoing also works.

Declare @Qtr varchar(20)

Exec spGetQuarter_byDate
'2002-05-12',
@Qtr Output

Print @Qtr
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Okay. I guess your SP now now looks like this, right?

[tt]
Alter PROCEDURE spGetQuarter_byDate
@Date datetime,
@Quarter varchar(20) Output
AS
Set @Quarter =
(SELECT Quarter FROM Quarters
WHERE @Date BETWEEN StartDate AND EndDate)
RETURN
[/tt]


I think what you are missing is that in your calling client program (ASP?), your SP is not returning the @quarter output parameter back to a 'return' value. Rather, @quarter is being sent back thru an 'output parameter', and your client process, when it calls the SP, must set up to receive this output parm.

If you'd like to post your clinet code, perhaps someone here will take a look at it for you.


In my code, I am simply trying to set a variable value to the return value of the SP
So you see why this statement is wrong? Ans: you cannot use the Return value to return anything except an int.

 

[peggasus88]

Thanks for the help! Yeah, I was thinking about it incorrectly, and I have now gotten it to work.

 

[bperry]

oKAY, that's really great. Good luck with it.