December 15, 2009 at 10:51 pm
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 ...
December 15, 2009 at 11:12 pm
Hi,
Use the output parameter in the procedure and ref the BOL for set the output parameter in the SP.
December 15, 2009 at 11:16 pm
Hi
Thanks for your reply.
Is there any way to capture select statement return values ?
December 15, 2009 at 11:27 pm
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
December 15, 2009 at 11:32 pm
hi arun
its working ... thanks 🙂
December 16, 2009 at 2:46 am
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_".
December 21, 2009 at 10:02 am
nagarajan.tiruppur (12/15/2009)
HiI 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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2009 at 2:28 am
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[/url] - children of divorced parents
December 23, 2009 at 3:52 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply