SQL Server dynamic table variables and returning field variables from these tables

Question

I am struggling to make this logic work in my stored procedure. My logic is simple. I have two stored procedures called in another stored procedure (see code below).

The idea is to return a date variable or the count of the records based on the input provided and populate values in the main stored procedure variables

The procedures run fine but I come across the following problems:

dbo.GetLastDate doesn't return a date value
dbo.usr_GetRecCount always returns 0, even when there are records in the table

Main stored procedure:

    ....
                Exec dbo.usr_GetLastDate @TableName, @LastTransDate
                EXEC dbo.usr_GetRecCount @TableName, @RecCount
    ...

dbo.usr_GetLastDate:

   ALTER PROCEDURE [dbo].[usr_GetLastDate]
    -- Add the parameters for the stored procedure here
    @TableName SYSNAME,
    @OUTDATE date Output
AS
BEGIN
    SET NOCOUNT ON;

   DECLARE @SQLCommand NVARCHAR(MAX) = 
        N'SELECT @OUTDATE=MAX(TRANSDATE) FROM ' + QUOTENAME(@TableName);

  EXECUTE [dbo].[sp_executesql] 
             @sqlCommand, N'@OUTDATE DATE OUTPUT', @OUTDATE=@OUTDATE OUTPUT;

END

`dbo.usr_GetRecCount`:

   ALTER PROCEDURE [dbo].[usr_GetRecCount]
    -- Add the parameters for the stored procedure here
    @ViewName SYSNAME,
    @OUTINT int output
AS
BEGIN

    SET NOCOUNT ON;

   DECLARE @SQLCommand NVARCHAR(MAX) = 
        N'SELECT @OUTINT=COUNT(SYMBOL) FROM ' + QUOTENAME(@ViewName);


   EXECUTE [dbo].[sp_executesql] 
             @sqlCommand, N'@OUTINT INT OUTPUT', @OUTINT=@OUTINT OUTPUT;
END

Maye you should add a space after the FROM in your SQL statements - otherwise your statement will be SELECT COUNT(Symbol) FROMMytable and that might just not work .... — marc_s
– marc_s, Commented Oct 24, 2015 at 13:15
I fixed that still doesn't work. Also added the output parameter in the SP — HDB
– HDB, Commented Oct 24, 2015 at 22:52
What does "not work" mean? This might be how you are calling them. The parameter has to be declared OUTPUT when the stored procedure is executed. — Gordon Linoff
– Gordon Linoff, Commented Oct 24, 2015 at 23:12

Gordon Linoff · Accepted Answer · 2015-10-24 14:57:57Z

3

Use sp_executesql with an explicit OUTPUT parameter:

DECLARE @SQLCommand NVARCHAR(MAX) = 
            N'SELECT @OUTDATE = MAX(TRANSDATE) FROM ' + QUOTENAME(@TableName);

DECLARE @OUTDATE DATE;

EXECUTE sp_executesql @sqlCommand,
        N'@OUTDATE DATE OUTPUT',
        @OUTDATE=@OUTDATE OUTPUT;

A similar structure should be used for the second query as well.

edited Oct 24, 2015 at 14:57

answered Oct 24, 2015 at 13:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HDB Over a year ago

Gordon: I tried this, but it did not work and still returned blank value for my date (@LastTransDate) and 0 for the record count (@RecCount)

Dan Guzman Over a year ago

Specify the OUTPUT keyword in the parameter declaration and parameter value assignment: N'@OUTDATE DATE OUTPUT', @OUTDATE=@OUTDATE OUTPUT

HDB Over a year ago

I did make those changes but still nothing. here is the code below

HDB Over a year ago

I made all the recommended changes but still having issues. Must be missing something critical, I;ll keep looking ... made some changes to the code .... anything wrong that u guys see?

HDB Over a year ago

Adding an output flag at the end when calling the SP did the trick

M.Ali · Accepted Answer · 2015-10-24 14:44:08Z

0

You need to declare and pass this variable as OUTPUT to get a value from the sql statement.

DECLARE @SQL NVARCHAR(MAX), @Value DATE;

 SET @SQL =  N'SELECT @Value = MAX(TRANSDATE) FROM ' + QUOTENAME(@TableName);

EXECUTE [dbo].[sp_executesql] @SQL
                             ,N'@Value Date OUTPUT'
                             ,@Value OUTPUT

SELECT @Value AS RtnValue

answered Oct 24, 2015 at 14:44

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Comments

HDB · Accepted Answer · 2015-10-25 13:42:05Z

Thank you all for your help. After adding corrections suggested by some of you this the final code that worked

** Main Procedure **
Exec dbo.usr_GetLastDate @TableName, @LastTransDate OUTPUT
EXEC dbo.usr_GetRecCount @TableName, @RecCount OUTPUT

ALTER PROCEDURE [dbo].[usr_GetLastDate]
    -- Add the parameters for the stored procedure here
    @TableName SYSNAME,
    @OUTDATE date Output
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
   DECLARE @SQLCommand NVARCHAR(MAX) = 
        N'SELECT @OUTDATE=MAX(TRANSDATE) FROM ' + QUOTENAME(@TableName);

   EXECUTE [dbo].[sp_executesql] 
             @sqlCommand, N'@OUTDATE DATE OUTPUT', @OUTDATE=@OUTDATE OUTPUT;

END

ALTER PROCEDURE [dbo].[usr_GetRecCount]
    -- Add the parameters for the stored procedure here
    @TableName SYSNAME,
    @OUTINT int output
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
   DECLARE @SQLCommand NVARCHAR(MAX) = 
        N'SELECT @OUTINT=COUNT(SYMBOL) FROM ' + QUOTENAME(@TableName);


   EXECUTE [dbo].[sp_executesql] 
             @sqlCommand, N'@OUTINT INT OUTPUT', @OUTINT=@OUTINT OUTPUT;
END

Collectives™ on Stack Overflow

SQL Server dynamic table variables and returning field variables from these tables

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related