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

Thom A · Accepted Answer

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;

Get output in dynamic query

Answers (2)

Related Questions