help with stored procedure
-
In a stored procedure report, how do I allow an optional filter for a select statement/
For example:
use pubs
go
CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname = @lastname and au_fname = @firstname
go
EXECUTE au_info @firstname = null, @lastname = 'Ringer'
GO
DROP PROCEDURE au_info
I want the stored procedure to return all records where last name is "ringer" (there should be two of them) but right now it returns nothing.
Thanks in advance,
Billy
-
ALTER PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname = @lastname OR au_fname = @firstname
GO
EXECUTE au_info @firstname = NULL, @lastname = 'Ringer'
GO
-
bp,
Your query is only going to find those records like this:
au_fname au_lname
NULL ringer
That's the word NULL.
srgangu's script will find any records where
au_fname is NULL or au_lname is ringer.
I believe you want all records where the au_lname is ringer and the au_fname is null (in other words blank).
If so change
au_fname = @firstname
to read
aufname IS NULL
-SQLBill
-
To add to my last post...
both of you are searching for the WORD NULL. @lastname is a VARCHAR. NULL (meaning empty) is not VARCHAR.
Using = means to match (equality). You can't make a match to NULL (empty value). SQL Server has the IS NULL statement to search for the NULL (empty value).
-SQLBill
-
Actually, I only want to select records where last name is "Ringer". It does not matter what the first name is. I can't leave out first name parameter because otherwise the sproc will complain it is missing. Not sure how I can accomplish that (that is, I want the filters to be optional; I want the sproc to be able to filter without first and last name, either or both.)
Thanks in advance,
Billy
-
Try this,
use pubs
go
CREATE PROCEDURE au_info
@lastname varchar(40) = NULL
, @firstname varchar(20) = NULL
AS
SELECT au_lname, au_fname
FROM authors
WHERE (au_lname = @lastname or au_fname = @firstname)
go
EXECUTE au_info @lastname = 'Ringer'
GO
DROP PROCEDURE au_infoThe two changes of note are,
1) use = NULL on the parameter definition in the CREATE PROCEDURE statement
2) use OR instead of AND in the WHERE clause
Also, the following uses wildcard matching and will return 3 records, matching with 'Stringer' and 'Ringer'
use pubs
go
CREATE PROCEDURE au_info
@lastname varchar(40) = NULL
, @firstname varchar(20) = NULL
AS
SET @lastname = '%' + @lastname + '%'
SET @firstname = '%' + @firstname + '%'
SELECT au_lname, au_fname
FROM authors
WHERE (au_lname LIKE @lastname or au_fname LIKE @firstname)
go
EXECUTE au_info @lastname = 'Ringer'
GO
DROP PROCEDURE au_infoHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Thanks... just wondering you would consider piecing together the sql statement and running it. I tried it and it appears to execute a lot faster. Below is the code to display this.
use pubs
go
CREATE PROCEDURE au_info1 @lastname varchar(40) = NULL, @firstname varchar(20) = NULL AS SELECT au_lname, au_fname FROM authors WHERE (au_lname = @lastname or au_fname = @firstname)
go
CREATE PROCEDURE au_info2 @lastname varchar(40) = NULL, @firstname varchar(20) = NULL AS SET @lastname = '%' + @lastname + '%'SET @firstname = '%' + @firstname + '%' SELECT au_lname, au_fname FROM authors
WHERE (au_lname LIKE @lastname or au_fname LIKE @firstname)
GO
CREATE PROCEDURE au_info3 @lastname varchar(40) = NULL, @firstname varchar(20) = NULL AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname = @lastname
union
SELECT au_lname, au_fname
FROM authors
WHERE au_fname =@firstname
go
CREATE PROCEDURE au_info4 @lastname varchar(40) = NULL, @firstname varchar(20) = NULL AS
declare @STMT varchar(8000)
SET @STMT = 'SELECT au_lname, au_fname FROM authors WHERE 1=1 '
IF (@lastname IS NOT NULL)
SET @STMT = @STMT + ' AND au_lname = ''' + @lastname + ''''
IF (@firstname IS NOT NULL)
SET @STMT = @STMT + ' AND au_fname = ''' + @firstname + ''''
EXEC (@stmt)
go
GO
print convert(varchar(50), getdate(), 113)
EXECUTE au_info1 @lastname = 'Ringer'
print convert(varchar(50), getdate(), 113)
EXECUTE au_info2 @lastname = 'Ringer'
print convert(varchar(50), getdate(), 113)
EXECUTE au_info3 @lastname = 'Ringer'
print convert(varchar(50), getdate(), 113)
EXECUTE au_info4 @lastname = 'Ringer'
print convert(varchar(50), getdate(), 113)
GO
DROP PROCEDURE au_info1
DROP PROCEDURE au_info2
DROP PROCEDURE au_info3
DROP PROCEDURE au_info4
-
hello,
you might even try this 'easy' way in the where-statement:
where
(@firstname is null or au_fname like @firstname+'%')
and
(@lastname is null or au_lname like @lastname+'%')
-
What you seem to really want is to match anything where the field supplied is null, and match the supplied string if not null:
CREATE PROCEDURE au_info
(
@lastname varchar(40) = null,
@firstname varchar(40) = null
)
AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname like IsNull(@lastname,'%')
and au_fname like IsNull(@firstname,'%')
go
This will work when a parameter is left out, supplied as null, and when the caller uses '%' in the values.
Unless the null is inside quotes, you are matching on a null value, not the word null as some posters suggested. In your original query, the column au_fname would have to have been null to match.
Sloan
Sloan
If you can't do it in SQL, get a bigger hammer! -
It certainly took awhile for someone to point out the use of IsNull(). Sloan definitely hit the target.
-
You can also use a different query in each case. I resort to this when I can't get one query to execute quickly for all cases. This query is sort enough that it's easy.
IF @ExactMatch = 0 BEGIN -- partial match is okay
SET @lastname = @lastname + '%'
SET @firstname= @firstname+ '%'
END
IF @lastname IS NULL AND @firstname IS NULL
SELECT au_lname, au_fname
FROM authors
ELSE IF @firstname IS NULL
SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE @lastname
ELSE IF @lastname IS NULL
SELECT au_lname, au_fname
FROM authors
WHERE au_fname LIKE @firstname
ELSE
SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE @lastname and au_fname LIKE @firstname
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Ok... thanks for everyone's responses. However, for discussion purposes, I don't think using the LIKE operator for the optional parameters is not entirely sufficient. What would you do if you want to filter based on datetime? for example (I would like to retrieve all sales records starting from 1/1/1994. then in a second report, I would want all sales records ending 12/31/1993. then in a third report, I would want all sales records in first quarter of 1994.):
use pubs
go
CREATE PROCEDURE sales_info @ord_num varchar(20) = null, @start_date datetime = null, @end_date datetime = null, @payterms varchar(12) = null AS
SELECT * FROM sales WHERE ord_num like IsNull(@ord_num,'%') and payterms like IsNull(@payterms,'%') and ord_date >= @start_date and ord_date < dateadd(d, 1, @end_date)
go
EXECUTE sales_info @ord_num = null, @start_date = '1/1/1994', @end_date = null, @payterms = null
GO
DROP PROCEDURE sales_info
-
This may work for what you want. As long as an unknown value is passed as null it will return the correct results.
For example if the value for @FirstNM is null no records will be filtered out because FirstNM will always be equal to itself and if you pass 'Ringer' for @LastNM it will filter for any records with that value. I also included a date range filter but you will need to adjust the datepart to suit your needs.
CREATE Procedure usp_TestVariables (
--general search fields
@FirstNM varchar(25) = null,
@LastNM varchar(25) = null,
@MinValue int = null,
@MaxValue int = null,
@StartDate datetime = null,
@EndDate datetime = null)
AS
Select *
from table1
-- Check to for a value is within a range, set defaults to lowest and highest possible values
Where value BETWEEN ISNULL(@MinValue,0) AND ISNULL(@MaxValue,999999999)
-- Check for an exact match on first name
and FirstNM = ISNULL(@FirstNM,FirstNM)
-- Check for a like match on last name
and LastNM Like ISNULL('%'+@LastNM+'%',LastNM)
-- Check for a date range
and datediff(d, DateValue,Getdate())
between datediff(d,@StartDate, Getdate())
and datediff(d,@EndDate, Getdate())
-
Hi,
change your proc as shown below
CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname = isnull(@lastname,au_lname) and au_fname = isnull(@firstname,au_fname)
go
EXECUTE au_info @firstname = null, @lastname = null
GO
DROP PROCEDURE au_info
-
One other way to write this sort of query - which I find to be a very useful technique is :-
CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname = @lastname
and au_fname = case isnull(@firstname,'')='' then au_fname else @firstname end
GO
EXECUTE au_info @firstname = '', @lastname = 'Ringer'
GO
This way it doesn't matter if there is a value in au_fname or not.!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply