Get output in dynamic query

Question

I am creating a stored procedure where I want to get output parameter value. That is @RecordCount INT OUTPUT in blow query. But I am unable to get the OUTPUT parameter in value from below SP.

ALTER PROCEDURE [dbo].[GetOrderListByClient]                
 @PageIndex INT = 1                
  ,@PageSize INT = 10                
  ,@orderNumber varchar(15) = null,           
  @ref varchar(255) = null,                
  @OrderstartDate date = null ,                
  @OrderEndDate date= null ,                
  @ClientId varchar(1000) ,                
  @RecordCount INT OUTPUT                
AS        
BEGIN 
   IF  @OrderEndDate IS NULL Or @OrderEndDate = ''      
         SET @OrderEndDate =CONVERT(Varchar(14), GETDATE(),101)   


  SET @OrderstartDate = CONVERT(varchar(10),@OrderstartDate,101)                
  SET @OrderEndDate =CONVERT(VARCHAR(10),@OrderEndDate,101)                
  SET NOCOUNT ON;

    DECLARE @QUERY VARCHAR(MAX)  
    DECLARE @FINALQUERY VARCHAR(MAX)    
    DECLARE @QUERY2 VARCHAR(MAX)




    SET @QUERY =

 ' 


  DECLARE @temp table                
    (                 
     RowNumber int ,                
     ORDERID int,                
     ORDERNUM varchar(15) NULL,                
     READYDATE date NULL,                
     ref varchar(255) NULL,                
     PADDR varchar(40) NULL,                
     DADDR varchar(40) NULL,                
     DCity varchar(25) NULL,                
     readyTime datetime2(7) NULL,                
     PICKTIME time null,                
     DRV1ID int null,                
     TOTAL decimal(10,2) NULL,                
     DeliveryTime time null,          
     PCITY varchar(40) null,          
     PICKUP varchar(40) null,          
     DROPOFF varchar(40) null,    
     PICTURECOUNT int                  
     )  



 INSERT INTO @temp                
    SELECT ROW_NUMBER() OVER                
  (                
        ORDER BY [ORDERNUM] DESC                
  )AS RowNumber                
  ,O.ORDERID, O.ORDERNUM, CONVERT(VARCHAR(11),CAST(O.READYDATE as DATE) ) AS READYDATE,O.Ref,O.PADDR,O.DADDR,          
  O.DCity,CONVERT(VARCHAR(5),O.readyTime,14)AS readyTime,CONVERT (VARCHAR(5),CAST(O.PICKTIME as Time)) AS PICKTIME,          
  O.DRV1ID,O.TOTAL,CONVERT (VARCHAR(5),CAST(O.DELIVERBY as Time)) AS DeliveryTime,PCITY,PICKUP,DROPOFF,O.PICTURECOUNT                           
  --INTO @temp                
  from Orde_  O                 
  WHERE  '

  IF (@QUERY <>'')
       SET @QUERY = @QUERY + ' CANCELLED = 0 ' 
   ELSE
       SET @QUERY = @QUERY + ' AND CANCELLED = 0 '

  IF (@QUERY2 ='')
       SET @QUERY = @QUERY +  ' CLIENTID IN (' +  @ClientId +')'
   ELSE
       SET @QUERY = @QUERY +  ' AND CLIENTID IN (' +  @ClientId +')'


  --if( @orderNumber <>'')
  --    SET @QUERY2 = @QUERY + ' AND ORDERNUM = '''+ @orderNumber+''

  IF (@ref <> '')
    BEGIN
      IF @QUERY2 = ''
           SET @QUERY = @QUERY + ' REF Like %'''+ @ref + '%'''
      ELSE
           SET @QUERY = @QUERY + ' AND  REF Like %'''+ @ref+ '%'''
    END 



  --  AND ((READYDATE  BETWEEN '''+ CONVERT(VARCHAR(10), @OrderstartDate,101)+''' AND  '''+  CONVERT(VARCHAR(10),@OrderEndDate,101)+''')  OR ( '+  CONVERT(VARCHAR(10), @OrderstartDate,101)+' IS NULL OR READYDATE >= '''+ CONVERT(VARCHAR(10),@OrderstartDate,101)+''' )       
  --  AND ('''+ CONVERT(VARCHAR(10), @OrderEndDate,101)+''' IS NULL OR READYDATE <= '''+CONVERT(VARCHAR(10),@OrderEndDate,101)+'''))) '     

    SET @QUERY  = @QUERY + '         

     SELECT  @RecordCount= COUNT(1)  FROM @temp                 

    SELECT * FROM @temp                
     WHERE RowNumber BETWEEN ( '+ CONVERT(VARCHAR(20), @PageIndex) +' -1) * '+ CONVERT(VARCHAR(20),@PageSize) +' + 1 AND((( '+ CONVERT(VARCHAR(10),@PageIndex)+' -1) * '+ CONVERT(VARCHAR(10),@PageSize)+' + 1) + '+ CONVERT(VARCHAR(10),@PageSize)+') - 1 '               


   PRINT  @QUERY
   --exec   (@QUERY)  

  -- Execute sp_Executesql @Query , @PageIndex,@PageSize,@OrderstartDate,@OrderEndDate,@ClientId, @RecordCount OUTPUT        
 -- DROP TABLE #Results                
