sp_executesql

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Comments posted to this topic are about the item sp_executesql

  • This was removed by the editor as SPAM

  • Carlo Romagnano

    SSC-Insane

    Points: 21710

    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

    SSCarpal Tunnel

    Points: 4764

    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: 12215

    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: 104762

    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: 33261

    Why not option 2?

    Thanks.

  • sknox

    SSChampion

    Points: 12215

    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

    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

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

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