why it doesnt accept a concated string as parameter in SP?

Question

I wrote an SP that works perfectly. I was calling the SP with regular parameters and worked. However, now I need to call the SP like below

EXEC @v_nReturn = sp_get_next_value 'LP_' + @WhID + '_COUNTER'

The intellisense gives an error on the first '+' sign.

It says "incorrect syntax err...."

@WhID is NVARCHAR(10), so I shouldn't convert it to NVARCHAR.

what is the problem?

Answer 1

SQL Server doesn't do (full) expression parsing when you call a stored procedure. This is definitely an area where a small change would be highly convenient, although there are probably good reasons for the limitation.

As mentioned in a comment, use a separate variable:

DECLARE @arg varchar(256) =  'LP_' + @WhID + '_COUNTER';
EXEC @v_nReturn = sp_get_next_value @arg;

Be careful if @WhID is numeric. Then you need to convert the value to a string first.

Answer 2

Try with parens:

EXEC @v_nReturn = (sp_get_next_value 'LP_' + @WhID + '_COUNTER')