Dynamic stored procedure call problem

[voiD]

Hello,

First of all, Merry Christmas to everybody!

I've faced with a problem, what currently I can't solve. I want to call stored procedure from an other database with some input parameters and want to store the result of the stored procedure in a variable. All this I want to do dynamically.
My idea was to store the sp in a table with its parameters, somehow like this:

[dbname].[schemaname].sp_my_stored_proc @Param1=@Param1Value, @Param2=@Param2Value

The call of the sp would be done in an other sp which first reads the sp to be called from the table, then substitutes the @ParamXValue-s with actual values and then calls the stored procedure somehow like this

exec @Result = @SpToBeCalled

where @SpToBeCalled contains the stored procedure to be called with the substituted parameter values and @Result would contain the result of the stored procedure.
My problem is that, can't exec this way the sp, I got always error and it seems the MS SQL recognizes the value of @SpToBeCalled as a stored procedure name and says doesn't find such sp.
The above mentioned way is working well without parameters. Does anybody idea how could it be solved with parameters? Could it be solved at all?

Thank you in advance for any piece of ideas!

Regards,
VoiD

 

[WayneRyan]

Hi voiD,

Look at the Help for --> sp_ExecuteSql.

You'll have to use dynamic SQL for what you want.

hth,
Wayne

 

[Rx_]

Just to be clear on the question this cold and snowy early morning.
Is the SP being called on Another Database being called within a linked or local SQL Server Database?
thanks

 

[Minty]

I do something similar to loop around and email out various reports automatically, the only (significant) difference being that I don't need any return values. The sProcNames are pulled in from a table of predefined reports, then stored temporarily to give me a table to loop around. Not sure if it will help;

ALTER PROCEDURE [dbo].[Reporting_Daily_List] 
AS
BEGIN

DECLARE @Proc nvarchar(100),
@RowCnt int,
@MaxRows int,
@acct as int,
@emailAddy as nvarchar(200),
@ccMail as nvarchar(300)

SET @RowCnt = 1

-- These next two rows are specific to the source table or query
DECLARE @Import TABLE (rownum int IDENTITY (1, 1) Primary key NOT NULL , Acc_No int, EmailAdd nvarchar(300), CCEmailAdd nvarchar(300),sProcName nvarchar(100))
INSERT INTO @Import (Acc_No, EmailAdd, CCEmailAdd, sProcName) 
SELECT Account_No, RecipientEmail, CCEmailTo, ProcName 
FROM  DailyReportsList
WHERE RecipientEmail is not Null
GROUP BY Account_No, RecipientEmail, CCEmailTo, ProcName
 
SELECT @MaxRows=count(*) FROM @Import

-- For each row in the reports list that is returned run the SP for the report and send it to the listed email addresses
WHILE @RowCnt <= @MaxRows 
BEGIN
    SELECT @emailAddy = (SELECT EmailAdd FROM @Import WHERE rownum=@RowCnt) 
    SELECT @acct = (SELECT Acc_No FROM @Import WHERE rownum = @RowCnt)
    SELECT @ccMail = (SELECT CCEmailAdd FROM @Import WHERE rownum = @RowCnt)
    SELECT @Proc = ('dbo.' +(SELECT sProcName FROM @Import WHERE rownum = @RowCnt))
    EXEC @Proc @acct, @emailAddy , @ccMail
    SELECT @RowCnt = @RowCnt + 1
END
END