Geek Answers Handbook

Return value in SQL Server stored procedure

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

However, it only returns the 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
+7
source share
4 answers

You can do one of the following:

Edit:

SET @UserId = 0 to SELECT @UserId

This will return the value just like your second part of the IF statement.

Or, seeing that @UserId is set to Output, change:

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

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

Seeing that you accept the second condition as correct, a query that you could write (without having to change anything outside of this 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 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
+5
source

I can recommend pre-init of the future index value, it is very useful in many cases, for example, for several jobs, for export etc

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

and create the next SP and the generated ID value from this SP and put in a new user line!

 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
+1
source

Try calling proc this way:

 DECLARE @UserIDout int EXEC YOURPROC @EmailAddress = 'sdfds', @NickName = 'sdfdsfs', ..., @UserId = @UserIDout OUTPUT SELECT @UserIDout
0
source 
 @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
0
source

More articles:


All Articles