locked
exec with character string containing literal quotes RRS feed

  • Question

  • I have a script with several instances of this.

    INSERT

    INTO ContactsRoles

     

    (fkContactID, fkRoleID

    )

    SELECT

    C.ContactID

    ,

     

    (SELECT RoleID

     

    FROM Roles

     

    WHERE (Name = 'newsletter')) AS Expr1
     

    FROM

    Contacts AS C INNER

    JOIN

    ADR_import

    .dbo.Contacts AS ACC ON C.FirstName = ACC.[first name] AND C.LastName = ACC.[last name] AND

    C

    .eMail = ACC.[E-mail address]
     

    WHERE

    (ACC.newsletter = 1)

    I wish to create a stored procedure that parameterizes the 2 bold instances of 'newsletter'. The stored procedure uses EXEC with a character string.

    EXEC

     

    ('INSERT INTO ContactsRoles (fkContactID, fkRoleID) SELECT C.ContactID, (SELECT RoleID FROM Roles WHERE (Name = '+@nameVal+')) AS Expr1 FROM Contacts AS C INNER JOIN ADR_import.dbo.Contacts AS ACC ON C.FirstName = ACC.[first name] AND C.LastName = ACC.[last name] AND C.eMail = ACC.[E-mail address] WHERE (ACC.['+@fieldName+'] = 1')

    When I EXEC the stored procedure, I get the error "Incorrect syntax near '1'.". I think I am losing the quotes around the @nameVal string. How can I fix it?

    Bill 


    Bill Swartz
    Thursday, June 2, 2011 8:43 PM

Answers

  • Rather than using EXEC, you should do:

    DECLARE @sql nvarchar(MAX)
    
    SELECT @sql=
       'INSERT INTO ContactsRoles (fkContactID, fkRoleID)
          SELECT C.ContactID, (SELECT RoleID
                               FROM   Roles
                               WHERE Name = @nameVal) AS Expr1
          FROM   Contacts AS C
          JOIN   ADR_import.dbo.Contacts AS ACC
            ON   C.FirstName = ACC.[first name]
           AND   C.LastName = ACC.[last name]
           AND   C.eMail = ACC.[E-mail address]
        WHERE    ACC.' + quotename(@fieldName) '= 1'
    EXEC sp_executesql @sql, N'@nameVal nvarchar(20)', @nameVal

    By using sp_executesql the dynamic SQL gets easier to right, because you don't need to interpolate values you can pass as parameters. The column name you need to interpolate into the query text, since you cannot pass column names as parameters.

    Then again, this may be an option:

       INSERT INTO ContactsRoles (fkContactID, fkRoleID)
          SELECT C.ContactID, (SELECT RoleID
                               FROM   Roles
                               WHERE Name = @nameVal) AS Expr1
          FROM   Contacts AS C
          JOIN   ADR_import.dbo.Contacts AS ACC
            ON   C.FirstName = ACC.[first name]
           AND   C.LastName = ACC.[last name]
           AND   C.eMail = ACC.[E-mail address]
        WHERE   CASE @fieldname
                     WHEN 'newsletter' THEN ACC.newsletter
                     WHEN 'thatcol'    THEN ACC.thatcol
                     ...
                END = 1

    I don't know the full plot, but if there is a small set of columns to choose from, I don't think it is worth the increase in complexity by using dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Friday, June 3, 2011 12:26 AM
    • Marked as answer by Kalman Toth Friday, June 3, 2011 12:45 AM
    Thursday, June 2, 2011 10:13 PM

All replies

  • Looks like there's actually 2 problems.  The quotes around the @nameValue and a missing close parenthesis at the end.

     

    EXEC

     

    ('INSERT INTO ContactsRoles (fkContactID, fkRoleID) SELECT C.ContactID, (SELECT RoleID FROM Roles WHERE (Name = '''+@nameVal+''')) AS Expr1 FROM Contacts AS C INNER JOIN ADR_import.dbo.Contacts AS ACC ON C.FirstName = ACC.[first name] AND C.LastName = ACC.[last name] AND C.eMail = ACC.[E-mail address] WHERE (ACC.['+@fieldName+'] = 1)')

    • Marked as answer by Bill Swartz Thursday, June 2, 2011 9:30 PM
    • Unmarked as answer by Kalman Toth Friday, June 3, 2011 12:45 AM
    Thursday, June 2, 2011 8:52 PM
  • Rather than using EXEC, you should do:

    DECLARE @sql nvarchar(MAX)
    
    SELECT @sql=
       'INSERT INTO ContactsRoles (fkContactID, fkRoleID)
          SELECT C.ContactID, (SELECT RoleID
                               FROM   Roles
                               WHERE Name = @nameVal) AS Expr1
          FROM   Contacts AS C
          JOIN   ADR_import.dbo.Contacts AS ACC
            ON   C.FirstName = ACC.[first name]
           AND   C.LastName = ACC.[last name]
           AND   C.eMail = ACC.[E-mail address]
        WHERE    ACC.' + quotename(@fieldName) '= 1'
    EXEC sp_executesql @sql, N'@nameVal nvarchar(20)', @nameVal

    By using sp_executesql the dynamic SQL gets easier to right, because you don't need to interpolate values you can pass as parameters. The column name you need to interpolate into the query text, since you cannot pass column names as parameters.

    Then again, this may be an option:

       INSERT INTO ContactsRoles (fkContactID, fkRoleID)
          SELECT C.ContactID, (SELECT RoleID
                               FROM   Roles
                               WHERE Name = @nameVal) AS Expr1
          FROM   Contacts AS C
          JOIN   ADR_import.dbo.Contacts AS ACC
            ON   C.FirstName = ACC.[first name]
           AND   C.LastName = ACC.[last name]
           AND   C.eMail = ACC.[E-mail address]
        WHERE   CASE @fieldname
                     WHEN 'newsletter' THEN ACC.newsletter
                     WHEN 'thatcol'    THEN ACC.thatcol
                     ...
                END = 1

    I don't know the full plot, but if there is a small set of columns to choose from, I don't think it is worth the increase in complexity by using dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Friday, June 3, 2011 12:26 AM
    • Marked as answer by Kalman Toth Friday, June 3, 2011 12:45 AM
    Thursday, June 2, 2011 10:13 PM