March 27, 2008 at 12:42 am
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved
1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column
Pls provide me with full stored procedure code.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply