CODEWITHSUNDEEP
sqlsql-server
user2738513
user2738513

Reputation: 11

SQL - Exec (string + null) - is this a bug or by design?

i was revising an an old sql query code and could not figure out why the new version wont work. Below is the simplest version of the code.

--old version

declare @query as varchar(10)
set @query = null
print 'test1'
exec('print ''a'''+@query+';print ''b''')

--new version

declare @query as varchar(10)
declare @text varchar(20)
set @text = null
set @query = 'print ''a'''+@text+';print ''b'''
print 'test2'
exec (@query)

Results

test1
a
b

test2

Running exec with strings + null automatically ignores null. is this by design or documented somewhere? or is this a bug? i must be missing something. could someone pls explain?

Could any one pls post mysql results? thanks.

Upvotes: 0

Views: 160

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103525

The documentation states the syntax:

Execute a character string
{ EXEC | EXECUTE } 
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS {  USER } = ' name ' ]
[;]

So, my guess is that the + in the expression 'print ''a'''+@query+';print ''b''' is not being interpreted as string concatenation, but simply as part of the syntax for the exec command.

That might explain why the behaviour is different; although not whether it's a bug or a feature.

Contrast the above with the documentation for print:

PRINT msg_str | @local_variable | string_expr

...
string_expr
Is an expression that returns a string. Can include concatenated literal values, functions, and variables. For more information, see Expressions (Transact-SQL).

Notice that print just refers to a "string expression", where exec defines its own syntax.

Upvotes: 1

Related Questions