March 24, 2006 at 9:52 am
Basically I have a problem Where I need suppress the result set that is generated by this stored procedure. So when I execute this proceudre with a piece of dynamic SQL it just returns me the rowcount and nothing else, IE no resultset.
Is there anyway of just returning the rowcount from a piece of dynamic SQL?
This would help greatly!!
----------------------------------------------------
CREATE PROCEDURE TEST_ROW_COUNT
@SQLeval nvarchar(2000),
@count int output
AS
BEGIN
EXEC(@SQLeval)
SET @count = @@RowCount
END
DECLARE @output as int
EXEC TEST_ROW_COUNT 'select * from d_clientDetails', @output OUTPUT
print @output
-------------------------------------------------------
March 24, 2006 at 10:01 am
You coluld just do a count(*) and return that or eat the output of the select into a #temptable (or table variable) then drop it. Not sure why yuo would want to reun the sSELECT without returning anything.
March 24, 2006 at 10:06 am
The problem is, Iam doing a large amount of dynamic SQL, I cant use a temp table to eat the output as I dont know the exact column definition of the returned results, most of the time the dynamic SQL will be a stored proc call like:
EXEC ('EXEC INTERNAL_SQL_RANK 2,7,8')
And I would then use a @@rowcount to determine whether or not I need to perform a certain action.
but I really need to suppress all the results sets that are returned by executing the dynamic SQL.
Thanks Michael
March 24, 2006 at 10:21 am
use sp_executesql with output parameter
CREATE PROCEDURE TEST_ROW_COUNT
@SQLeval nvarchar(2000),
@count int output
AS
BEGIN
declare @RowsReturned int
SP_EXECUTESQL @SQLeval,'N@RowsReturned int OUTPUT', @RowsReturned OUTPUT
SET @count = @RowsReturned
END
DECLARE @output as int
EXEC TEST_ROW_COUNT 'select @RowsReturned = count(*) from d_clientDetails', @output OUTPUT
print @output
March 24, 2006 at 10:45 am
Ive looked into this before and never got it quite working...
the syntax you posted didnt quite work (iam SQL2000 if it helps)
I had to change it to this:
CREATE PROCEDURE TEST_ROW_COUNT
@SQLeval nvarchar(2000),
@count int output
AS
BEGIN
declare @RowsReturned int
EXEC SP_EXECUTESQL @SQLeval,'N@RowsReturned int OUTPUT', @RowsReturned OUTPUT
SET @count = @RowsReturned
END
DECLARE @output as int
EXEC TEST_ROW_COUNT 'select @RowsReturned = count(*) from d_clientDetails', @output OUTPUT
print @output
--------------
I highlighted the changes in red.
Also how would this work if my dynamic SQL was a stored procedure call? Surely there is a way in SQL of just suppressing the result set or just getting it to only return the @@rowcount without anything else?
March 27, 2006 at 5:08 am
FOR THE RECORD:
I was disapointed only two people on this forum tried to help solve my problem, thanks guys the input was appreciated. I have now found the solution (or hack) to this problem and thought I would post it to share with others....
For those who wish to only recieve a @@rowcount from a stored procedure, or any expressions for that matter, you can use the OPENQUERY command
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servername
SELECT @count = count(*) FROM OPENQUERY(LOCALSERVER ,'SET FMTONLY OFF SET NOCOUNT ON EXEC modular..getIssues 685,54')
essentially used for cross querying accross multiple DBs for distributed transactions it can be used in a call back loop to the local server!!
It means you can join stored procedures with with any other query also which is pretty good. Altho pay heed to the performance issues associated with this command - I wont go into details but its worth reading up
Thanks anyways guys!!!!!
Michael
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply