May 16, 2012 at 12:12 pm
RBarryYoung (5/16/2012)
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? 😀
Only if the server is running on batteries recharged with a solar panel.
The probability of survival is inversely proportional to the angle of arrival.
May 16, 2012 at 12:45 pm
RBarryYoung (5/16/2012)
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? 😀
methink DBCC TIMEWARP would have come in handy. run the select, check for records and timewarp to right before you ran the select...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 16, 2012 at 2:25 pm
Matt Miller (#4) (5/16/2012)
RBarryYoung (5/16/2012)
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? 😀methink DBCC TIMEWARP would have come in handy. run the select, check for records and timewarp to right before you ran the select...:)
Heh. 🙂
[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]
May 17, 2012 at 12:26 am
Knowing that it probably wouldn't work, I was playing around with this anyway and of course it didn't work.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Test AS
BEGIN
DECLARE @SQL NVARCHAR(100)
PRINT 'Entering SP'
SET @SQL = 'KILL @@SPID'
EXEC (@SQL)
PRINT 'Exiting SP'
END
GO
EXEC dbo.Test
This is the erxception it throws:
Entering SP
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@@SPID'.
Exiting SP
Anyone care to suggest why it doesn't? I know that the @@SPID is probably different for the dynamic SQL session than for the SP session (I tried using KILL that way too and it also did not work).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 4:53 am
dwain.c (5/17/2012)
Knowing that it probably wouldn't work, I was playing around with this anyway and of course it didn't work.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Test AS
BEGIN
DECLARE @SQL NVARCHAR(100)
PRINT 'Entering SP'
SET @SQL = 'KILL @@SPID'
EXEC (@SQL)
PRINT 'Exiting SP'
END
GO
EXEC dbo.Test
This is the erxception it throws:
Entering SP
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@@SPID'.
Exiting SP
Anyone care to suggest why it doesn't? I know that the @@SPID is probably different for the dynamic SQL session than for the SP session (I tried using KILL that way too and it also did not work).
Try this, Dwain:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Test AS
BEGIN
DECLARE @SQL NVARCHAR(100), @SPID smallint
SELECT 'Entering SP' AS 'Where are we?', @@SPID AS '@@SPID'
SELECT @SPID = @@SPID
SET @SQL = 'SELECT @@SPID AS ''@@SPID in EXEC call''; KILL ' + CAST ( @SPID AS varchar )
EXEC (@SQL)
SELECT 'Exiting SP' AS 'Where are we?'
END
GO
EXEC dbo.Test
May 17, 2012 at 6:12 am
Here's how I would probably do it:
CREATE PROC pr_OrganicDissapearance AS
BEGIN TRY
;WITH yourQuery As
(
SELECT *
FROM yourTable
WHERE (yourConditions)
-- etc ...
)
SELECT *
FROM yourQuery
WHERE -1 < ( 1 / (SELECT COUNT(*) FROM yourQuery) )
END TRY
BEGIN CATCH
RETURN -- suppress the error, and the resultset
END CATCH
Does that do it? 😀
[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]
May 17, 2012 at 6:27 am
Very slick try, but it doesn't seem to work:
USE tempdb
GO
CREATE TABLE tmp (a int)
INSERT tmp VALUES (1)
go
CREATE PROC pr_OrganicDissapearance AS
SELECT a FROM tmp WHERE a = 1
BEGIN TRY
;WITH yourQuery As
(
SELECT a
FROM tmp
WHERE a = 2
)
SELECT *
FROM yourQuery
WHERE -1 < ( 1 / (SELECT COUNT(*) FROM yourQuery) )
END TRY
BEGIN CATCH
--cannot return here because OP wants additional result set out
--PRINT 'no records so no resultset?'
END CATCH
SELECT a FROM tmp WHERE a = 1
GO
EXEC pr_OrganicDissapearance
GO
drop PROC pr_OrganicDissapearance
GO
DROP TABLE tmp
GO
a
-----------
1
(1 row(s) affected)
a
-----------
(0 row(s) affected)
a
-----------
1
(1 row(s) affected)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 17, 2012 at 7:04 am
Jan Van der Eecken (5/17/2012)
dwain.c (5/17/2012)
Knowing that it probably wouldn't work, I was playing around with this anyway and of course it didn't work.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Test AS
BEGIN
DECLARE @SQL NVARCHAR(100)
PRINT 'Entering SP'
SET @SQL = 'KILL @@SPID'
EXEC (@SQL)
PRINT 'Exiting SP'
END
GO
EXEC dbo.Test
This is the erxception it throws:
Entering SP
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@@SPID'.
Exiting SP
Anyone care to suggest why it doesn't? I know that the @@SPID is probably different for the dynamic SQL session than for the SP session (I tried using KILL that way too and it also did not work).
Try this, Dwain:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Test AS
BEGIN
DECLARE @SQL NVARCHAR(100), @SPID smallint
SELECT 'Entering SP' AS 'Where are we?', @@SPID AS '@@SPID'
SELECT @SPID = @@SPID
SET @SQL = 'SELECT @@SPID AS ''@@SPID in EXEC call''; KILL ' + CAST ( @SPID AS varchar )
EXEC (@SQL)
SELECT 'Exiting SP' AS 'Where are we?'
END
GO
EXEC dbo.Test
Now that's an interesting approach! Who'da thunk it!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 7:13 am
"SET @SQL = 'KILL @@SPID'
EXEC (@SQL)"
Killing ones self is a depressing solution.
The probability of survival is inversely proportional to the angle of arrival.
May 17, 2012 at 8:11 am
Thanks for the test, Kevin.
Hmm, I wonder why that didn't work? ...
[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]
May 17, 2012 at 8:19 am
sturner (5/17/2012)
"SET @SQL = 'KILL @@SPID'EXEC (@SQL)"
Killing ones self is a depressing solution.
Ah yes, but suicide by SP is less depressing than say jumping off a building.
Perhaps there's another solution akin to:
SET SP_CLOAKING ON
That way if you can't see it maybe it isn't really there.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 17, 2012 at 8:33 am
RBarryYoung (5/17/2012)
Thanks for the test, Kevin.Hmm, I wonder why that didn't work? ...
Well I looked at the estimated and actual query plans for several different versions of my attempt, and I have come to the conclusion that SQL Server is creating the result-set and sending it (the headers) to the client before it even knows if it will find any rows. Indeed, before it has evaluated any part of the query except what the column-set will be.
So that makes it seem pretty clear that it cannot be done without a preliminary query to test it.
[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]
May 17, 2012 at 11:20 am
RBarryYoung (5/17/2012)
RBarryYoung (5/17/2012)
Thanks for the test, Kevin.Hmm, I wonder why that didn't work? ...
Well I looked at the estimated and actual query plans for several different versions of my attempt, and I have come to the conclusion that SQL Server is creating the result-set and sending it (the headers) to the client before it even knows if it will find any rows. Indeed, before it has evaluated any part of the query except what the column-set will be.
So that makes it seem pretty clear that it cannot be done without a preliminary query to test it.
I think there is kinda a "contract" if you will that a sproc will provide outputs when a SELECT is executed, even if it errors out.
BTW, I also tried this for the error handling and it responded similarly:
IF @@ERROR <> 0
BEGIN
PRINT 'does this work? ... nope'
END
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply