? Running a Batch Process ?

[Stickarm]

I am trying to create a procedure to allow me to create one complete table using data from 2 seperate tables. I am able to do what I am trying to do but I feel I am being kind of repetative. I need to save this process as I will have to do this on new files periodically. I have been creating scripts using query analyzer to do this in real time, but have also save the procedures as stored procedures. The problem I have is that I wrote this procedure:

CREATE PROCEDURE sp_updateSutOB
AS
UPDATE tbl_distributorfinal
SET
tbl_distributorfinal.tt11status = 'Out of Business',
tbl_distributorfinal.tt11statusdate = suttm.reg_stat_date
from tbl_distributorfinal inner JOIN suttm
ON tbl_distributorfinal.taxreg = suttm.main_acct + suttm.sub_acct
where (((suttm.tax_type)='11') AND ((suttm.[reg_stat_code])='3'))


The Procedure works fine but i need to do it for 3 different suttm.tax_type 's (11,12 and 13) and 5 different suttm.[reg_stat_code].'s (1,2,3,4 and 5) This would require me to write 15 seperate procedures. Is there any way to batch this or include it in one procedure?

Thanks in advance,
Phil

 

[tlbroadbent]

SQL Server Stored Procedures can accept parameters.

CREATE PROCEDURE sp_updateSutOB
@taxtype char(2), @statcode char(1)
AS

UPDATE tbl_distributorfinal
SET
tbl_distributorfinal.tt11status = 'Out of Business',
tbl_distributorfinal.tt11statusdate = suttm.reg_stat_date
from tbl_distributorfinal inner JOIN suttm
ON tbl_distributorfinal.taxreg = suttm.main_acct + suttm.sub_acct
WHERE suttm.tax_type= @taxtype
AND suttm.reg_stat_code=@statcode

Execute the SP:

Exec sp_updateSutOB '11','3' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[harryrich]

First of all you can put multiple SQL statements in a procedure, so there is nothing to prevent you from putting 15 update statements in your procedure.

However, if you look at the CREATE PROCEDURE statement, you will see that procedures can have parameters, so you might simplfy things by putting parameters in place of the constants in your procedure and calling it for each of the 15 alternatives from another procedure.

Finally, if you can fight your way through the syntax, there is a way of generating a list of values as a subquery. This could be joined to your other two tables to do the thing in a single SQL statement. For 15 instances, I think the approach in the previous paragraph would get you there fastest.

 

[Stickarm]

Thanks,
After I posted, I did put in parameters and it works fine. Thanks for your help