Search code examples
sqlsql-servert-sqlsql-server-2000

Inline SQL Error

I am new to using inline sql but keep getting an incorrect syntax near the keyword 'Update' error.

This is the syntax I am using, can someone point out (probably a very very newbie issue that is causing this?)

Declare @MyField As varchar(100)
Declare @Sql As nvarchar(1000)

Create Table #TableNames
(
 TableName varchar(50)
)

Insert Into #TableNames(TableName)
Values('Pixel')
Values('HellFire')

Declare curX Cursor for Select TableName from #TableNames
Open curX
Fetch Next from curX Into @MyField
While @@ Fetch_Status <> -1
Begin

Set @Sql = N'(Update (trixieIce.dbo.' + @FieldName + N' Set Shipped = 'Yes' ShippedDate = GetDate() WHERE ShippedDate IS NULL And Shipped IS NULL)'

Exec sp+executesql @SQL
Fetch next from curX into @FieldName
End

Close curX
Deallocate curX
Solution

  • I have to assume that you are tying to implement Dynamic SQL. If so, this is how:

    Declare @MyField As varchar(100)
Declare @Sql As nvarchar(1000)

Set @Sql = N'Update trixieIce.dbo.' + @MyField
           + N' Set Shipped = ''Yes'', ShippedDate = GetDate()'

    Then after this you can execute it with EXEC(@Sql).

    You should also be aware the if @MyField is really a prarmeter that you are receiving from a client application, that it needs to be validated properly or else you'll be vulnerable to SQL Injection attacks. This is explained here: https://stackoverflow.com/a/1246848/109122