Stored Procedure help needed - select string composition
-
Hi
I am pretty new to stored procedures, but an old hand at crafting select statements by hand. I have an ASP.net system (using VB) that must display 150 different Crystal Reports. I have tried various different methods to feed these reports their needed data, but in the end it seems to be best to use stored procedures .
On to the actual problem.
I need my stored procedure to return a recordset to its report. this is pretty easily done through a simple "select" SP, which just basically encapsulates a select command. However, I need to be able to modify the "where" clause of the selection based on parameters. For example,
CREATE PROCEDURE spMyproc(@param1 int) AS
SELECT * FROM Users WHERE Username = @param1
GO
would be the basic way. Now what I want is for it to disregard the Username filter if @param1 is null. What effective manner is there for me to implement that? My select statement is quite large, and there are multiple parameters that need to be considered as above, so making a block if statement is not feasible.
-
This is one way:
where username = IsNull(@param1, username)
if @param1 is NULL, the IsNull function returns username, which is, of course, always equal to itself.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ah, great! Thanks Phil. That should work great for the simple filters. Unfortunately, I just realized that I have a couple more intricate filters to do as well {stuff like WHERE left(field4, len(field2)-2) = @param1}.
I thought I'd try to solve the abovementioned problem by doing the "basic" (without conditional filters) selection into a temp table (#temptable1). Then, I would run through each conditional filter and do a re-selection into a #temptable2, and finally just drop the results of that selection back into #temptable1. The SQL I used looked like this:
.....
CREATE PROCEDURE CR_Selection @param1 varchar(50) = NULL
AS
SELECT
table1.field1, table1.field2, table2.field3, table3.field4
INTO #temptable1
FROM table1 INNER JOIN table2 ON table1.table1id = table2.table2id INNER JOIN table3 ON table2.table2id = table3.table3id
IF @param1 IS NOT NULL
BEGIN
SELECT *
INTO #temptable2
FROM #temptable1
WHERE left(field4, len(field2)-2) = @param1
DROP TABLE #temptable1
SELECT * INTO #temptable1 FROM #temptable2
DROP TABLE #temptable2
END
GO
.....
but when I try to verify my SQL, the Stored Procedure Syntax Checker throws an error for the third select (back INTO #temptable1), saying that #temptable1 already exists. I thought that the DROP TABLE #temptable1 would work... What am I doing wrong here?
-
Sorry, I just realized that your solution is still valid, even for the more complex filter. However, I would like to know the reason for the error (assuming someone can help) for interest sake.
Thanks for the help!
-
Another way is to use default values on your parameters of '%'.
So the above example becomes:
where username like @param1
if @param1 has a value of '%' (instead of null) then it will return everything. Note that this works for integers too.
The only problem with this solution is that you won't be making the best use of any indexes. However, tith the IsNull solution you'll be forcing a scan instead because of the use of a function in the WHERE clause so you're in pretty much the same situation.
What I don't know is which is the better solution in terms of performance.
Ultimately, if you want the best possible performance you probably have to resort to using IF...ELSE statements.
Hope that helps,
-
It's all about how the compiler works as it tries to optimise the code. God knows what's going on in detail though.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hmmm.
Thanks for everyone's suggestions. I am not overly concerned about performance here - Crystal's method of querying is far less efficient than the most basic SQL filter (it normally selects EVERYTHING in each referenced table, and then do the joins internal to Crystal!), so pretty much anything I do here will improve.
So there isn't anything wrong with my logic there, right?
Please note, I will probably not use that hack, but it's an interesting method which appeals to the programmer in me, and should be useful for more general cases (value @param1 for instance, or some other arbitrary case).
-
Try this:
CREATE PROCEDURE spMyproc(@param1 int) AS
DECLARE @SQL_String varchar(4098)
If ISNULL(@param1,'') = ''
BEGIN
SET @SQL_String = 'SELECT * FROM Users'
END
IF CHARINDEX('A',@param1,1) > 0
BEGIN
SET @SQL_String = 'SELECT * FROM Users WHERE USername = ' + LEft(@param1,CHARINDEX('A',@param1,1))
END
(and so on)......
Exec (@SQL_String 
GO
Hopefully you get the idea from above.
Rick Sheeley, DBA, ETelecare, Scottsdale, AZ
-
This will keep you from having to duplicate your column names / complex formulae on both sides of the = operator.
If @param1 is null, query planner just pulls everything instead of isNull()-ing the parameter to the column/formula for each row, to boot.
CREATE PROCEDURE spMyproc(@param1 int) AS
SELECT
*
FROM
Users
WHERE
left(field4, len(field2)-2) = @param1 OR @param1 IS NULL
GO
-
Excellent! Thanks everybody for your help, these are a bunch of really valuable tips. I prefer the @SQLString method, for it's flexibility, even though it can take longer to execute (SQL Server takes time to do conditionals and string concatenations it would seem), and all the other tips will come in handy too.
My procedure has been crafted, and it works well, thanks to y'all.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply