Parameter driven where & order by
-
Does anybody have any good examples/suggestions for using parameter based where and order by clauses? I would like to be able to use an if/else or case statement without recreating the field list (it is very large). A brief example of a where clause is listed below.
Create procedure myproc(@var char(1))
Select field1, field2, … field30
From table1
Where
**if @var = ‘A’ Begin
** field1 = ‘xxx’ and field2 = ‘yyy’ End
**Else if @var = ‘B’ Begin
** field1 = ‘yyy’ and field2 = ‘xxx’ End
**Else
** field1 = ‘xyz’
Return
go
I am currently putting the entire select/where/orderby between an if/else or case statement, but I can’t help but think there is a better or easier way to do this without re-creating the field list.
Thanks,
KW
-
I would say what you say you have at the moment (which I assume is along the lines of
if var=A then
select fields from table where condition1
elseif var=B then
select fields from table where condition2
else
select fields from table where condition3
)
is preferable to what you are thinking about changing to. It would be my suspicion that the more verbose method is easier for SQL Server to optimize / cache execution plans for than the maybe more 'elegant' method with less text but a tricksy case statement right in the middle of it.
You can surely just copy + paste the field list; avoiding this work is not a good enough reason to put more work on the server engine
-
I have looked at this so many times and you hurt potential performnace doing this sort of thing. However that said you can do like so.
SELECT
field1, field2, … field30
FROM
table1
WHERE
(CASE @var
WHEN 'A' THEN (CASE WHEN field1 = ‘xxx’ and field2 = ‘yyy’ THEN 1 ELSE 0 END)
WHEN 'B' THEN (CASE WHEN field1 = ‘yyy’ and field2 = ‘xxx’ THEN 1 ELSE 0 END)
ELSE (CASE WHEN field1 = ‘xyz’ THEN 1 ELSE 0 END)
END) = 1
But when doing so you need to use the WITH RECOMPILE option on the SP declaration itself.
The best way to do thou to get the most performance is like so.
CREATE PROC myproc;1
@var char(1)
AS
SET NOCOUNT ON
IF @var = 'A'
EXEC myproc;2
IF @var = 'B'
EXEC myproc;3
ELSE
EXEC myproc;4
GO
CREATE PROC myproc;2
AS
SET NOCOUNT ON
SELECT
field1, field2, … field30
FROM
table1
WHERE
field1 = ‘xxx’ and field2 = ‘yyy’
GO
CREATE PROC myproc;3
AS
SET NOCOUNT ON
SELECT
field1, field2, … field30
FROM
table1
WHERE
field1 = ‘yyy’ and field2 = ‘xxx’
GO
CREATE PROC myproc;4
AS
SET NOCOUNT ON
SELECT
field1, field2, … field30
FROM
table1
WHERE
field1 = ‘xyz’
GO
It does mean more work on your part but has a better overall performance.
Also as for Order By, if this is in an application I suggest using the Recordset Sort property instead. It is far more flexible than doing inside the SP.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply