December 8, 2010 at 6:42 pm
I'd like to create a stored procedure with parameters which returns a query result. Pretty simple but here's the thing - I want it to be flexible so that if some of the parameters are missing, it will still return the results based on the other parameters. Here it is:
ALTER PROCEDURE [dbo].[PTicketSearch]
(
@StartDate datetime,
@EndDate datetime,
@Submitter varchar(100),
@Assigned varchar(100),
@status varchar(10)
)
AS
Select *
From ProblemTicket
Where ProbTicketDate Between @StartDate And @EndDate
AND ProbTicketSubName = @Submitter
AND ProbTicketAssigned = @Assigned
AND ProbTicketStatus = @status
So say for example, I provide all the parameters except for @status and @Submitter. I want everything to come back which falls between the start/end dates and is assigned to whomever I specify in the @Assigned parameter.
So basically, I want to create a flexible stored procedure which can return results based on any combination of parameters I choose. Please advise. Thanks in advance!
December 8, 2010 at 7:25 pm
The quick and dirty solution is this:
Select *
From ProblemTicket
Where ( (ProbTicketDate Between @StartDate And @EndDate) OR (@StartDate IS NULL))
AND (ProbTicketSubName = @Submitter OR @Submitter IS NULL)
AND (ProbTicketAssigned = @Assigned OR @Assigned IS NULL)
AND (ProbTicketStatus = @status OR @status IS NULL)
If that runs too slow you're looking at using an if map against different parameter sets and creating specific queries with basically the same structure but different where clauses.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 8:01 pm
Hi I think if you write like this it will help you:
ALTER PROCEDURE [dbo].[PTicketSearch]
(
@StartDate datetime,
@EndDate datetime,
@Submitter varchar(100),
@Assigned varchar(100),
@status varchar(10)
)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ' SELECT * FROM PROBLEMTICKET WHERE 1=1 '
IF (@STARTDATE IS NOT NULL)
BEGIN
SET @SQL = @SQL +' AND PROBTICKETDATE >= ' + @STARTDATE
END
IF (@ENDDATE IS NOT NULL)
BEGIN
SET @SQL = @SQL +' AND PROBTICKETDATE <= ' + @ENDDATE
END
IF (@SUBMITTER IS NOT NULL)
BEGIN
SET @SQL = @SQL +' AND PROBTICKETSUBNAME = ' + @SUBMITTER
END
IF(@ASSIGNED IS NOT NULL )
BEGIN
SET @SQL = @SQL +' AND PROBTICKETASSIGNED = ' + @ASSIGNED
END
IF(@STATUS IS NOT NULL)
BEGIN
SET @SQL = @SQL +' AND PROBTICKETSTATUS = ' + @status
END
EXECUTE(@SQL)
END
Note: you might have to do some conversion for the date fields if required nad >= nad <= condtion if required.
Thanks & Regards,
MC
December 9, 2010 at 11:27 am
Thanks Craig!
December 9, 2010 at 11:29 am
Thanks Only4! BTW, could you please explain to me this part:
SET @SQL = ' SELECT * FROM PROBLEMTICKET WHERE 1=1 '
I'm not familiar with the 'WHERE 1=1' part. Could you please explain how that works? Thanks!
December 9, 2010 at 11:41 am
dso808 (12/9/2010)
Thanks Only4! BTW, could you please explain to me this part:SET @SQL = ' SELECT * FROM PROBLEMTICKET WHERE 1=1 '
I'm not familiar with the 'WHERE 1=1' part. Could you please explain how that works? Thanks!
1=1 is always true. It's a trick so that you don't have to have wrapper logic to decide if the word WHERE belongs in your query. WHERE 1=1 is always true, so you're not dealing with syntax errors, and then you can just add AND statements to that for proper clause methodology.
It just tightens your code... and is something I hadn't thought of. Nice idea, MC. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 12:32 pm
OK, I guess this is what you meant by this part:
"Note: you might have to do some conversion for the date fields if required nad >= nad <= condtion if required."
Yup, I'm getting the following error message:
"Conversion failed when converting datetime from character string."
I've tried using CAST on ProbTicketDate with no success. Any suggestions? Thanks again!
December 9, 2010 at 12:37 pm
dso808 (12/9/2010)
OK, I guess this is what you meant by this part:"Note: you might have to do some conversion for the date fields if required nad >= nad <= condtion if required."
Yup, I'm getting the following error message:
"Conversion failed when converting datetime from character string."
I've tried using CAST on ProbTicketDate with no success. Any suggestions? Thanks again!
Need to see some samples of what's actually in ProbTicketDate first, and the DDL for the table in question.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 12:53 pm
Hi Craig and thanks for the speedy response. The [ProbTicketDate] field is a DateTime field. An example of what's stored in the table - '1/4/2010 12:00:00 AM'. Thanks again
December 9, 2010 at 12:59 pm
dso808 (12/9/2010)
Hi Craig and thanks for the speedy response. The [ProbTicketDate] field is a DateTime field. An example of what's stored in the table - '1/4/2010 12:00:00 AM'. Thanks again
Ah, then it's on the parameters themselves. Wrap @startdate / @enddate in the concatonation (where the +'s are) with CONVERT( VARCHAR(20), @StartDate, 101).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 1:03 pm
Craig Farrell (12/8/2010)
The quick and dirty solution is this:
The quick, dirty and generally non-performent solution.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Edit: Just note that MC's version is vulnerable to SQL Injection. The version I have in the above blog post is not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2010 at 1:22 pm
Thanks Craig! That's taken care of now. But when I execute the stored procedure to test like so (just trying to get ALL records from the table):
-- exec PTicketSearch '','','','',''
I get this error message:
-- Incorrect syntax near the keyword 'AND'.
Perhaps a problem here?
-- SET @SQL = 'SELECT * FROM PROBLEMTICKET WHERE 1=1 '
Thanks again!
December 9, 2010 at 1:33 pm
dso808 (12/9/2010)
Thanks Craig! That's taken care of now. But when I execute the stored procedure to test like so (just trying to get ALL records from the table):-- exec PTicketSearch '','','','',''
I get this error message:
-- Incorrect syntax near the keyword 'AND'.
Perhaps a problem here?
-- SET @SQL = 'SELECT * FROM PROBLEMTICKET WHERE 1=1 '
Thanks again!
Nope, that line's right. Can you repost your current code?
Gail, sadly, I know. I didn't say it was pretty, just easy to maintain and is 'good enough' against smaller recordsets. Anything "good" requires the headaches that dso is currently trying to deal with. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 1:39 pm
Here you go! Thanks again Craig.
ALTER PROCEDURE [dbo].[PTicketSearch]
(
@StartDate datetime,
@EndDate datetime,
@Submitter varchar(100),
@Assigned varchar(100),
@status varchar(10)
)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT * FROM PROBLEMTICKET WHERE 1=1 '
IF (@STARTDATE IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PROBTICKETDATE >= ' + CONVERT( VARCHAR(20), @StartDate, 101)
END
IF (@ENDDATE IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PROBTICKETDATE <= ' + CONVERT( VARCHAR(20), @EndDate, 101)
END
IF (@SUBMITTER IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PROBTICKETSUBNAME = ' + @Submitter
END
IF (@ASSIGNED IS NOT NULL )
BEGIN
SET @SQL = @SQL + ' AND PROBTICKETASSIGNED = ' + @Assigned
END
IF (@STATUS IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND PROBTICKETSTATUS = ' + @status
END
EXECUTE(@SQL)
END
December 9, 2010 at 1:40 pm
Thanks GilaMonster for the link! Very interesting and helpful blog post.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply