I am trying to create a stored procedure containing a dynamic query.
I am still new to using conditionals in sql, so any help to
get this query running would be appreciated!!!
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
IF @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
AND start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
ENDHi
Just check if this solves the purpose
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
CASE WHEN @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
END
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"AJ" wrote:
> I am trying to create a stored procedure containing a dynamic query.
> I am still new to using conditionals in sql, so any help to
> get this query running would be appreciated!!!
> CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
> AS
> BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END|||Well you could approach it very simplistically and just replace all your
different IF cases with OR operations (because that's what they really
are) like this:
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE (@.name IS NOT NULL and first_name + ' ' + last_name LIKE @.name)
OR (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR (start_dt BETWEEN @.from_dt AND @.to_dt)
You could shuffle the WHERE clause around a bit but chances are the
query optimiser will come up with the same plan for the majority of the
variations so you may as well stick to something that you understand and
that's readable (so those who maintain the system after you aren't
bamboozled by your code).
I changed your "<= AND >=" bits to "BETWEEN" because it's a little more
readable IMHO. Also I noticed you left off the '@.' symbol on a couple
references to your proc parameters in the WHERE clause (to_dt & from_dt).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:
>I am trying to create a stored procedure containing a dynamic query.
>I am still new to using conditionals in sql, so any help to
>get this query running would be appreciated!!!
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
>END
>|||Hmmm... The CASE statement is wrong. I think you mean
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
THEN start_dt >= @.from_dt AND start_dt <= @.to_dt
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
But I'm not sure that would work even. BOL says the bit after THEN can be a
ny valid SQL expression but I don't know if "x LIKE y" or "a >= x and a <= b
" are valid in this context (even though they just resolve to a boolean, whi
ch I guess is a valid SQL e
xpression) - never tried that before. At the very least it's a little unort
hodox. Typically a CASE is used to return a specific value to compare somet
hing to like this
WHERE MyCol = (CASE
WHEN a THEN SomeVal
WHEN b THEN SomeOtherVal
ELSE SomeCatchAllVal
END)
Also, the middle case is redundant because it's the same result as the ELSE
case. You could simply write it as
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
However, you could get rid of the CASE statement entirely by saying
WHERE first_name + ' ' + last_name LIKE @.name
OR start_dt BETWEEN @.from_dt AND @.to_dt
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chandra wrote:
>Hi
>Just check if this solves the purpose
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> CASE WHEN @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
> start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END
>END
>
>|||Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where
clause but if no value is passed into the proc for each specific
parameter then a default value will be used, for each parameter, such
that it won't limit the resultset at all (ie. any @.name string, the min
@.from_dt value and the max @.to_dt value for the datatypes you chose).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:
>Hi all, so far i have adopted the following approach.
>If no parameters are supplied i want all records to be selected.
>At the moment this isn't catered for in the query below.
>My overall logic is:
>If @.name is provided filter results with @.name
>If @.from_dt is provided filter results with @.from_dt
>If @.name and @.from_dt are provided filter with both.
>@.if no parameters are provided just select all records.
>Can anyone give some modifications to this query to achieve this?
>Thanx...
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> (@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
> OR
> (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
>@.from_dt AND @.to_dt)
> OR
> (@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
> ORDER BY
> last_name
>|||One small caveat to Mike's excellent suggestion - the technique he proposes
requires that none of the columns (first_name, last_name, start_dt, and
end_dt columns) be nullable. If any of the rows contains nulls in one or
more of those columns, then you will not match that row. Since you did not
post any DDL, we do not know the details of the table being searched, so
this may or may not apply in your case. In the future, please post DDL and
sample data so you have the best chance of receiving the most complete
answer possible.
Erland Sommarskog has a great article about dynamic search criteria at:
http://www.sommarskog.se/dyn-search.html
ITHT
Jeremy Williams
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23xxsVZmaFHA.3400@.tk2msftngp13.phx.gbl...
Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where clause
but if no value is passed into the proc for each specific parameter then a
default value will be used, for each parameter, such that it won't limit the
resultset at all (ie. any @.name string, the min @.from_dt value and the max
@.to_dt value for the datatypes you chose).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
AJ wrote:
Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||No, SQL has a CASE expression **not** a CASE statement. Big
difference! Expressionds return a value: they do not control flow of
control.|||Exactly what I was getting at (did you read my whole post?).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
--CELKO-- wrote:
>No, SQL has a CASE expression **not** a CASE statement. Big
>difference! Expressionds return a value: they do not control flow of
>control.
>
>
