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   gt  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

User · Answer

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

User · Answer

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 gt  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

User · Answer

Try to call your proc in this way   DECLARE  UserIDout int  EXEC YOURPROC  EmailAddress    sdfds    NickName    sdfdsfs         UserId    UserIDout OUTPUT  SELECT  UserIDout

User · Answer

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   gt  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

[sql] Return value in SQL Server stored procedure

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures