force output param to not be tinyint?

Question

I just inherited a stored procedure as part of a system for inserting a new user.
At the top, @user_id is declared as INT OUTPUT and at the bottom SET @user_id = @@IDENTITY, which always returns zero for some reason. If I do:

select @@IDENTITY 

...I get something around '1872040'

If I do:

SET @user_id = 187300  

...I get:

Error converting data type int to smallint

I also get the same if I do:

SET @user_id = CAST(@@IDENITY as INT)... 

It needs to be SET @user_id = CAST(@@IDENITY as TINYINT) or just@user_id = @@IDENITY for it to not give me errors. However, then it just returns null / 0. So, I was googling around and couldn't really find an answer, is there a better way to force the output param to not be tinyint? this is in ms sql.

Answer 1

I have reproduced a scenario that can cause this problem. I think it could be not the sproc itself, but the way you're calling the sproc - specifically, the data type being specified for @user_id by the calling code.

e.g.

DECLARE @user_id SMALLINT -- THIS IS WRONG, SHOULD BE INT
EXECUTE YourSproc 'ValueA', @user_id OUTPUT

Once the IDENTITY value on the table exceeds the capacity of a SMALLINT, this would give you the conversion error.

So make sure you're specifying the correct datatype in the calling code.

Also, as per my comment, you probably want to be using SCOPE_IDENTITY() instead of @@IDENTITY.