Stored Procedures

/* To Insert and Delete Multiple Id's Through SP */

CREATE procedure [dbo].[UP_TEST]
@MultipleId varchar(500)
AS
DECLARE @QUERY VARCHAR(2000)

SET @QUERY = ''
SET @QUERY = @QUERY + ' INSERT INTO T_Test_BackUp
SET @QUERY = @QUERY + ' Select * from T_TEST where id in (' + @MultipleId + ')'
SET @QUERY = @QUERY + ' delete from T_TEST where id in(' + @MultipleId + ')'

EXEC(@QUERY)

/* To Return The Latest Identity Using Output Parameter Through SP */

create proc up_InsertTest
@id int,
@ElementNo varchar(10),
@Name varchar(100),
@outputval varchar(100) = null output

as
Begin
declare @ErrorNumber int
Begin Tran
begin
insert into T_Test(id,ElementNo,Name)
VALUES(@id,@ElementNo,@Name)
set @ErrorNumber = @@Error
if (@ErrorNumber >0) goto Error_lbl
set @outputval = convert(varchar(100), IDENT_CURRENT('T_Test'))
end
Error_Lbl:
if (@ErrorNumber <> 0 )
begin
rollback tran
set @outputval= -1
return
end
End