T-SQL: Cannot pass concatenated string as argument to stored procedure

Question

Scenario: Need to pass n arguments to a stored procedure. One of the arguments is of type varchar(x). That varchar argument needs to be constructed from a handful of other varchar variables. This problem uses SQL Server 2005, but this behaviour applies to all versions of SQL Server.

Setup:

DECLARE @MyString varchar(500), @MyBar varchar(10), @MyFoo varchar(10)

SELECT @MyBar= 'baz ' 
SELECT @MyFoo= 'bat ' 

-- try calling this stored procedure!
EXEC DoSomeWork @MsgID, 'Hello ' + @MyBar + '" world! "' + @MyFoo + '".'

This produces the exception in SQL Server: Incorrect syntax near '+'. Typically you might think that the datatype would be wrong (i.e. the variables are of different types, but that would produce a different error message).

Here's a correct implementation that compiles without error:

SELECT @MyString= 'Hello ' + @MyBar + '" world! "' + @MyFoo + '".';

EXEC DoSomeWork @ID, @MyString

Question: Why is it that T-SQL can't handle the concatenation of a varchar as an argument? It knows the types, as they were declared properly as varchar.

Answer 1

You cannot do something like this either

exec SomeProc getdate()

you have to put all that stuff in a param like you are doing at your bottom query It might be because it is non deterministic (at least for functions)

Answer 2

It's a limitation on the EXEC statement. See The curse and blessings of dynamic SQL for more information.

Answer 3

The EXECUTE statement simply has a different grammar then other statements like SELECT and SET. For instance, observe the syntax section at the top of the following two pages.

EXECUTE statement: http://doc.ddart.net/mssql/sql70/ea-ez_2.htm

SET statement: http://doc.ddart.net/mssql/sql70/set-set.htm

The syntax for EXECUTE only accepts a value

[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]

Whereas the syntax for SET accepts an expression

{@local_variable = expression}

A value is basically just a hard coded constant, but an expression is going to be evaluated. It's like having the varchar 'SELECT 1 + 1'. It's just a varchar value right now. However, you can evaluate the string like this:

EXEC('SELECT 1 + 1')

I suppose all I'm pointing out is that the EXEC command doesn't allow expressions by definition, which you apparently found out already. I don't know what the intention of the developers of T-SQL where when they made it that way. I suppose the grammar would just get out of hand if you where allowed to throw subqueries within subqueries in the parameter list of a stored procedure.

T-SQL Expression: http://doc.ddart.net/mssql/sql70/ea-ez_5.htm