END

Why are you injecting your parameters? Always parameters your queries, and that includes your dynamic statements. Dos and Don'ts of Dynamic SQL — Thom A
– Thom A ♦, Commented Nov 28, 2019 at 8:57
Also, what do you mean by "But I am unable to get the OUTPUT in value from below SP"? Do you mean the dataset, or of a variable? You have to use an OUTPUT parameter to get the value of a Scalar value. — Thom A
– Thom A ♦, Commented Nov 28, 2019 at 8:58
@Larnu ` @RecordCount INT OUTPUT ` i used and i want the value in this that is total record count — A.Goutam
– A.Goutam, Commented Nov 28, 2019 at 9:01

Thom A · Accepted Answer · 2019-11-28 09:36:50Z

This is an example query, however, it should get you on the right path. What you have a above is a huge security problem, and needs fixing. You don't have any dynamic objects in your query, you're only using dynamic SQL due to the fact that the query is a "Catch-all Query". This method is fine for such queries (and I personally recommend it), but you must parametrise your query (again, Dos and Don'ts of Dynamic SQL).

This query, below, at least shows you how you would craete a dynamic query, with an OUTPUT parameter:

CREATE TABLE dbo.YourTable (ID int IDENTITY,
                            SomeString varchar(25),
                            SomeInt int)
GO                          
CREATE PROC dbo.YourProc @SomeString varchar(25) = NULL, @SomeInt int = NULL, @RowCount int OUTPUT AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);


    SET @SQL = N'SELECT @RowCount = COUNT(*)' + @CRLF +
               N'FROM dbo.YourTable' + @CRLF +
               CASE WHEN @SomeString IS NOT NULL OR @SomeInt IS NOT NULL
                         THEN N'WHERE ' + STUFF(CASE WHEN @SomeString IS NOT NULL THEN @CRLF + N'  AND SomeString = @SomeString' ELSE N'' END +
                                                CASE WHEN @SomeInt IS NOT NULL THEN @CRLF + N'  AND SomeInt = @SomeInt' ELSE N''END,1,8,N'')
                         ELSE ''
               END + N';';

    --PRINT @SQL; --Your debugging best friend.
                  --You'll need SELECT for queries over 4,000 characters (but don't leave that (uncommented) in your SP!

    EXEC sp_executesql @SQL, N'@SomeString varchar(25), @SomeInt int, @RowCount int OUTPUT', @SomeString, @SomeInt, @RowCount OUTPUT;

END;
GO

DECLARE @RowCount int;
EXEC dbo.YourProc @SomeString = NULL,
                  @SomeInt = NULL,
                  @RowCount = @RowCount OUTPUT;

SELECT @RowCount; --0
GO

INSERT INTO dbo.YourTable (SomeString,
                           SomeInt)
VALUES('sdfgsdfg',1),
      ('sdfjhsdgfs',1),
      ('sdfgkhjdfbgk',2);
GO

DECLARE @RowCount int;
EXEC dbo.YourProc @SomeString = NULL,
                  @SomeInt = 1,
                  @RowCount = @RowCount OUTPUT;
SELECT @RowCount; --2
GO

DECLARE @RowCount int;
EXEC dbo.YourProc @SomeString = 'sdfjhsdgfs',
                  @SomeInt = 1,
                  @RowCount = @RowCount OUTPUT;
SELECT @RowCount; --1

GO

DROP PROC dbo.YourProc;
DROP TABLE dbo.YourTable;

this helps me EXEC sp_executesql @SQL, N'@SomeString varchar(25), @SomeInt int, @RowCount int OUTPUT', @SomeString, @SomeInt, @RowCount OUTPUT;

Ajeet Verma · Accepted Answer · 2019-11-28 09:09:54Z

Please try below query and let me know whether the issue is resolved or not.

ALTER PROCEDURE [dbo].[GetOrderListByClient] @PageIndex INT = 1
    ,@PageSize INT = 10
    ,@orderNumber VARCHAR(15) = NULL
    ,@ref VARCHAR(255) = NULL
    ,@OrderstartDate DATE = NULL
    ,@OrderEndDate DATE = NULL
    ,@ClientId VARCHAR(1000)
    ,@RecordCount INT OUTPUT
AS
BEGIN
    IF @OrderEndDate IS NULL
        OR @OrderEndDate = ''
        SET @OrderEndDate = CONVERT(VARCHAR(14), GETDATE(), 101)
    SET @OrderstartDate = CONVERT(VARCHAR(10), @OrderstartDate, 101)
    SET @OrderEndDate = CONVERT(VARCHAR(10), @OrderEndDate, 101)
    SET NOCOUNT ON;

    DECLARE @QUERY VARCHAR(MAX)
    DECLARE @FINALQUERY VARCHAR(MAX)
    DECLARE @QUERY2 VARCHAR(MAX)

    SET @QUERY = 
        ' 
DECLARE @temp table                
    (                 
     RowNumber int ,                
     ORDERID int,                
     ORDERNUM varchar(15) NULL,                
     READYDATE date NULL,                
     ref varchar(255) NULL,                
     PADDR varchar(40) NULL,                
     DADDR varchar(40) NULL,                
     DCity varchar(25) NULL,                
     readyTime datetime2(7) NULL,                
     PICKTIME time null,                
     DRV1ID int null,                
     TOTAL decimal(10,2) NULL,                
     DeliveryTime time null,          
     PCITY varchar(40) null,          
     PICKUP varchar(40) null,          
     DROPOFF varchar(40) null,    
     PICTURECOUNT int                  
     )  



 INSERT INTO @temp                
    SELECT ROW_NUMBER() OVER                
  (                
        ORDER BY [ORDERNUM] DESC                
  )AS RowNumber                
  ,O.ORDERID, O.ORDERNUM, CONVERT(VARCHAR(11),CAST(O.READYDATE as DATE) ) AS READYDATE,O.Ref,O.PADDR,O.DADDR,          
  O.DCity,CONVERT(VARCHAR(5),O.readyTime,14)AS readyTime,CONVERT (VARCHAR(5),CAST(O.PICKTIME as Time)) AS PICKTIME,          
  O.DRV1ID,O.TOTAL,CONVERT (VARCHAR(5),CAST(O.DELIVERBY as Time)) AS DeliveryTime,PCITY,PICKUP,DROPOFF,O.PICTURECOUNT                           
  --INTO @temp                
  from Orde_  O                 
  WHERE  '

    IF (@QUERY <> '')
        SET @QUERY = @QUERY + ' CANCELLED = 0 '
    ELSE
        SET @QUERY = @QUERY + ' AND CANCELLED = 0 '

    IF (@QUERY2 = '')
        SET @QUERY = @QUERY + ' CLIENTID IN (' + @ClientId + ')'
    ELSE
        SET @QUERY = @QUERY + ' AND CLIENTID IN (' + @ClientId + ')'

    --if( @orderNumber <>'')
    --    SET @QUERY2 = @QUERY + ' AND ORDERNUM = '''+ @orderNumber+''
    IF (@ref <> '')
    BEGIN
        IF @QUERY2 = ''
            SET @QUERY = @QUERY + ' REF Like %''' + @ref + '%'''
        ELSE
            SET @QUERY = @QUERY + ' AND  REF Like %''' + @ref + '%'''
    END

    --  AND ((READYDATE  BETWEEN '''+ CONVERT(VARCHAR(10), @OrderstartDate,101)+''' AND  '''+  CONVERT(VARCHAR(10),@OrderEndDate,101)+''')  OR ( '+  CONVERT(VARCHAR(10), @OrderstartDate,101)+' IS NULL OR READYDATE >= '''+ CONVERT(VARCHAR(10),@OrderstartDate,101)+''' )       
    --  AND ('''+ CONVERT(VARCHAR(10), @OrderEndDate,101)+''' IS NULL OR READYDATE <= '''+CONVERT(VARCHAR(10),@OrderEndDate,101)+'''))) '     
    SET @QUERY = @QUERY + '         

     SELECT  @RecordCount= COUNT(1)  FROM @temp ' + '             

    SELECT * FROM @temp                
     WHERE RowNumber BETWEEN (' + '+ CONVERT(VARCHAR(20), @PageIndex) ' + ' -1) * ' + ' CONVERT(VARCHAR(20),@PageSize)' + ' 1 AND((( ' + 'CONVERT(VARCHAR(10),@PageIndex)' + ' -1) * ' + ' CONVERT(VARCHAR(10),@PageSize)' + ' 1) ' + '+ CONVERT(VARCHAR(10),@PageSize)' + ') - 1 '

    --PRINT @QUERY
        exec   (@QUERY)  
         Execute sp_Executesql @Query , @PageIndex,@PageSize,@OrderstartDate,@OrderEndDate,@ClientId, @RecordCount OUTPUT        
        -- DROP TABLE #Results                
END

i want to get output parameter value @RecordCount that i am using for total number of records
This does not solve any of the injection problems in the query, and this should not be used.

Collectives™ on Stack Overflow

Get output in dynamic query

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related