sp_executesql

Question

Post reply

sp_executesql

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
June 29, 2016 at 9:16 pm

#75744

Comments posted to this topic are about the item sp_executesql
June 29, 2016 at 10:48 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22600 More actions · Answer 1

The second option is useless, but it also is correct.

SELECT @p = N'@m varchar(200)="event message", @d datetime=getdate(), @U varchar(10)="Steve" '

I.e. this code returns 1 despite @p is initialized with 1234.

declare @p int = 1

exec sp_executesql N'select @p',N'@p int = 1234',@p

No error is raised.

edwardwill SSCertifiable Points: 5790 More actions · Answer 2

Carlo Romagnano (6/30/2016)
The second option is useless, but it also is correct.
SELECT @p = N'@m varchar(200)="event message", @d datetime=getdate(), @U varchar(10)="Steve" '
I.e. this code returns 1 despite @p is initialized with 1234.
declare @p int = 1
exec sp_executesql N'select @p',N'@p int = 1234',@p
No error is raised.

Agreed. This was my answer.

sknox SSChampion Points: 12388 More actions · Answer 3

Carlo Romagnano (6/30/2016)
The second option is useless, but it also is correct.
SELECT @p = N'@m varchar(200)="event message", @d datetime=getdate(), @U varchar(10)="Steve" '
I.e. this code returns 1 despite @p is initialized with 1234.
declare @p int = 1
exec sp_executesql N'select @p',N'@p int = 1234',@p
No error is raised.

Your example works, but the second option does not. You are correct that ="xxx" does not raise an error, but =getdate() does:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '('.

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@d".

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Nice easy question,

but I don't like the wording:

There are two things that could be called the parameter list: the list of (formal) parameter declarations, or the list of (actual) parameter assignments (@m = @msg, @d= @dt, @U = @user in this example).

Fortuntely none of the answer options was syntactically possible as a parameter assignment list and only the first one was a list of formal parameter declarations so it was straightforward to work out that the list of formal parameter declarations was what was required and that the first option was the right one. But wouldn't it have been nicer to have unambiguous terminology used in the question?

Tom

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

SQL_Hunt

SSC-Dedicated

Points: 33518

July 3, 2016 at 6:54 pm

#1888232

Why not option 2?

Thanks.

sknox SSChampion Points: 12388 More actions · Answer 6

SQL-DBA-01 (7/3/2016)
Why not option 2?

In parameter declaration, @parameter type = defaultvalue, the part after the equals sign is a default, not an assigned, value.

In T-SQL, parameter default values must be constants; they cannot be functions. So Option 2 fails on the "@d datetime=getdate()" part.

From Books Online:

default
A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure. See Example C below.

Reference: https://msdn.microsoft.com/en-us/library/ms187926.aspx

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 7

Easy question. I guess it was inspired from the SQL Server Profiler. There the sql statements are executed like this one.

Igor Micev,My blog: www.igormicev.com