April 22, 2008 at 10:40 am
Hi,
I have a stored procedure where accepts an input. But the Application could either pass me a value or the string 'ALL'. If it is 'ALL' then i need to return the entire result set, else use the String in the WHERE clause.
I created a procedure as follows
CREATE PROC A (@Input VARCHAR(10))
AS
BEGIN
IF @Input <> 'ALL '
BEGIN
SELECT A1,A2 FROM TABLEA WHERE A3 ='@Input'
END
ELSE
BEGIN
SELECT A1,A2 FROM TABLEA
END
END
I dont want to use dynamic SQL. So is there a better way to write this query. Please help
Vinoj
April 22, 2008 at 12:14 pm
There are "simpler looking" ways of doing this query; however, using the IF/ELSE as you have done will allow good use of an index when you actually pass an argument other than 'ALL'. The version you have given should run well.
April 22, 2008 at 1:14 pm
You may want to use the RECOMPILE keyword. If the first time you run the procedure it is with the 'All' value, the execution plan could get stuck with a table scan for all subsequent executions.
April 22, 2008 at 1:52 pm
I came up with an idea of creating a CTE and then doing a final select from the CTE based on the Input parameter. My Select query too is not as simple as mentioned here. But what i fear is that when i do the final select from the CTE will it be a table scan or not
Vinoj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply