Menu
I have a TSQL sproc that builds a query as and executes it as follows:
EXEC (@sqlTop + @sqlBody + @sqlBottom) @sqlTop contains something like SELECT TOP(x) col1, col2, col3...
TOP(x) will limit the rows returned, so later I want to know what the actual number of rows in the table is that match the query.
I then replace @sqlTop with something like:
EXEC ('SELECT @ActualNumberOfResults = COUNT(*) ' + @sqlBody) I can see why this is not working, and why a value not declared error occurs, but I think it adequately describes what I'm trying to accomplish.
Any ideas?
849
In SQL, you can make a database query and use the COUNT function to get the number of rows for a particular group in the table. Here is the basic syntax: SELECT COUNT(column_name) FROM table_name; COUNT(column_name) will not include NULL values as part of the count.
MySQL ROW_COUNT() can be used to get the total number of rows affected by MySQL query. To illustrate it we are creating a procedure with the help of which we can insert records in a table and it will show us how many rows have been affected.
Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
use sp_executesql and an output parameter
example
DECLARE @sqlBody VARCHAR(500),@TableCount INT, @SQL NVARCHAR(1000) SELECT @sqlBody = 'from sysobjects' SELECT @SQL = N'SELECT @TableCount = COUNT(*) ' + @sqlBody EXEC sp_executesql @SQL, N'@TableCount INT OUTPUT', @TableCount OUTPUT SELECT @TableCount GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
