When I try to execute a stored proc ,from another stored proc, from the middle-tier -- nothing happens

Question

Hi:

We have the ability to execute stored procs from the middle-tier. Basically, in a database table called "SQLJobStep" we have -- among other things -- a varchar(300) column called "StoredProcedure" which holds something like this:

usp_SendReminderEmails @Debug=0, @LoginID

Via the middle-tier, a user clicks on a link to run the proc of their choice. What happens in the backend is a proc ("usp_SQLJobsActionGet") looks up the correct value from the "SQLJobStep.StoredProcedure" column and executes the value above.

This is the part of the code from "usp_SQLJobsActionGet" that executes the above value:

DECLARE @StepId int
DECLARE @LoginID varchar(12)
DECLARE @StoredProcedure varchar(300)

SET @StepId = 74
SET @LoginID = 'Yoav'

SELECT @StoredProcedure = SJS.StoredProcedure 
FROM SQLJobStep AS SJS
WHERE SJS.StepId = @StepId

SET @StoredProcedure = ISNULL(@StoredProcedure, '')

IF CHARINDEX('@LoginID', @StoredProcedure) > 0
BEGIN
    SET @LoginID = ISNULL(@LoginID, 'UNKNOWN')
    SET @StoredProcedure = REPLACE(@StoredProcedure, '@LoginID', '@LoginID = ''' + @LoginID + '''')
END

IF @StoredProcedure != ''
BEGIN
    EXEC(@StoredProcedure)
END    

Fairly simple stuff....

The above code converts the original value to (and then executes):

usp_SendReminderEmails @Debug=0, @LoginID = 'Yoav'

Here is the issue:

When executing the "usp_SendReminderEmails @Debug=0, @LoginID = 'Yoav'" value nothing happens. No error is returned to the middle-tier. But I know that a value is pulled from the SQLJobStep table because we have other stored procedure values that get pulled and they run fine. (Note that the other values only have the @LoginID parameter, while this has @Debug=0 as well.)

At the same time, if I run the code above (both the gutted code and calling "usp_SQLJobsActionGet" directly) in SQL Management Studio, it works perfectly.

Do you have any advice? I am sure I am missing something very basic.

Thanks.

Answer 1

My advice? Use sp_ExecuteSQL instead of concatenation / replacement:

IF @StoredProcedure != ''
BEGIN
    EXEC sp_ExecuteSQL @StoredProcedure, N'@LoginID varchar(12)', @LoginID
END

Overall, though - the EXEC should work; are you sure that @StoredProcedure is not empty?

Answer 2

Hi everyone:

Thanks for helping. I found the answer to my issue, and as you can guess it had to do with issues beyond what I described originally:

In the usp_SendReminderEmails proc, we call another proc in order to audit each e-mail record that is sent. This auditing proc inserts a record into an audit table and then returns the identity (SELECT TOP 1 SCOPE_IDENTITY()). While it only returns 1 record at a time, it happens to be called in a cursor (in usp_SendReminderEmails) to send out each email at a time (note: this is a SQL Job proc).

What I noticed is that upon executing usp_SendReminderEmails @Debug=0, @LoginID = 'Yoav' in Management Studio, it works fine but there is a warning returned(!):

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

When calling the proc from the middle-tier, therefore, nothing happened - no error returned, but no processing of usp_SendReminderEmails either. I fixed it by calling the audit proc in an insert into temp table in usp_SendReminderEmails, thereby ensuring it doesn't get returned (since it's only an identity value):

INSERT INTO #AuditData (AuditDataId)
EXEC usp_AuditSave