How to modify @@ROWCOUNT in a store procedure?
bogdan
I have a stored procedure that executes multiple statements (including calls
to other procedures). I'd like to have @@ROWCOUNT to be set to a certain
number just before the procedure exists. Basically I'd like to preserve the
@@ROWCOUNT from one of the statements only. Is there a way to do that in
T-SQL?
Thanks,
Bogdan
robie.b...@gmail.com
Hi, you can pass a parameter to your stored procedure for the number
of rows you want to return, per instance @MaxRow.
Then in your stored procedure, you can use the following statement.
-- Set maximum rows returned. If set to 0, all rows are returned.
SET ROWCOUNT @MaxRow;
-- Do whatever you want to do
-- Turn off the row limiter.
SET ROWCOUNT 0;
Does it help ?
bogdan
<robie.b...@gmail.com> wrote in message
news:f8661ccc-eddd-4c0b...@r66g2000hsg.googlegroups.com...
Thanks for the reply. Unfortunately this does not help. I do not need to
limit number of rows returned from a query. What I'd like to do is to save
@@ROWCOUNT and then restore it at the end of the proc.
But, as Dan explained above, this is not possible. The problem is that I
can modify the proc but I cannot modify applications that call it. I'm
afraid that some of the apps might rely on @@ROWCOUNT. One example would be
asp.net where AffectedRows reflect @@ROWCOUNT, I think.
Thanks,
Bogdan
ML
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
bogdan
and asp.net) that access the database. The apps call a stored procedure
that inserts a single row into a table. There is a new requirement to
insert rows to other tables after a successful insert into the original
table. This is supposed to be based on certain conditions - mainly values
being inserted into the original table. Since all apps use the same stored
proc I thought the proc would be a good place to do it.
The problem is that supposedly asp.net apps (TableAdapters in particular)
rely on @@ROWCOUNT (returned by TableAdapter's insert method). So, if I
execute any statements like 'IF @@ROWCOUNT == 1' after the original INSERT
I'll reset the @@ROWCOUNT and that could impact asp.net apps.
The ideal solution would be:
INSERT INTO table
-- Save @@ROWCOUNT
DECLARE @RowCountSav int;
SET @RowCountSav = @@ROWCOUNT; -- this resets @@ROWCOUNT
-- If successful, execute the new code
IF @RowCountSav = 1
BEGIN
[...]
END
-- Restore the initial @@ROWCOUNT
SET @ROWCOUNT = @RowCountSav; -- this cannot be done, of course
Bogdan
"ML" <M...@discussions.microsoft.com> wrote in message
news:466953DB-46B4-4709...@microsoft.com...
Plamen Ratchev
will set the @@ROWCOUNT to the saved value. For example, if you have
auxiliary table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx), you can do as last
statement:
SELECT n INTO #Junk
FROM Numbers
WHERE n <= @RowCountSav;
That in effect will set the final @@ROWCOUNT to your saved value.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
bogdan
"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:E6660ACC-924B-420F...@microsoft.com...
Print Mail
Here's an example using the OP's example scenario:
--first query (need rowcount preserving)
INSERT @NewVal,NULL INTO tblList
--second query that should not effect the rowcount
SET NOCOUNT ON;
UPDATE tblList SET Text=@NewText WHERE Val=@NewVal
SET NOCOUNT OFF;
INSERT INTO table
-- If successful, execute the new code
BEGIN
SET NOCOUNT ON;
[...] --code in this section will not affect the rowcount
SET NOCOUNT OFF;
END
--the original rowcount is maintained
More info on SET NOCOUNT: http://msdn.microsoft.com/en-us/library/ms189837.aspx
> On Friday, May 09, 2008 8:21 AM bogdan wrote:
> Hi,
>
> I have a stored procedure that executes multiple statements (including calls
> to other procedures). I'd like to have @@ROWCOUNT to be set to a certain
> number just before the procedure exists. Basically I'd like to preserve the
> @@ROWCOUNT from one of the statements only. Is there a way to do that in
> T-SQL?
>
> Thanks,
> Bogdan
>> Although you can't change @@ROWCOUNT, you can set your own variable to the
>> desired value and return via an OUTPUT parameter.
>>
>> CREATE PROC dbo.Proc1
>> @row_count int OUTPUT
>> AS
>> SELECT...
>> SET @row_count = @@ROWCOUNT
>>
>> EXEC dbo.Proc2
>> RETURN 0
>> GO
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>>> What are you actually trying to achieve (in business terms)?
>>>>
>>>>
>>>> ML
>>>>
>>>> ---
>>>> Matija Lah, SQL Server MVP
>>>> http://milambda.blogspot.com/
>>>>> On May 9, 8:21=A0am, "bogdan" <bog...@nospam.com> wrote:
>>>>> ls
>>>>> n
>>>>> the
>>>>> n
>>>>>
>>>>> Hi, you can pass a parameter to your stored procedure for the number
>>>>> of rows you want to return, per instance @MaxRow.
>>>>> Then in your stored procedure, you can use the following statement.
>>>>>
>>>>> -- Set maximum rows returned. If set to 0, all rows are returned.
>>>>> SET ROWCOUNT @MaxRow;
>>>>>
>>>>> -- Do whatever you want to do
>>>>>
>>>>> -- Turn off the row limiter.
>>>>> SET ROWCOUNT 0;
>>>>>
>>>>>
>>>>> Does it help ?
>>>>>>> You can easily accomplish this if you execute last one dummy statement that
>>>>>>> will set the @@ROWCOUNT to the saved value. For example, if you have
>>>>>>> auxiliary table with numbers
>>>>>>> (http://www.projectdmx.com/tsql/tblnumbers.aspx), you can do as last
>>>>>>> statement:
>>>>>>>
>>>>>>> SELECT n INTO #Junk
>>>>>>> FROM Numbers
>>>>>>> WHERE n <= @RowCountSav;
>>>>>>>
>>>>>>> That in effect will set the final @@ROWCOUNT to your saved value.
>>>>>>>
>>>>>>> HTH,
>>>>>>>
>>>>>>> Plamen Ratchev
>>>>>>> http://www.SQLStudio.com
>>>>>>>> That'll do. Thanks.
Print Mail
Just in case anyone lands on this question from Google, I have another solution using SET NOCOUNT, that I've employed in insert triggers that perform additional updates.
Here's an example using the OP's example scenario:
Erland Sommarskog
information is sent to the client after each statement or not. It
has nothing to do with how @@rowcount is set.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx