Inline SQL Error

Question

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

What do you mean by "*inline sql*"? Do you perhaps mean Dynamic Sql? — RBarryYoung, Nov 18 '13 at 18:46
It's not clear what you are trying to do here. Please add some explanation. — RBarryYoung, Nov 18 '13 at 19:00
The code that I currently have posted below should work if you are doing the `EXEC(@SQL)` within the body of your loop. — RBarryYoung, Nov 18 '13 at 19:03
In this line: `Exec sp+executesql @SQL` take out the `+`, its a typo/invalid. — RBarryYoung, Nov 18 '13 at 19:19
It now says executed succesfully, but shows this message "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'spexecutesql'" Is that an issue or can I ignore that? — MasterOfStupidQuestions, Nov 18 '13 at 19:24
Sorry, it should really be `Exec sp_executesql @SQL`, my bad. — RBarryYoung, Nov 18 '13 at 19:29

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

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

edited May 23 '17 at 12:19

Community

1
1

answered Nov 18 '13 at 18:49

RBarryYoung

55,398
14
96
137

This is incomplete because `@MyField` needs to have something in it. Also, I'm assuming that that's the table name, so if it isn't then that's another problem. If it still doesn't work for you, post the whole of what you are trying to execute so we can figure it out. – RBarryYoung Nov 18 '13 at 18:58
Still getting Incorrect Syntax near '+' Can this be run on SQL Server 2000 – MasterOfStupidQuestions Nov 18 '13 at 19:05
@user2954053 Yes, this should work on SQL Server 2000. Please post a *complete* example of what you are trying to execute now so that we can test it ourselves. What I have posted should work, so I need to see what you are actually doing to tell what is wrong. – RBarryYoung Nov 18 '13 at 19:08

score 0 · Answer 2 · answered Nov 18 '13 at 18:41

I dont think you have closed all of the ()

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

Set @Sql = (Update **(**trixieIce.dbo.' + @FieldName + ' Set Shipped = 'Yes' ShippedDate = GetDate())

You opened that one but did not close. Also a , on the fields in the set fields

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

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

Inline SQL Error

2 Answers2