SQL Server: error with Transaction and Update statement -
i trying update 2 tables in row part of else statement.
therefore wrapped update statements in transaction thought correct approach getting following error when trying execute this. variables declared @ beginning of stored procedure.
can tell me missing or doing wrong here ?
msg 156, level 15, state 1, line 1 incorrect syntax near keyword 'else'. msg 137, level 15, state 2, line 4 must declare scalar variable "@parentid". msg 137, level 15, state 2, line 11 must declare scalar variable "@lastupdate". my sql (update: posting full query):
alter procedure [dbo].[moc_updatenav] @itemid int, @parentid int, @itemname nvarchar(100), @linkref nvarchar(2000), @sortid int, @lastupdate nvarchar(50), @modby varchar(50) begin set nocount on; if not exists ( select * moc_links itemid = @itemid ) insert moc_links ( parentid, itemname, linkref, sortid ) select @parentid, @itemname, @linkref, @sortid insert moc_log ( lastupdate, modtime, modby ) select @lastupdate, getdate(), @modby else begin transaction update moc_links set parentid = @parentid, itemname = @itemname, linkref = @linkref, sortid = @sortid itemid = @itemid update moc_log set lastupdate = @lastupdate, modtime = getdate(), modby = @modby itemid = @itemid commit end many in advance this, tim.
make sure have declared @parentid , @lastupdate variable , inside else block wrap whole transaction inside begin..end block. rest seems fine me.
alter procedure [dbo].[moc_updatenav] @itemid int, @parentid int, @itemname nvarchar(100), @linkref nvarchar(2000), @sortid int, @lastupdate nvarchar(50), @modby varchar(50) begin set nocount on; begin transaction; if not exists(select 1 moc_links itemid = @itemid) begin --<-- block here insert moc_links(parentid, itemname, linkref, sortid) values (@parentid,@itemname,@linkref,@sortid) insert moc_log (lastupdate,modtime,modby) values (@lastupdate,getdate(),@modby) end else begin --<-- block here update moc_links set parentid = @parentid ,itemname = @itemname ,linkref = @linkref ,sortid = @sortid itemid = @itemid update moc_log set lastupdate = @lastupdate ,modtime = getdate() ,modby = @modby itemid = @itemid end commit transaction; end note
dont know whole statement looks like, transaction wrapper make more sense around whole if..else statement. dont think ?
Comments
Post a Comment