SQL Help with a simple Stored Procedure

[wxman]

This should be basic – but it driving me nuts on how to do it so any help would be much appreciated.

I have a column that contains [eventType] as follows

HAIL
TORNADO
FUNNEL
DOWNBURST

I want to pass to the Proc parameters to filter on [eventType] depending on weather check boxes on the web page are selected.

EXEC MyProc @_hail =1, @_tornado =1, @_ funnel=0, @ _downburt=0

MyProc
@_hail BIT,
@_tornado BIT,
@_ funnel BIT,
@ _downburt BIT

SELECT * from eventsTable
WHERE [eventType] = HAIL
OR [eventType] = TORNADO

I am not sure how to do the dynamic WHERE clause.

[Spacecadet]

Rob, yours looks wrong , ANDs and ORs are mixed up
needs changing to this:

SELECT
*
FROM
eventsTable
WHERE
(
@_hail = 1
and
[eventType] = 'HAIL'
)
or
(
@_tornado = 1
and
[eventType] = 'TORNADO'
)
etc... 

heres another way with less boolean:

SELECT * from eventsTable
WHERE 
[eventType] = case when @_hail =1 then 'HAIL' else 'NO MATCH' end
or [eventType] = case when @_tornado =1 then 'TORNADO' else 'NO MATCH' end
or [eventType] = case when @_funnel =1 then 'FUNNEL' else 'NO MATCH' end
or [eventType] = case when @_downburt =1 then 'DOWNBURT' else 'NO MATCH' end 

[DimPrawn]

Or use UNION ALL to join simple selects:

  SELECT * 
FROM eventsTable 
WHERE @_hail = 1 and [eventType] = 'HAIL' 
UNION ALL 
SELECT * 
FROM eventsTable 
WHERE @_tornado = 1 and [eventType] = 'TORNADO'
UNION ALL 
SELECT * 
FROM eventsTable 
WHERE @_funnel = 1 and [eventType] = 'FUNNEL' 
UNION ALL 
SELECT * 
FROM eventsTable 
WHERE @_downburst = 1 and [eventType] = 'DOWNBURST' 

HTH

[Spacecadet]

Tsk, 4 selects when 1 will do

[DimPrawn]

Tsk, 4 selects when 1 will do

I get paid by the hour!

[wxman]

A BIG public THANK YOU is for all who have replied is required here Power to UKcontractor indeed

I know that we are all contractors who charge by the hour - but this is a personal private project that I working on and SQL is not really my area of expertise. So I appreciate the time and effort that people have given to help me out.

Often thanks is not given when help is required – but I am not that sort of person – so again many thanks for helping me out.

PS if you ever find yourselves in the Village of Rothey, Leicestershire – the beers ore on me down at the “Woodies”

[Weltchy]

Tsk, 4 selects when 1 will do

Guessing at the execution plan that would come out for both SQL Statements, I'd hazard a guess that the first would probably suit a smaller table, record-wise, whilst the second would perform better with a large table, especially if that column had an index against it!!! The first statement would not make best use of an index on eventtype as its nested within a case statement.

Likewise, shame on both of you for using SELECT *. Thats lazy. Fieldnames please!!!!

[AtW]

Likewise, shame on both of you for using SELECT *. Thats lazy. Fieldnames please!!!!

It's a very bad practice too - in case of changes to table bugs may appear in code and re-compilation won't find them because you were doing select * rather than select specific columns.

[Spacecadet]

It's a ver..blah zzzzzzzzzzzzzzzz

get back to general

couldn't list the column names as they were not provided in the spec

[AtW]

It is a very bad practice to have such "spec" - if you don't know what columns you going to have, what's good for you to select *?

Back to exile now!