July 12, 2010 at 4:24 am
Hi All,
I have a Procedure which should return only 1 Record on selecting record according to Priority.
BEGIN
DECLARE @Accounts nvarchar(MAX)
SELECT @Accounts = ISNULL([map_user_modules].[Accounts], '') FROM [map_user_modules]
WHERE [map_user_modules].[id_user] = 231 AND [map_user_modules].[id_module] = 5
IF ((@Accounts = '') OR (@Accounts = 'All'))
SELECT @Accounts = null
BEGIN
Select @@ROWCOUNT as Priority2
END
IF @@ROWCOUNT = 1
BEGIN
Return
END
BEGIN
Select @@ROWCOUNT as Priority3
END
IF @@ROWCOUNT = 1
BEGIN
Return
END
BEGIN
Select @@ROWCOUNT as Priority4
END
IF @@ROWCOUNT = 1
BEGIN
Return
END
In the above statement Suppose we take a scenario where Select @@ROWCOUNT as Priority3 is executed, I get two tables returned, One is the Table returned by Select @@ROWCOUNT as Priority3 and the other is the Table returned by Select @@ROWCOUNT as Priority2 which has no Records. How can i eliminate the Empty table that returns no records.
July 12, 2010 at 7:04 am
You're way off in your use of @@RowCount. Every time something is referenced or updated, you're going to get a change in @@RowCount. If you want to use it, it needs to be run immediately after whatever statement was run that you care about. Take this for example, I don't affect a single row of data, but @@RowCount equals one, no matter what:
DECLARE @id INT
SET @id = 42
SELECT @@rowcount
A better approach would be something like:
DECLARE @count int
SELECT *
FROM Person.Address
WHERE StateProvinceID = 79
SET @count = @@ROWCOUNT
SELECT @count
SELECT @@ROWCOUNT
If you run this against AdventureWorks you'll see that the @count value is 2636, but the second @@ROWCOUNT value is, again 1, because of the previous statement.
Every time you run SELECT, you're generating another result set. If you want fewer result sets, you need to use SET to set variables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply