T-SQL Update Query syntax error

Question

I have this simple query :-

update [Schema1].ReportData 
set Qry = 'declare @cols nvarchar(max) select @cols = ''Code,Name,DateOfJoining''' 
where ViewName = 'Rpt1'

But here is the problem. I have more than 50 schemas, so I did the following (summarized SQL):

WHILE (@i <= (SELECT MAX(idx) FROM @schema_table))
    BEGIN

        -- get the next record primary key
        SET @schema_names = (SELECT schema_names FROM @schema_table WHERE idx = @i)

        BEGIN TRY
            DECLARE @sSQL nvarchar(500);            
            SELECT @sSQL = N'update '+@schema_names+'.ReportData set Qry='declare @cols nvarchar(max) select @cols = ''Code,FName,DateOfJoining ,CategoryName''' where  ViewName='Rpt1''
            EXEC sp_executesql @sSQL
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE()+' '+@schema_names AS ErrorMessage;
        END CATCH

        -- increment counter for next record
        SET @i = @i + 1
    END

But is giving me syntax error on this line :-

SELECT @sSQL = N'update '+@schema_names+'.ReportData set Qry='declare @cols nvarchar(max) select @cols = ''Code,FName,DateOfJoining ,CategoryName''' where  ViewName='Rpt1''

Actually i tried many things like using double quotes & escape characters. How to solve this?

change to: PRINT ERROR_MESSAGE()+' '+@schema_names AS ErrorMessage;? Problem is still avaiable? — starko
– starko, Commented Oct 24, 2016 at 10:36
Tried to make this syntax work, but the whole thing you're doing selecting that value for @cols you really should clarify that. — Jens
– Jens, Commented Oct 24, 2016 at 11:18
I am saving the queries in the database for my application & Qry is the column in which i save the queries. — Anup
– Anup, Commented Oct 24, 2016 at 11:34

gofr1 · Accepted Answer · 2016-10-24 11:39:20Z

1

Hope that I understood your UPDATE statement right:

SELECT @sSQL = N'update '+QUOTENAME(@schema_names)+'.ReportData 
set Qry=''declare @cols nvarchar(max) select @cols = ''''Code,FName,DateOfJoining ,CategoryName''''''
where  ViewName=''Rpt1'''

If you are working with any DB object it is better to use QUOTENAME

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

Will bring you this query to execute:

update [Schema1].ReportData 
set Qry='declare @cols nvarchar(max) select @cols = ''Code,FName,DateOfJoining ,CategoryName'''
where  ViewName='Rpt1'

So after running this batch, Qry will be equal to this:

declare @cols nvarchar(max) select @cols = 'Code,FName,DateOfJoining ,CategoryName'

EDIT

One more way is to use CHAR(39) (single quote):

SELECT @sSQL = N'update '+QUOTENAME(@schema_names)+'.ReportData 
set Qry= '+CHAR(39) +'declare @cols nvarchar(max) select @cols = '+CHAR(39)+ CHAR(39)+'Code,FName,DateOfJoining ,CategoryName'+ CHAR(39) +CHAR(39) + CHAR(39) + '
where  ViewName='+ CHAR(39)+'Rpt1'+ CHAR(39)

edited Oct 24, 2016 at 11:39

answered Oct 24, 2016 at 11:21

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jens Over a year ago

Same implementation as what I had, but I commend the good use of QUOTENAME().

Anup Over a year ago

I tried both the examples. but still getting Unclosed Quotation mark error.

gofr1 Over a year ago

Unclosed quotation mark after the character string .. what string exactly? Please elaborate. Maybe you already got [] on the beginning/end of your schema names?

gofr1 Over a year ago

Maybe you got quotes in schema names? Can you check this?

Collectives™ on Stack Overflow

T-SQL Update Query syntax error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related