March 3, 2014 at 12:35 pm
Is there an option to NOT return a query set in a stored procedure (conditionally)?
So I have my sproc which just does:
DECLARE @Rows AS INT
SET @Rows = 0
SELECT 1 AS MyColumn
SET @Rows = @@ROWCOUNT
What if I only want to return "MyColumn" sometimes based off an input parm (but still return @Rows as my output) but other times I want to return both.
Is there something I can do to basically throw away the result set from that sproc if the only purpose is to run the sproc and gather a row count (but not bother returning any actual data)?
March 3, 2014 at 1:36 pm
Dirrect the query to a temp table saving the count then output based on criteria and if you need the table do a query of the temp table.
March 3, 2014 at 1:40 pm
I was afraid of that... I was hoping for some nifty WITH option, something like the RESULT SET option in SQL 2012 but with an option to just not return the result set.
March 3, 2014 at 1:55 pm
Why not just make the last step in your procedure to return what you want based on your input parameter? Example:
CREATE PROCEDURE SomeProc(@intOutput Integer)
AS
BEGIN
--do some stuff here
IF @intOutput = 1
SELECT @Rows;
ELSE IF @intOutput = 2
SELECT MyColumn
FROM MyTable;
ELSE
SELECT @OtherRowCount;
END;
Granted, your calling procedure will have to know what to expect back, but since it passed the @intOutput parameter when calling SomeProc, it already does. I'm not saying this is an ideal situation, but it is a possibility. I think having separate procedures would probably be best.
HTH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply