Return Output value and datarows from dynamic query

Question

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 ?

Muhammad Imran Ansari · Answer

You need to pass parameters to sp_executeSQL procedure. To demonstrate: I have created Product table like: DECLARE @TotalRecords BIGINT; DECLARE @sqlQuery NVARCHAR(MAX) DECLARE @WHERECLAUSE NVARCHAR(500) = ' WHERE (Products.CategoryID = @SubCategoryID) ' DECLARE @params1 NVARCHAR(255) = '@TotalRecords BIGINT OUTPUT, @SubCategoryID INT' DECLARE @params2 NVARCHAR(255) = '@SubCategoryID INT, @sortExpression NVARCHAR(50)' DECLARE @SQL NVARCHAR(4000) = 'SELECT @TotalRecords = COUNT(*) FROM Products WHERE Products.CATEGORYID = @SubCategoryID ' EXEC sp_executeSQL @SQL, @params1, @TotalRecords = @TotalRecords OUTPUT, @SubCategoryID = 1 SELECT @TotalRecords [TotalRecords] SET @sqlQuery = 'SELECT * FROM (SELECT [ProductID],[VendorName], Products.[CategoryID],Products.[Category], ROW_NUMBER() OVER(ORDER BY ProductID ) as RowNum FROM Products ' + @WHERECLAUSE + ' ) AS EmpInfo ' EXEC sp_executeSQL @sqlQuery, @params2, @SubCategoryID = 1, @sortExpression = 'ProductID' Output: You can modify your query accordingly.

Nitin Sontakke · Answer

Frankly, I am little lost as to why there are single quotes around the query which is assigning record counts. In my opinion, if it is a SQL Server, the following line of code should work just fine. select @TOTALRECORDS = count (*) FROM Products WHERE Products.CATEGORYID = @SubCategoryID You may also want to share the piece of code where you are retrieving value of output parameter. Hope you are aware that output parameter must be explitly mentioned as being output while calling stored procedure from code like C#. A locally declared and preferably initialized variable must be passed. Furthermore, their value must be explicitly retrieved from the parameter VARIABLE declared while making a stored procedure call.

peter · Answer

The code provided was intended to show the relevant parts. I just wanted to find out how to return output variable to calling method using the above logic. In my SP i have a series of where clauses that i execute on condition of null column values. That works perfectly! I now need to return a count of all rows

Nitin Sontakke · Answer

Is the above posted code part of a stored procedure? If yes, it is better to show entire stored procedure, may be removing irrelevant part to the question. Where is @sortExpression coming from? Which part of null you intend to handle? For example, do you want all products if @SubcategoryId is null? If yes, there is better way to handle it. You don't necessary need a dynamic SQL. For example, you can have WHERE clause as follows: where Products.CategoryId = isnull(@CategoryId, Products.CategoryId) In above case all products will be selected if @CategoryId is null.

Nitin Sontakke · Answer

Hello, At the moment, just acknowledging that I have read your response. Thank you! It is bit late here in India (almost half past midnight) so will respond you tomorrow, Sorry!

peter · Answer

There is a problem with this site.It frequently does not allow editing and pasting text on reply? i use dynamic string because of variable data set on condition of null statements and pagation as described. Yes,its for pagation purpose therefore cannot get record count from returned datarows only a subset value. Not certain of SQL version operating as i have multiple versions in existance on system. I think they are causing connection problems; for instance i am unable to test performance of SP from the designer window? As it says file not found or incompatible datafile? I believe the location is displaces and i cannot reset using the configstring settings per norm.Although the Data retrieval operates fine! If i remove the quote marks i cannot SET the code unless i use a SELECT clause. When i want to append to the query on a where filter on condition statement action.

peter · Answer

I am unable to respond?

Nitin Sontakke · Answer

Hello, If you could please answer following questions, that would be very helpful. 1/ Is there any specific reason why you intend to use dynamic SQL? 2/ If non-dynamic SQL solution solves your business problem, will you accept it? 3/ Is above code a part of stored procedure? 4/ Which version of SQL Server you are using? 5/ Do you need total records for pagination purpose? On line where you are setting @TotalRecords variable, you will need to remove the single quotation marks. Please note that sometimes we need to question the answers we have. It is only then that new answers start to emerge. Hope you get the point. Thank you!