peter

peter

  • NA
  • 320
  • 0

Return Output value and datarows from dynamic query

Jan 14 2022 9:30 PM

i require to run seperate query statements using dynamic strings as such

@TOTALRECORDS  INTEGER =0 OUTPUT,

@SubCategoryID INT  =0

    set @WHERECLAUSE =' (Products.CategoryID = @SubCategoryID) '

set @TOTALRECORDS ='count(*) FROM Products WHERE  (Products.CATEGORYID=@SubCategoryID  )'

  SET @sql = 'SELECT * 

   FROM 
   (SELECT [ProductID],[VendorName],    
    Products.[CategoryID],Products.[Category],    
            ROW_NUMBER() OVER(ORDER BY  @sortExpression ) as RowNum FROM Products ' +   @WHERECLAUSE +  ' ) AS EmpInfo '

Execute sp_Executesql     @SQL 

I cannot get it to return the output value ?


Answers (8)