CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Seth Spearman
Seth Spearman

Reputation: 6780

How can I pass @VAR + magic numbers to a stored procedure parameter in a sql script?

Lets say I have a stored proc call MyStoredProc which recieved an int (@MyParam INT) datatype.

Let's say I have an int declared in a script like this...

DECLARE @MyVar INT ;
SET @MyVar = 101 ;

I just wrote a long sql script where I do a LOT of this...

EXEC MyStoredProc @MyVar + 1  ;

I am shocked that this is causing a syntax error warning.

I can do this... 

EXEC MyStoredProc @MyVar ;

and I can do this... 

EXEC MyStoredProc 101 ;

but I can't do this... 

EXEC MyStoredProc @MyVar + 1  ;

This is going to make my script a LOT harder to write unless I am missing something. This is for SQL 2005.

Upvotes: 3

Views: 168

Answers (2)

abatishchev
abatishchev

Reputation: 100308

The only option I see is to declare a temp variable:

declare @var1_increment = @var1 + 1
exec stored_proc @var1_increment

And create a Connect and User Voice topics proposing to extend T-SQL syntax.

Upvotes: 2

Kaf
Kaf

Reputation: 33839

If you add @MyVar + @MyVar2 there is a possibility that could overflow an integer, that may be a one reason.

By the way following is not working on sql server 2008

EXEC MyStoredProc @MyVar + @MyVar2;

I think you can do the addition before calling the stored procedure (as per @adatishchev) or pass two parameters to the stored procedure and add them inside the stored procedure.

EXEC MyStoredProc @MyVar, @MyVar2;

CREATE PROCEDURE [dbo].[MyStoredProc]

     @MyVar INT,
     @MyVar2 INT
AS
BEGIN
     SELECT @MyVar += @MyVar2;

END

Upvotes: 2

Related Questions