CODEWITHSUNDEEP
sql-server-2008stored-procedures
Ryan Abarquez
Ryan Abarquez

Reputation: 307

How to Run a Stored Procedure using the Table Values as Parameters

I have a stored procedure intended to insert data into a table and it has 6 parameters

SP:

CREATE PROCEDURE [dbo].[sp_tb_mis_am_node]
@pid_code varchar(10),
@id_code varchar(10),
@description varchar(50),
@disp_order int, /*** This will serve as a display order sequence ***/
@username varchar(20),
@user_key int,

Now i want to run this SP and populate its parameters using the values in my existing table with the same number and name of column

Table:

SELECT  [pid_code]
      ,[id_code]
      ,[description]
      ,[disp_order]
      ,[username]
      ,[user_key]
  FROM [webloan_helper].[dbo].[tbl_ledger_add]

How will I execute it?

I'm using Microsoft SQL Server 2008 R2

Upvotes: 1

Views: 76

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

If re-writing your stored procedure1,2 is not on the cards, and nor is incorporating the body into your current method2, then your only real choice remaining is to loop.

To loop, you use a cursor and a WHILE, something like:

DECLARE @pid_code varchar(10)
DECLARE @id_code varchar(10)
DECLARE @description varchar(50)
DECLARE @disp_order int
DECLARE @username varchar(20)
DECLARE @user_key int

DECLARE boris cursor local fast_forward FOR SELECT  [pid_code]
      ,[id_code]
      ,[description]
      ,[disp_order]
      ,[username]
      ,[user_key]
  FROM [webloan_helper].[dbo].[tbl_ledger_add]

OPEN boris
FETCH NEXT FROM boris INTO @pid_code,@id_code,@description,
                           @disp_order,@username,@user_key

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC [sp_tb_mis_am_node] @pid_code,@id_code,@description,
                           @disp_order,@username,@user_key

    FETCH NEXT FROM boris INTO @pid_code,@id_code,@description,
                           @disp_order,@username,@user_key
END
CLOSE boris
DEALLOCATE boris

1There is one bit of re-writing of the current procedure that I would recommend, however:

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

2With the intention, in either case, that you re-write them to be set-based rather than working with a single row. For the re-write of the stored procedure instance, this would mean taking a table-valued parameter.

Upvotes: 1

VSIA
VSIA

Reputation: 11

Create Procedure ExecuteProc AS Begin Declare @RowNumber INT; -- Row number of Table webloan_helper SET @RowNumber = 1;

While (@RowNumber <= (SELECT COUNT(*) FROM webloan_helper))
Begin
    Declare @pid_code varchar(10);
    Declare @id_code varchar(10);
    Declare @description varchar(50);
    Declare @disp_order int;
    Declare @username varchar(20);
    Declare @user_key int;

    Set @pid_code = (Select pid_code From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);
    Set @id_code = (Select id_code From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);
    Set @description = (Select [description] From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);
    Set @disp_order = (Select disp_order From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);
    Set @username = (Select username From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);
    Set @user_key = (Select user_key From (Select Row_Number() Over (Order By pid_code) As RowNum, * From webloan_helper) t2 Where RowNum = @RowNumber);

    Exec sp_tb_mis_am_node @pid_code,@id_code,@description,@disp_order,@username,@user_key;
     -- Order By Should be unique column

SET @RowNumber= @RowNumber+ 1; End

End

Upvotes: 1

Related Questions