Return value in SQL Server stored procedure

Question

I have a stored procedure that has an if statement in it. If the number of rows counted is greater than 0 then it should set the only output parameter @UserId to 0

However it only returns a value in the second part of the query.

@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF 
    (SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
    BEGIN
        SET @UserId = 0
    END
ELSE
    BEGIN
        INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
        SELECT SCOPE_IDENTITY()
    END

END

What do you mean by "returns a value in the second part of the query"? Output parameter won't be returned as a recordset — Andrey Gurinov
– Andrey Gurinov, Commented Mar 23, 2012 at 10:23

Curtis · Accepted Answer · 2012-03-23 11:10:00Z

9

You can either do 1 of the following:

Change:

SET @UserId = 0 to SELECT @UserId

This will return the value in the same way your 2nd part of the IF statement is.

Or, seeing as @UserId is set as an Output, change:

SELECT SCOPE_IDENTITY() to SET @UserId = SCOPE_IDENTITY()

It depends on how you want to access the data afterwards. If you want the value to be in your result set, use SELECT. If you want to access the new value of the @UserId parameter afterwards, then use SET @UserId

Seeing as you're accepting the 2nd condition as correct, the query you could write (without having to change anything outside of this query) is:

@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF 
    (SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
    BEGIN
        SELECT 0
    END
ELSE
    BEGIN
        INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
        SELECT SCOPE_IDENTITY()
    END

END

edited Mar 23, 2012 at 11:10

answered Mar 23, 2012 at 10:22

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

andrew slaughter Over a year ago

Hi Ive tried setting the second part to set the @UserId but that didnt work either

Curtis Over a year ago

@andrewslaughter See my edit. Both conditions are now using SELECT, which will return a value in your result set. Looking at your question, it seems thats how you are accessing the value from this query

Koopakiller · Accepted Answer · 2016-07-04 14:30:06Z

4

I can recommend make pre-init of future index value, this is very usefull in a lot of case like multi work, some export e.t.c.

just create additional User_Seq table: with two fields: id Uniq index and SeqVal nvarchar(1)

and create next SP, and generated ID value from this SP and put to new User row!

CREATE procedure [dbo].[User_NextValue]
as
begin
    set NOCOUNT ON


    declare @existingId int = (select isnull(max(UserId)+1, 0)  from dbo.User)

    insert into User_Seq (SeqVal) values ('a')
    declare @NewSeqValue int = scope_identity()     

    if @existingId > @NewSeqValue 
    begin  

        set identity_insert User_Seq  on
        insert into User_Seq (SeqID) values (@existingId)     
        set @NewSeqValue = scope_identity()     
    end

    delete from User_Seq WITH (READPAST)

return @NewSeqValue

end

edited Jul 4, 2016 at 14:30

Koopakiller

2,8813 gold badges34 silver badges50 bronze badges

answered Mar 23, 2012 at 11:02

a3code

14.1k26 gold badges84 silver badges151 bronze badges

1 Comment

Franziee Over a year ago

by the MSDN there is an advice: Also you should avoid using the return code to return application data.

Andrey Gurinov · Accepted Answer · 2012-03-23 10:25:34Z

2

Try to call your proc in this way:

DECLARE @UserIDout int

EXEC YOURPROC @EmailAddress = 'sdfds', @NickName = 'sdfdsfs', ..., @UserId = @UserIDout OUTPUT

SELECT @UserIDout

answered Mar 23, 2012 at 10:25

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

3 Comments

Curtis Over a year ago

@AndreyGurinov I don't see how this would solve the problem.. UserId will still be NULL for the 2nd condition.

andrew slaughter Over a year ago

Hi thanks for that If I ExecuteStoredProcedure from the object explorer and put the parameters in the first time I run it, I get in the results pane (no column name) 64 (this is the last insert user id) &UserId NULL Return Value 0 If I then run it again I just get &UserId 0 return value 0

Water Over a year ago

This helped me figure out how to call the stored procedure

KEERTHANA · Accepted Answer · 2015-11-18 11:15:17Z

0

@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
DECLARE @AA INT
SET @AA=(SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress)

IF @AA> 0
    BEGIN
        SET @UserId = 0
    END
ELSE
    BEGIN
        INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
        SELECT SCOPE_IDENTITY()
    END

END

answered Nov 18, 2015 at 11:15

KEERTHANA

1

Collectives™ on Stack Overflow

Return value in SQL Server stored procedure

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related