CODEWITHSUNDEEP
sql-serverstored-procedures
Vykta Wakandigara
Vykta Wakandigara

Reputation: 349

I keep getting errors when trying to execute a stored procedure

I have a stored procedure which is supposed to update two tables, and it is defined as follows

CREATE procedure ChangeNames 
@oldname nvarchar(100),
@newname nvarchar(100), 
@tablename nvarchar(100)
AS
Begin
Declare @sql nvarchar(max);
Set @sql  = 'UPDATE' +  @tablename + 'SET NAMES =' +  @newname + 'where 
names =' + @oldname + 
'UPDATE ref_names SET NAMES =' + @newname + 'where names =' + @oldname 
Execute sp_executesql @sql
End`

I then execute the procedure as follows:

USE [database_name]
GO


exec dbo.ChangeNames
@oldname = 'ab', 
@newname = 'cd', 
@tablename = 'ef' 

GO

I get the following error:

Incorrect syntax near '='.

How can I fix this?

Upvotes: 0

Views: 55

Answers (3)

Milad
Milad

Reputation: 117

You are missing space after update keyword
Set @sql = 'UPDATE ' + @tablename + 'SET NAMES =' + @newname + 'where

Upvotes: 0

Thom A
Thom A

Reputation: 95544

I would parametrise your SQL, rather than concatenating the values, and quote the dynamic object name; it makes it far safer:

CREATE PROCEDURE ChangeNames @oldname nvarchar(100),
                             @newname nvarchar(100),
                             @tablename sysname --changed to sysname, as that's the appropriate datatype for an object's name
AS
BEGIN

    DECLARE @SQL nvarchar(MAX);
    SET @SQL = N'UPDATE ' + QUOTENAME(@tablename) + NCHAR(10) + 
               N'SET names = @New' + NCHAR(10) + 
               N'WHERE names = @old;' + NCHAR(10) + 
               N'UPDATE ref_names' + NCHAR(10) + 
               N'SET names = @New' + NCHAR(10) + 
               N'WHERE names = @old;';
     PRINT @SQL; --Your best friend
    EXEC sp_executesql @SQL, N'@new nvarchar(100), @old nvarchar(100)', @new = @newname, @old = @oldname;
END

Upvotes: 2

EzLo
EzLo

Reputation: 14189

You are missing a few spaces between keywords and single quotes and when delimiting literal values:

Set @sql = '
    UPDATE ' +  @tablename + ' SET 
        NAMES = ''' +  @newname + ''' 
    WHERE
        names = ''' + @oldname + ''' 

    UPDATE ref_names SET 
        NAMES = ''' + @newname + '''
    WHERE
        names = ''' + @oldname + ''''

It's recommended to use PRINT to check the generated SQL before executing dynamic SQL, you will be able to spot these mistakes.

PRINT (@sql)
-- Execute sp_executesql @sql

Also add QUOTENAME to object names, like your dynamic table reference. The object name might have characters that break your dynamic SQL, such as spaces. Using QUOTENAME will correctly escape them.

Set @sql  = 'UPDATE ' +  QUOTENAME(@tablename) + --...

One last thing, make sure the value you are passing to search and update have escaped single quotes as they will also break your dynamic SQL otherwise.

So if you want to update values to FLIGHT CENTRE's TRAVEL GROUP (note the single quote in the middle) you will have to actually write FLIGHT CENTRE''s TRAVEL GROUP so the quote is escaped correctly.

Upvotes: 2

Related Questions