Named stored proc parameters and concatenation

Question

Possible Duplicate:
Cast integer and concatenate to varchar in TSQL

How do you call a stored procedure using named parameters that have more complex values. Here is a somewhat concocted example:

EXEC MyStoredProc @Param1='My name is: '+@Name

Or:

EXEC MyStoredProc @Param1=CONCAT('My name is: ',@Name)

I get an error trying to concatenate the literal string 'My name is: ' with the variable @Name. Parentheses do not help the matter any. Is this a limitation of T-SQL (i.e., when named parameters are used, the expression after the equal sign must be a single literal or variable)?

Thanks

Why can't you pass in from the source a concatenated string to begin with? — Oded, Sep 27 '12 at 19:58
Limitation of SQL Server. See connect item linked to in above. — Martin Smith, Sep 27 '12 at 19:59

score 5 · Accepted Answer · answered Sep 27 '12 at 19:58

5

One way

declare @Var1 varchar(50)
select @Var1 = 'My name is: '+@Name

EXEC MyStoredProc @Param1=@Var1

The same is true with functions as well

you can't do this

EXEC MyStoredProc @Param1=getdate()

you need to do

declare @Var1 datetime
select @Var1 = getdate()

EXEC MyStoredProc @Param1=@Var1

answered Sep 27 '12 at 19:58

SQLMenace

132,095
25
206
225

1

You can call *some* functions inline (the `@@` prefixed ones - so `@@identity` works but `scope_identity()` doesn't) – Martin Smith Sep 27 '12 at 20:01
Correct..although I think of @@ more as system variables than functions – SQLMenace Sep 27 '12 at 20:03
Updated with a scalar function call example... – Michael Goldshteyn Sep 27 '12 at 20:08
you have to do the concat before calling the proc – SQLMenace Sep 27 '12 at 20:09

Named stored proc parameters and concatenation

1 Answers1