Call SQL stored proc with OUTPUT param from another stored proc

Question

It seems very simple solution, but I can't figure it out. Please help.

I have to call a stored proc with OUTPUT param from another stored proc. I think one of the issues is dynamic SQL, but I don't know how else to write it since @SQLWhere will change dynamically within C# code.

This is the proc being called from another proc:

ALTER PROCEDURE [dbo].[USP_RetrieveTotalRecord]
@SQLWhere AS NVARCHAR(1000),
@TotalRecordsFound as varchar(16) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @SQL AS NVARCHAR(Max)
    SET @SQL='Select @TotalRecordsFound = Count(Table_ID)  
    From TableName Where ' + @SQLWhere 
    EXEC(@SQL)  
    return
END

Here is how I am calling it from another proc:

Declare @TotalRec AS NVARCHAR(16);
Declare @SQLWhere AS NVARCHAR(1000);
SET @SQLWhere='Date Between ''12/13/2016'' AND ''12/14/2016'''
EXECUTE  USP_RetrieveTotalRecord @SQLWhere, @TotalRec output;

Here is the error I am trying to resolve:

Msg 137, Level 15, State 1, Line 30
Must declare the scalar variable "@TotalRecordsFound".

Ignoring the nightmare this could be for SQL injection, you need to parameterise your dynamic query to include output. EDIT: The answer here is an example: http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable-for-sql-server — ZLK, Jan 19 '17 at 21:50

DaveN · Answer 1 · 2017-01-19T22:11:27.690

0

Not sure why you have a 2nd SP, just use one like so:

Declare @TotalRec AS NVARCHAR(16);
Declare @SQLWhere AS NVARCHAR(1000);
SET @SQLWhere='Date Between ''12/13/2016'' AND ''12/14/2016'''
 SET @SQL='Select @TotalRecordsFound = Count(Table_ID)  
    From TableName Where ' + @SQLWhere 
 EXEC(@SQL)

Or use date variables if that's all you are using for selection (no dynamic sql necessary) - unless this is just a simplified example

--- comment section is broken, so, in response to get a value out, use something like this:

Ok - the simplest way is to use sp_ExecuteSQL

Declare @result int
Declare @sql nvarchar(max)
SET @SQL = ' SELECT COUNT(*) FROM MyTable'

exec sp_ExecuteSQL @sql, N' @Result int Output', @Result output

select @result as MyCount

edited Jan 19 '17 at 22:11

answered Jan 19 '17 at 21:32

DaveN

888
2
8
16

DaveN, You are right that this is a simplified version. There can be 1 to many criteria for WHERE clause. Actually, we don't need another proc. I agree with you. But how do I use @TotalRecordsFound in another 'Select' that follows. – SilverFish Jan 19 '17 at 21:43
Also, I need count in form of variable and not field that is why using @TotalRecordsFound, since I have to do calculation in next step – SilverFish Jan 19 '17 at 21:47

score 0 · Answer 2 · answered Jan 19 '17 at 21:51

Don't do what you are trying to do, Only pass values to stored procedure and then build the dynamic sql inside your procedure, something like ......

ALTER PROCEDURE [dbo].[USP_RetrieveTotalRecord]
   @StartDate   DATE = NULL
  ,@EndDate     DATE = NULL
  ,@TotalRecordsFound INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(Max);
    SET @SQL = N' Select @TotalRecordsFound = Count(Table_ID) '
             + N' From TableName Where 1 =1 '
             + CASE WHEN  @StartDate IS NOT NULL THEN 
               N' AND [Date] >= @StartDate ' ELSE N' ' END
             + CASE WHEN  @EndDate IS NOT NULL THEN 
               N' AND [Date] <= @EndDate ' ELSE N' ' END

    EXEC sp_executesql @SQL  
                      ,N'@StartDate DATE, @EndDate DATE, @TotalRecordsFound INT OUTPUT'
                      ,@StartDate 
                      ,@EndDate
                      ,@TotalRecordsFound OUTPUT
END

Now @EndDate and @StartDate variables are optional , depending on what variable values you pass procedure will build the dynamic sql accordingly and return the results.

Also using parameterised query with sp_executesql will protect you against a possible SQL-Injection attach and also your proc will benefit from parameterised execution plans.

M. Ali, I have to use send @SQLWhere from .NET code since it being built based on a number of selections. However, one thing I see is that you are using EXEC sp_executesql . I can try that. Please let me know how will you call this proc USP_RetrieveTotalRecord from another stored proc? — SilverFish, Jan 19 '17 at 22:03
@Silverfish well just get the values from your application layer , try not to build t-sql in your application code. Also pass valid `Date` values to your procedure so you can avoid implicit conversions and benefit from indexes on the table. — M.Ali, Jan 19 '17 at 22:07

score 0 · Answer 3 · answered Jan 19 '17 at 22:31

M. ali, thanks for your help, but we have all SELECT, WHERE, and GROUP by criteria being passed from the application after dynamic selections. I needed a quick fix. Finally I was able to resolve the issue using a temp table. I know they are not recommended, but I tried using Common table expression, table variable, but the @TotalRecordsFound was not visible outside the dynamic SQL. Therefore, created temp table, Inserted data into it using dynamic SQL, and then joined it with the next select statement.

Call SQL stored proc with OUTPUT param from another stored proc

3 Answers3