Syntax Error while running a stored procedue

A

AmjiBhai

Joined
Feb 21, 2018
Messages
216
Reaction score
86
ALTER PROCEDURE [dbo].[ServerRefUpdate]
@str varchar(999),@server varchar(50)


AS
BEGIN
set @str = 'Update [dbo].[Lists] SET ASREFSER='' + @server + '' where isnull(ASREFSER,'')='+''''

exec ( @str )



Please help me resolve the following error message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')='.
 
Last edited:
Abraham Andres Luna said:
Try this instead:

SQL: 
set @str = "Update [dbo].[Lists] SET ASREFSER='" + @server + "' where isnull(ASREFSER,'')=''"
Click to expand...
I tried but unfortunately still no luck....
Strange thing is the insert command using the @server variable is working but Update/SET is failing...


set @str = 'insert into Products select * from ['+@server+'].seregation.[dbo].[Products] P where convert(varchar,P.ProductID)+P.ProductName not in (select distinct convert(varchar,ProductID)+ProductName from Products A) and P.Productid in (select A.ProductId from ['+@server+'].seregation.dbo.batches A, ['+@server+'].seregation.dbo.Products B where A.status=1 and A.ProductID=B.ProductID)'

exec ( @str )



set @str = "Update [dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"

exec ( @str )

error msg is as follows:
Msg 207, Level 16, State 1, Procedure LoadSubscriberData, Line 38
Invalid column name 'Update [dbo].[Lists] SET [ASREFSER]=''.
Msg 207, Level 16, State 1, Procedure LoadSubscriberData, Line 38
Invalid column name '' where isnull([ASREFSER],'')='''.
 
So don't you need the server name to be a part of the update statement?

Code: 
set @str = "Update [Seregation_Master].[dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"
 
Abraham Andres Luna said:
So don't you need the server name to be a part of the update statement?

Code: 
set @str = "Update [Seregation_Master].[dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"
Click to expand...
Yes. I dont need to update remote server....Records are being pulled from remote server and inserted into the local db....
I was lucky to resolve this issue by making the following change: Abraham ! thanks a lot for your precious time & help .


BEGIN
UPDATE Lists
SET
ASREFSER=@server
Where isnull(ASREFSER,'')=''
END
 
Back
Top