May 15, 2012 at 2:31 am
I want to do a select in a proc and if @@ROWCOUNT = 0, I would like to kill that rowless resultset so that it is not returned to the caller of that proc.
I know I can select into a table variable and then select, or not, from that table variable based on rowcount. I am just curious whether there is an organic "drop last resultset" option available in TSQL. I suspect not, but was just wondering.
Particular imagined case:
create proc myproc
begin
select something
select somethingelse
if(@@ROWCOUNT=0)
begin
[Magically kill somethingelse resultset]
select somethingother
end
end
May 15, 2012 at 2:46 am
reidres (5/15/2012)
I want to do a select in a proc and if @@ROWCOUNT = 0, I would like to kill that rowless resultset so that it is not returned to the caller of that proc.I know I can select into a table variable and then select, or not, from that table variable based on rowcount. I am just curious whether there is an organic "drop last resultset" option available in TSQL. I suspect not, but was just wondering.
Particular imagined case:
create proc myproc
begin
select something
select somethingelse
if(@@ROWCOUNT=0)
begin
[Magically kill somethingelse resultset]
select somethingother
end
end
Something like this?
create proc myproc
as
begin
select something
declare @cnt int
select @cnt = COUNT(*) from somethingelse
if @cnt = 0
begin
select somethingother
end
else
begin
select somethingelse
end
end
May 15, 2012 at 3:03 am
Thank you, but no.
While the output of your proc is correct, I am literally interested in either a process that matches my topic title or a definitive statement that it can't be done.
May 15, 2012 at 3:15 am
reidres (5/15/2012)
Thank you, but no.While the output of your proc is correct, I am literally interested in either a process that matches my topic title or a definitive statement that it can't be done.
I'm not aware of any certified Organic process within SQL Server. I guess too much of pesticides used...
If you do select something as resultset within stored proc, your caller can still get it regardless if it has rows or not. The only way I can think of is "preselect" your resultsets into temp-tables (using select into) and then return only ones which have records. Something like:
...
SELECT Col1, Col2, Col3
INTO #something
FROM Table1
IF @@ROWCOUNT > 0 SELECT * FROM #something
SELECT Col1, Col2, Col3
INTO #somethingelse
FROM Table2
IF @@ROWCOUNT > 0 SELECT * FROM #somethingelse
...
And definitive statement for "drop last resultset":
No, it cannot be done.
You cannot kill resultsets in SQL Server as it is semi-pacifistic system (you can kill processes...). :hehe:
May 15, 2012 at 3:15 am
I don't think you can kill the resultset, but you could do something like this...
create proc myproc
begin
select something
if exists(select somethingelse)
select somethingelse
else
select somethingother
end
May 15, 2012 at 3:17 am
I would say that the only other way would be to select the different parts into a number of temporary tables and then use logic around that to bring back either temp1 temp2 or temp1 temp3
There is no way to destroy a result set within a procedure
create proc myproc
as
begin
select SomeColumn1 into #temp1 from SomeTable1
DECLARE @rc INT
select SomeColumn2 into #temp2 from SomeTable2
set @rc = @@ROWCOUNT
IF @rc = 0
begin
select SomeColumn3 into #temp3 from SomeTable3
end
IF @rc = 0
BEGIN
select * from #temp1
select * from #temp3
END
ELSE
BEGIN
select * from #temp1
select * from #temp2
END
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
end
May 15, 2012 at 12:14 pm
OK, thanks to everyone for responding. I thought there would be no organic way to remove it from the output once it had been selected, and you guys would know one way or the other.
That being said, of output-based solutions, the exists based answers beat @@rowcount based answers according to my understanding of best practices. And I am unclear as to why the three temp tables response would be offered, first because there are three and not one, and second because I thought that in a case like this (if we were to only look at intended output) a table variable would be the best practice.
Nonetheless, the point of my question was to see if there was an answer that met my criteria exactly, and that answer seems to be No. Which is sufficient.
Thank you.
May 16, 2012 at 1:17 am
reidres (5/15/2012)
And I am unclear as to why the three temp tables response would be offered, first because there are three and not one, and second because I thought that in a case like this (if we were to only look at intended output) a table variable would be the best practice.
Based on your first post you detail that you do three selects, this produces three result sets not one, which is why there are three temp tables. With table variables it implies that you know and define the table variable at creation time so you would have a declare @tab table (col1 int, col2 int......) in the proc and then you would do insert into @tab select col1, col2 ......... from sometable. With temp tables you dont need to know the definition of the columns inside the selecting tables as it creates it on the fly based on the schema of the table it has selected from.
Personally I am more leaning on the side of temp table over table variable due to persistance, statistics and other factors, the only time I would use a table variable is in a function call where temp tables are not allowed.
May 16, 2012 at 7:57 am
You wanted a simple, definitive answer. Here it is: you cannot do what you want to do.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 16, 2012 at 8:01 am
using "if exists()" before the actual query is the only way I know of to do what you want.
The probability of survival is inversely proportional to the angle of arrival.
May 16, 2012 at 8:10 am
Here is a simple example using exists to accomplish the type of behavior you are describing.
alter proc MyProc
(
@SomethingElse int
)
as begin
select top 5 * from sys.sysobjects as Something
if exists(select top 3 * from sys.sysobjects as SomethingElse where @SomethingElse = 1)
select top 3 * from sys.sysobjects as SomethingElse where @SomethingElse = 1
if @@ROWCOUNT = 0
select top 4 * from sys.sysobjects as SomethingOther
end
go
exec MyProc 1 --the second result set will have 3 rows
exec MyProc 2 --the second result set will have 4 rows
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 8:51 am
Sean Lange (5/16/2012)
Here is a simple example using exists to accomplish the type of behavior you are describing.
alter proc MyProc
(
@SomethingElse int
)
as begin
select top 5 * from sys.sysobjects as Something
if exists(select top 3 * from sys.sysobjects as SomethingElse where @SomethingElse = 1)
select top 3 * from sys.sysobjects as SomethingElse where @SomethingElse = 1
if @@ROWCOUNT = 0
select top 4 * from sys.sysobjects as SomethingOther
end
go
exec MyProc 1 --the second result set will have 3 rows
exec MyProc 2 --the second result set will have 4 rows
The EXISTS clause ignores the SELECT clause--including the TOP(n)--in the subquery. If you need to ensure that there are at least n records in the results, you'll need to use HAVING COUNT(*) >= n (or something similar) in the EXISTS subquery.
I would also replace the "IF @@ROWCOUNT = 0" with an "ELSE". ELSE makes it clear that the condition for the second part is completely dependent on the results of the EXISTS (since the EXISTS and the following query are essentially the same), whereas the second IF obscures that fact.
Now if the queries are changed so that it's possible that the EXISTS condition might be true, but the resulting query might return zero records, then you might want to keep the two separate IF statements.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2012 at 8:56 am
It was a 2 minute brief example of using exists to demonstrate the task at hand. I certainly was not expecting a code review. 😀
Your points are all certainly valid.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2012 at 9:56 am
Sean Lange (5/16/2012)
It was a 2 minute brief example of using exists to demonstrate the task at hand. I certainly was not expecting a code review. 😀Your points are all certainly valid.
Sure, but not everyone who visits this site will have the experience to see that, so it doesn't hurt to explicitly point out potential pitfalls that newer developers may encounter.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2012 at 11:41 am
I'm still trying to figure out what it means to "Organically Cancel" a resultset. Does that mean that it comes with a sprig of broccoli or something? 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply