sp_executesql Msg 102 Incorrect syntax

[Galla]

I am trying to set up a procedure that takes the table name as one of
the parameters and then executes an update statement for the specified
criteria. I am getting Msg 102 errors, Incorrect syntax near table name,
id and week.

Does anybody know what the problem is? Thanks for your help!

CREATE PROCEDURE [dbo].[change] @id nvarchar(10), @week
nvarchar(10),@tbl nvarchar(10)
AS
BEGIN
DECLARE @strsql as nvarchar(500)

SET @strsql = N'UPDATE ' + @tbl + N' SET [status] = 3 WHERE ID = ' + @id
+ N' and [WEEK] = ' + @week

EXEC sp_executesql @cmd, @tbl, @id, @week

END

[Eric Isaacs]

> EXEC sp_executesql @cmd, @tbl, @id, @week

Yes, @cmd is not defined in your stored procedure.

-Eric Isaacs

[Aaron Bertrand]

Maybe you meant:

EXEC sp_executesql @strsql;

On 8/25/09 3:40 PM, in article OLGSivbJ...@TK2MSFTNGP05.phx.gbl,

[Galla]

Yes, thanks Eric, that was a typo in this post.
I used the correct variable to execute and I get the errors mentioned above.

EXEC sp_executesql @strsql , @tbl, @id, @week

[Galla]

Ah yes, thanks Aaron, that did it ... plus another little tweak for the
id and week parameters:

SET @strsql= N'UPDATE ' + @tbl + N' SET [status] = 3 WHERE ID = ''' +
@id + N''' and [WEEK] = ''' + @week + ''''

EXEC sp_executesql @strsql

[Aaron Bertrand]

Sorry, I had no idea what the data types of your columns were. ID and week
certainly sounded numeric to me, and you may have been using NVARCHAR
parameters simply to avoid having to convert the values when concatenating
them into your dynamic SQL string.

Anyway, I strongly suggest some reading:

http://www.sommarskog.se/dynamic_sql.html

On 8/25/09 4:38 PM, in article #GnyDQcJ...@TK2MSFTNGP04.phx.gbl,

[Erland Sommarskog]

The second parameter to sp_executesql is the parameter list:

N'@id nvarchar(10), @week nvarchar(10)'

@tbl is not a parameter to the command, so there is no need to include it.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx