August 3, 2002 at 12:04 pm
I have an application that stores user's SQL statments as text in a Varchar field within a data table. I would like to create a function (or stored procedure) that calculate the rows returned for a given SQL Statment.
I've discovered that I can't create a #temp table in a function, and that I can't exectute a dynamic SQL statment.
A quick example is:
****
create table tquery_sql (query_id BIGINT, sqlstatement VARCHAR(8000))
INSERT INTO tquery_sql values (1, 'select * from northwind.dbo.Customers')
INSERT INTO tquery_sql values (2, 'select categoryName, description from northwind.dbo.Categories where categoryid < 5')
INSERT INTO tquery_sql values (3, 'select * from northwind.dbo.employees where EmployeeID = 6')
*****
I need to pass in the query_id to the function (or stored procedure) and have the rowcount returned. Any ideas?
Thanks,
Randy
August 3, 2002 at 4:59 pm
If you are looking for the # of rows affected by a single SQL statement, there is @@ROWCOUNT. For instance:
DECLARE @Rows int
UPDATE Products
SET Price = (Price * 0.8)
SET @Rows = @@ROWCOUNT
The catch with @@ROWCOUNT is it will return the # of rows affected by the last SQL statement to execute. That's why I assigned it to a variable. The act of assigning it to a variable is considered a SQL statement and thus would return a different value for @@ROWCOUNT if you checked it again.
So if you have multiple SQL statements going off in a stored procedure and you want the total count of them all, you'll need to have a variable which is keeping track of the # of rows. After each SQL statement you'll need to add @@ROWCOUNT to it...
SET @Rows = @Rows + @@ROWCOUNT
HTH.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 08/03/2002 5:00:29 PM
K. Brian Kelley
@kbriankelley
August 3, 2002 at 6:41 pm
I've been using @@ROWCOUNT, but I have to run the select statement in order to retrieve the rowcount. Since I have to use dynamic SQL, I can only use a stored procedure. This is OK, since I can still return the rowcount.
Is there a more efficient way to retrieve the rowcount? My current procedure looks like:
****
-- Run the SQL Statement
EXEC (@SQLStatement)
-- Retrieve the rowcount
SET @RowCount = @@ROWCOUNT
RETURN @RowCount
****
Is there a way to repress the SELECT statment? I don't want to waste resources and have 1 million records returned. Would it be faster to create a table and insert the records, then do a @@ROWCOUNT. I've tried to use #temp, but it doesn't work in the dynamic SQL statement.
Thanks,
Randy
August 3, 2002 at 7:55 pm
You'd have to use ##temp to make it visible. It is possible to return an output parameter using sp_executesql, you could return your count there.
Andy
August 3, 2002 at 9:30 pm
I can't use SP_EXECUTELSQL since it is limited to 4000 characters; I have to use EXEC (@my_dynamic_sql_statement), because I'm using up to 8000 characters (there are some long lists).
I can now use the ##temp table (thanks for the tip), but I though that ## was for a global temp table. Will I run into problems if multiple users are running the same stored procedure that is creating ##temp ? Also, this temp table is still around after I run the stored procedure. I though that temp tables only stayed around for a transacation. Since these temp tables are not in the sysobjects table, what is a good way to drop them?
Thanks,
Randy
August 3, 2002 at 10:46 pm
A table preceded by ## is a global temporary table. The problem with using a local temporary table is it can only be seen within the stored procedure that seens it and any stored procedures called by that stored procedure.
We would think this would also apply if we were using sp_executesql or EXEC(), but the difference with those two commands is that the dynamic SQL code is executed in a new context. Hence, the local temporary table doesn't exist.
One main difference between local and global temporary tables is that a local temporary table will be dropped when the stored procedure ends. However, a global temporary table will persist as least as long as the database session lasts. It can last longer if there are other tasks which also reference the global temporary table.
Another idea is to create a permanent "temporary" table. The table is permanent, but it has a column for SPID. You add a SPID entry to any INSERT and you can retrieve and cleanup based on the SPID as well.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 4, 2002 at 6:24 am
Agree that the permanent temp table is a good idea.
Andy
August 4, 2002 at 11:59 pm
try this
CREATE PROCEDURE Usp_ReturnRowCount(@SqlStr varchar(8000),
@RwCnt int OUTPUT)
AS
SET NOCOUNT ON
DECLARE@StrPosint
CREATE TABLE #Rslts
(
RwCntint
)
SET @StrPos = CHARINDEX ('FROM',@SqlStr)
SET @SqlStr = SUBSTRING(@SqlStr,@StrPos,LEN(@SqlStr))
SET @SqlStr = 'SELECT COUNT(*) ' + @SqlStr
INSERT INTO #Rslts
EXEC (@SqlStr)
SELECT @RwCnt = RwCnt
FROM #Rslts
SELECT 'ROWCOUNT' = @RwCnt
GO
this proc substitutes the select list with count(*)
so that the query returns just one value
and then executes the query and inserts the value into a temp table
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply