Passing Parameter from one SP to another

[scoobyroo]

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
____________________________________________________________________________________

 

[rickj65]

I believe if you set up a string variable and pass the dynamic SQL statement (EXEC proc statement) with all the parameters, this should do what you are looking for.

DECLARE @strSQL varchar(500)

SET @strSQL = 'exec ' + @ProcName + ' ' + CONVERT(varchar(3), @DataCheck)

EXEC(@strSQL)