locked
Call Stored Procedure Multiple times RRS feed

  • Question

  • I want to use a SQL script to execute a SP that takes value then calls a stored procedure multiple times for each of those values. Each time, I am passing in those values as a parameter for stored procedure call. How can I do that?

    My another question is I want to save SP execution error message with SP name and input parameter value in a table, How can I do that?

    Please help me how can I do this.

    Thanks


    Sunday, September 15, 2019 7:56 AM

Answers

  • Hi Uzzal Kumar Hore,

    For your 1st question, hope the attached sample can help you!

    --sample table script (run 1 time)
    create table test(
    id int not null identity,
    number int not null,
    rundate datetime default getdate()
    );
    go
    
    --sample sp (1st for genearting numbers with output parameter)
    create or alter procedure testsp (@next_number int output)
    as begin
    	insert into test (number,rundate)
    	select isnull(max(number)+1,1),getdate() from test;
    	select @next_number=max(number) from test;
    end;
    go
    --sample sp (2nd sp for select all values from table)
    create or alter procedure testspdetails (@number int )
    as begin
    	select * from test where number=@number;
    end;
    go
    
    --1st question Answer
    
    --(check this by executing multiple times)
    declare @next_number int,@start int,@end int;
    set @start=0;
    set @end=2;
    while(@start<@end)
    	begin
    	exec testsp @next_number output; --1st sp
    	--select @next_number;
    	exec testspdetails @number=@next_number; --2nd sp
    	set @start=@start+1;
    end
    --	select * from test;
    
    --drop table test;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi


    Sunday, September 15, 2019 8:39 AM
  • A stored procedure can be executed multiple times using a WHILE loop. It depends on circumstances, therefore, give some details about the values, etc.

    Sunday, September 15, 2019 9:15 AM

All replies

  • Hi Uzzal Kumar Hore,

    For your 1st question, hope the attached sample can help you!

    --sample table script (run 1 time)
    create table test(
    id int not null identity,
    number int not null,
    rundate datetime default getdate()
    );
    go
    
    --sample sp (1st for genearting numbers with output parameter)
    create or alter procedure testsp (@next_number int output)
    as begin
    	insert into test (number,rundate)
    	select isnull(max(number)+1,1),getdate() from test;
    	select @next_number=max(number) from test;
    end;
    go
    --sample sp (2nd sp for select all values from table)
    create or alter procedure testspdetails (@number int )
    as begin
    	select * from test where number=@number;
    end;
    go
    
    --1st question Answer
    
    --(check this by executing multiple times)
    declare @next_number int,@start int,@end int;
    set @start=0;
    set @end=2;
    while(@start<@end)
    	begin
    	exec testsp @next_number output; --1st sp
    	--select @next_number;
    	exec testspdetails @number=@next_number; --2nd sp
    	set @start=@start+1;
    end
    --	select * from test;
    
    --drop table test;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi


    Sunday, September 15, 2019 8:39 AM
  • Hi Uzzal Kumar Hore,

    For your 2nd question, check the attached sample that can help you!

    --sample sp (2nd sp changed now for bringing error)
    create or alter procedure testspdetails (@number int )
    as begin
    set nocount on;
    begin try
    	declare @number_var varchar(10) ;
    	set @number_var=(cast(@number as varchar)+'s')
    	select * from test where number=@number_var;
    	end try
    	begin catch
    	DECLARE     @ErrorMessage NVARCHAR(4000)
                ,@ErrorSeverity INT
                ,@ErrorState INT
                ,@ErrorProcedure NVARCHAR(128)
    
    SELECT  @ErrorMessage   =   ERROR_MESSAGE(),
            @ErrorSeverity  =   ERROR_SEVERITY(),
            @ErrorState     =   ERROR_STATE(),
            @ErrorProcedure =   ERROR_PROCEDURE();
    
    RAISERROR (@ErrorMessage,   
               @ErrorSeverity,
               @ErrorState,
               @ErrorProcedure)
    	end catch;
    end;
    go
    
    --(check this by executing multiple times)
    declare @next_number int;
    exec testsp @next_number output; --1st sp
    exec testspdetails @number=@next_number; --2nd sp
    
    --output (sp name ='testspdetails' and input param value='3s')
    /*
    (1 row affected)
    Msg 50000, Level 16, State 1, Procedure testspdetails, Line 22 [Batch Start Line 30]
    Conversion failed when converting the varchar value '3s' to data type int.
    */
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi


    Sunday, September 15, 2019 8:49 AM
  • A stored procedure can be executed multiple times using a WHILE loop. It depends on circumstances, therefore, give some details about the values, etc.

    Sunday, September 15, 2019 9:15 AM
  • Hi Uzzal Kumar Hore,

    For your second question, you could also get some ideas from this one

    CREATE TABLE [dbo].[Error](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [ErrorDate] [datetime] NOT NULL,
        [ErrorNumber] [nvarchar](max) NULL,
        [ErrorSeverity] [nvarchar](max) NULL,
        [ErrorState] [nvarchar](max) NULL,
        [ErrorProcedure] [nvarchar](max) NULL,
        [ErrorLine] [nvarchar](max) NULL,
        [ErrorMessage] [nvarchar](max) NULL
    )
    GO
    
    ALTER TABLE [dbo].[Error] ADD  CONSTRAINT [DF_Error_ErrorDate]  DEFAULT (getdate()) FOR [ErrorDate]
    GO
    CREATE PROCEDURE [dbo].[prc]
    WITH
    EXECUTE AS CALLER
    AS
    BEGIN
        SET XACT_ABORT, NOCOUNT ON
        DECLARE @starttrancount INT
        Begin TRY
            SELECT @starttrancount = @@TRANCOUNT
            IF @starttrancount = 0
            BEGIN TRANSACTION
    
    	-- Your own code start
    	Execute abcdeed
            -- Your own code end
    
    	IF @starttrancount = 0
            COMMIT TRANSACTION
    	End Try
        Begin Catch
            -- Test if the transaction is uncommittable.
            IF  XACT_STATE() <> 0 AND @starttrancount = 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
    	insert into Error(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
            SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()
    	End Catch
    END    

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 6:49 AM