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.

Solution 1

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://msdn.microsoft.com/en-us/library/ms188332.aspx

SET statement: http://msdn.microsoft.com/en-us/library/ms189484.aspx

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://msdn.microsoft.com/en-us/library/ms190286.aspx

Solution 2

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)

Solution 3

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

Comments

[Buggieboy]

The website referred to in this comment is no longer up.

[Michel de Ruiter]

Maybe EXEC only permits string variables because stored procedures can have OUT parameters for which expressions make no sense. But those don't work with string literals either...

[Pete Alvin]

What does this issue have to do with dynamic SQL?