Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Development » Procedure return value problem
Add to briefcase

Procedure return value problem Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
nagarajan.tiruppur
Posted Tuesday, December 15, 2009 10:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 18, 2010 5:21 AM
Points: 22, Visits: 45
Hi

I have a SP ....

CREATE PROCEDURE
sp_GET_NEXT_SEQU (@TABLE_NAME char(25))
AS
DECLARE @LV_TEST numeric(9,0)

BEGIN TRANSACTION
SELECT @LV_TEST = SQ_COUNT FROM F_Sequ LOCK WHERE SQ_TABLE=@TABLE_NAME
UPDATE F_Sequ SET SQ_COUNT = SQ_COUNT+1 WHERE SQ_TABLE=@TABLE_NAME
SELECT SQ_COUNT FROM F_Sequ WHERE SQ_TABLE=@TABLE_NAME
COMMIT TRANSACTION

But how can i will assign return value (from select statment) to variable

like

declare @sequ numeric(9,0)
select @sequ = exec sp_GET_NEXT_SEQU 'TABLE1'

?

Note: I will not add RETURN, because this is already using in so many places in various applications ...
Post #834853
arun.sas
Posted Tuesday, December 15, 2009 11:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
Hi,
Use the output parameter in the procedure and ref the BOL for set the output parameter in the SP.
Post #834859
nagarajan.tiruppur
Posted Tuesday, December 15, 2009 11:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 18, 2010 5:21 AM
Points: 22, Visits: 45
Hi

Thanks for your reply.

Is there any way to capture select statement return values ?
Post #834860
arun.sas
Posted Tuesday, December 15, 2009 11:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483 
Create procedure mysp
as
begin
declare @return varchar(10)
set @return = 'XYZ'
select @return
end

create table #temp
(
slno int identity(1,1),
name1 varchar(10)
)

insert into #temp (name1)
exec mysp

select * from #temp
Post #834863
nagarajan.tiruppur
Posted Tuesday, December 15, 2009 11:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 18, 2010 5:21 AM
Points: 22, Visits: 45
hi arun

its working ... thanks
Post #834865
NickBalaam
Posted Wednesday, December 16, 2009 2:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:27 AM
Points: 342, Visits: 788
In your original post your SQL creates a stored procedure prefixed with "sp_". Make sure you're aware of the potential knock-on affect of creating sp's that are prefixed "sp_".
Post #834929
RBarryYoung
Posted Monday, December 21, 2009 10:02 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
nagarajan.tiruppur (12/15/2009)
Hi

I have a SP ....

CREATE PROCEDURE
sp_GET_NEXT_SEQU (@TABLE_NAME char(25))
AS
DECLARE @LV_TEST numeric(9,0)

BEGIN TRANSACTION
SELECT @LV_TEST = SQ_COUNT FROM F_Sequ LOCK WHERE SQ_TABLE=@TABLE_NAME
UPDATE F_Sequ SET SQ_COUNT = SQ_COUNT+1 WHERE SQ_TABLE=@TABLE_NAME
SELECT SQ_COUNT FROM F_Sequ WHERE SQ_TABLE=@TABLE_NAME
COMMIT TRANSACTION

But how can i will assign return value (from select statment) to variable

like

declare @sequ numeric(9,0)
select @sequ = exec sp_GET_NEXT_SEQU 'TABLE1'

?

Note: I will not add RETURN, because this is already using in so many places in various applications ...


Hmm, FYI, a few notes on what was wrong with your code above, and what some of you other options are.

First, the reason that "select @sequ = exec sp_GET_NEXT_SEQU 'TABLE1'" does not work is because SQL Server Stored Procedure can only return INTs as their return values.

Secondly, what are the ways that this can be done?

0) Use an INT instead of a NUMERIC(9,0). This should work just fine, although I am not a big fan of stored procedure return values for anything other than status reporting.

1) Use an OUTPUT parameter, as mentioned by previous poster(s).

2) Capture the output of the SELECT statement, also mentioned by previous poster(s). Note, however, that there are certain limitations to this, especially when nesting stored procedure calls.

3) Use a SQL UDF (User-Defined Function) instead. While this is generally the preferred solution, it's not an option in this case because of your UPDATE statement (external/permanent data modifications are not alowed in SQL UDFs).

4) Use a temporary table to return your value(s)/data. This is a bit kludgey, but an option when the previous choices won't work.

5) Use a CLR UDF. This not preferred because CLR should not be added lightly to a SQL database, however, it is a fully supported way to get around some of the limitations of a SQL UDF.

There are a few others, but they are generally undesirable solutions.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #837348
laldopatakhaire2008
Posted Wednesday, December 23, 2009 2:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 23, 2009 2:25 AM
Points: 1, Visits: 0
I have also found this problem as well. I have contacted Cristian Darie via linkedin.com and am awaiting a reply. If I find the answer I will post it to this forum. I am using xampp on windows. I noticed that this problem has been introduced recently as used this code a couple of months ago and it worked with the solution mentioned in the errata.

Thanks





kids snowsuits - children of divorced parents
Post #838415
RBarryYoung
Posted Wednesday, December 23, 2009 3:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374
laldopatakhaire2008 (12/23/2009)
I have also found this problem as well. I have contacted Cristian Darie via linkedin.com and am awaiting a reply. If I find the answer I will post it to this forum. I am using xampp on windows. I noticed that this problem has been introduced recently as used this code a couple of months ago and it worked with the solution mentioned in the errata.

The solution(s) are already posted in this thread. Twice.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."
Post #838790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.