I have a table that has three columns: storedprocedure, datacheck, isactiveeligibilty. Stored procedure is the name of the stored procedure I need executed. Data check is the number of the check being performed. Isactiveeligibility indicates whether or not to run the procedure. I am using this loop to execute all the procedures in a table. This loop needs to pass the datacheck parameter to each of the stored procedures. I'm not sure how to add the @DataCheck to the EXEC statment. I hope this makes sense and someone can offer some advice. Thanks.
LOOP
__________________________________________________________________________
CREATE PROCEDURE usp_Eligibility_DataCheck_Loop_TEST
AS
DECLARE @id INTEGER
DECLARE @MaxLoopid INTEGER
DECLARE @ProcName VARCHAR(500)
DECLARE @DataCheck INTEGER
SELECT IDENTITY(int, 1,1) AS ID, DataCheck, StoredProcedure
INTO #tblDataCheckLoop
FROM tblDataChecks
WHERE IsActiveEligibility =1
SELECT @id =1,@MaxLoopid=max(id) from #tblDataCheckLoop
WHILE @id <= @MaxLoopid
BEGIN
SELECT @DataCheck = DataCheck, @ProcName = StoredProcedure
FROM #tblDataCheckLoop
WHERE id = @id
EXEC('exec ' + @ProcName)
SET @id = @id + 1
END
DROP TABLE #tblDataCheckLoop
_______________________________________________________________________________
SP PARAMETER NEEDS TO PASS TO
_______________________________________________________________________________
ALTER PROCEDURE usp_Eligibility_DC_Rate
@DataCheck INTEGER
AS
UPDATE tblEligibility
SET DataCheck = @DataCheck, DataCheckExclusion = LN_Bal
FROM tblEligibility e
INNER JOIN shawreporting.dbo.tblNTPool p ON e.M_UF_Num5_1 = p.ntpool
WHERE (Rate < .049 OR Rate > .35)
AND e.DataCheckExclusion = 0
AND e.DataCheck IS NULL
AND p.IsWarehouse = 1
____________________________________________________________________________________
LOOP
__________________________________________________________________________
CREATE PROCEDURE usp_Eligibility_DataCheck_Loop_TEST
AS
DECLARE @id INTEGER
DECLARE @MaxLoopid INTEGER
DECLARE @ProcName VARCHAR(500)
DECLARE @DataCheck INTEGER
SELECT IDENTITY(int, 1,1) AS ID, DataCheck, StoredProcedure
INTO #tblDataCheckLoop
FROM tblDataChecks
WHERE IsActiveEligibility =1
SELECT @id =1,@MaxLoopid=max(id) from #tblDataCheckLoop
WHILE @id <= @MaxLoopid
BEGIN
SELECT @DataCheck = DataCheck, @ProcName = StoredProcedure
FROM #tblDataCheckLoop
WHERE id = @id
EXEC('exec ' + @ProcName)
SET @id = @id + 1
END
DROP TABLE #tblDataCheckLoop
_______________________________________________________________________________
SP PARAMETER NEEDS TO PASS TO
_______________________________________________________________________________
ALTER PROCEDURE usp_Eligibility_DC_Rate
@DataCheck INTEGER
AS
UPDATE tblEligibility
SET DataCheck = @DataCheck, DataCheckExclusion = LN_Bal
FROM tblEligibility e
INNER JOIN shawreporting.dbo.tblNTPool p ON e.M_UF_Num5_1 = p.ntpool
WHERE (Rate < .049 OR Rate > .35)
AND e.DataCheckExclusion = 0
AND e.DataCheck IS NULL
AND p.IsWarehouse = 1
____________________________________________________________________________________