sp_executesql Msg 102 Incorrect syntax

#1

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

#2

Quote:

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

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

-Eric Isaacs

#3

Maybe you meant:

EXEC sp_executesql @strsql;

On 8/25/09 3:40 PM, in article OLGSivbJKHA.3632 (AT) TK2MSFTNGP05 (DOT) phx.gbl,
"Galla" <private (AT) somedomain (DOT) com> wrote:

Quote:

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

#4

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

Eric Isaacs wrote:

Quote:

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

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

-Eric Isaacs

#5

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

Quote:

Maybe you meant:

EXEC sp_executesql @strsql;

On 8/25/09 3:40 PM, in article OLGSivbJKHA.3632 (AT) TK2MSFTNGP05 (DOT) phx.gbl,
"Galla" <private (AT) somedomain (DOT) com> wrote:

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

#6

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 #GnyDQcJKHA.4652 (AT) TK2MSFTNGP04 (DOT) phx.gbl,
"Galla" <private (AT) somedomain (DOT) com> wrote:

Quote:

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

#7

Galla (private (AT) somedomain (DOT) com) writes:

Quote:

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

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, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sp_executesql Msg 102 Incorrect syntax

microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming

sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 02:40 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 03:13 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 03:23 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 03:24 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 03:38 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 03:55 PM

Re: sp_executesql Msg 102 Incorrect syntax - 08-25-2009 , 05:16 PM