March 27, 2008 at 2:35 am
Also asked and answered
here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99688
and here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99741
N 56°04'39.16"
E 12°55'05.25"
June 2, 2008 at 11:25 am
hi, the following soloution may be correct to the extent I understood the issue 🙂
create procedure SpArchai(@apProvedStatus nvarchar(3), @taskid int)
AS
BEGIN
IF lower(@apProvedStatus) NOT IN ('yes','no') RAISERROR ('Please input either Yes or No for Approved Status',10,1)
begin
BEGIN TRAN
INSERT INTO c (TaskId,PoId,Podate,Approved) SELECT TaskId,PoId,Podate,Approved FROM A WHERE Approved =@apProvedStatus AND TaskId = @taskid
IF @@ROWCOUNT>0
begin
DELETE FROM A WHERE Approved =@apProvedStatus AND TaskId = @taskid
COMMIT TRAN
end
else
rollback tran
end
END
drop procedure SpArchai_new
create procedure SpArchai_new(@apProvedStatus nvarchar(3), @taskid int,@Tablename varchar(1))
AS
BEGIN
IF lower(@apProvedStatus) NOT IN ('yes','no') RAISERROR ('Please input either Yes or No for Approved Status',10,1)
begin
declare @SQLString nvarchar(250)=''
select @SQLString ='INSERT INTO c (TaskId,PoId,Podate,Approved) SELECT TaskId,PoId,Podate,Approved FROM ' + @Tablename + ' WHERE Approved =' + char(39) + @apProvedStatus + char(39) + ' AND TaskId = '+ CAST( @taskid as varchar(20))
print @SQLString
BEGIN TRAN
exec ( @SQLString )
IF @@ROWCOUNT>0
begin
select @SQLString ='DELETE FROM ' + @Tablename + ' WHERE Approved =' + char(39) + @apProvedStatus + char(39) + ' AND TaskId = '+ CAST( @taskid as varchar(20))
print @SQLString
exec (@SQLString)
COMMIT TRAN
end
else
rollback tran
end
END
exec SpArchai_new 'YES' ,1,'A'
select * from a
Note : one should avoid the 'execute' statement as it may invite SQL injections, so better use sp_executeSQL if require.
regards,
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply