June 3, 2010 at 11:12 am
Okay, here's my scenario: I created a stored procedure that returns data based on some table JOINs. The query produces data that looks similar to this (names and values changed to protect the innocent):
create table #NameList
(
[ID] int,
[Name] varchar(50),
[City] varchar(50),
[County] varchar(50),
[SpecID] int,
[OfficeID] int
)
insert into #NameList values (1, 'Doe, John', 'Albany', 'Albany', 21, 1)
insert into #NameList values (1, 'Doe, John', 'Troy', 'Rensselaer', 21, 3)
insert into #NameList values (2, 'Doe, Jane', 'Troy', 'Rensselaer', 14, 3)
insert into #NameList values (3, 'Public, John', 'Colonie', 'Albany', 21, 2)
insert into #NameList values (4, 'Smith, John', 'Albany', 'Albany', 11, 4)
insert into #NameList values (5, 'Griffey, Ken', 'Niskayuna', 'Schenectady', 21, 8)
insert into #NameList values (6, 'Biden, Joe', 'Syracuse', 'Onondaga', 16, 14)
insert into #NameList values (7, 'Girardi, Joe', 'Saratoga Springs', 'Saratoga', 14, 6)
insert into #NameList values (8, 'Obama, Barack', 'Albany', 'Albany', 11, 1)
insert into #NameList values (9, 'Boeheim, Jim', 'Syracuse', 'Onondaga', 21, 14)
insert into #NameList values (10, 'Jeter, Derek', 'Colonie', 'Albany', 21, 2)
insert into #NameList values (10, 'Jeter, Derek', 'Colonie', 'Albany', 11, 2)
insert into #NameList values (10, 'Jeter, Derek', 'Albany', 'Albany', 21, 1)
insert into #NameList values (10, 'Jeter, Derek', 'Albany', 'Albany', 11, 1)
insert into #NameList values (11, 'Brown, Jim', 'Syracuse', 'Onondaga', 11, 14)
insert into #NameList values (12, 'Bourdain, Anthony', 'Albany', 'Albany', 11, 1)
insert into #NameList values (13, 'Adama, William', 'Albany', 'Albany', 21, 4)
insert into #NameList values (14, 'John, Elton', 'Niskayuna', 'Schenectady', 21, 8)
insert into #NameList values (15, 'Zimmern, Andrew', 'Saratoga Springs', 'Saratoga', 21, 6)
insert into #NameList values (16, 'Patrick, Danica', 'Troy', 'Rensselaer', 14, 3)
insert into #NameList values (17, 'Matsui, Hideki', 'Albany', 'Albany', 21, 1)
insert into #NameList values (18, 'Rivera, Mariano', 'Colonie', 'Albany', 14, 2)
insert into #NameList values (19, 'Oates, Adam', 'Troy', 'Rensselaer', 21, 3)
insert into #NameList values (20, 'Scholz, Tom', 'Albany', 'Albany', 11, 1)
select * from #NameList
Here's where I'm running into trouble. I want the SP to return rows based on the parameters passed through the SP. However, I'm running into an issue because of the conflict between [Name] LIKE 'whatever%' and [SpecID] = 'some number'.
Here are the ground rules for the WHERE clause.
I thought this would be easy enough, but I haven't been able to get the logic right. I tried something similar to the following (the variables are the parameters that are passed to the SP):declare @ByName varchar(25) = ''
declare @ByID int = ''
declare @BySpecID int = ''
declare @ByOfficeID int = ''
declare @ByCity varchar(25) = '%'
declare @ByCounty varchar(11) = '%'
set @ByName = @ByName + '%'
select * from #NameList
where (Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
The problem is that if I specify one of the IDs, it returns EVERYTHING (that's specified in the text filters), not just the results for that ID. Of course it does; such is the nature of OR! And if I change the first OR to AND, then it doesn't return anything, because none of the integer IDs are specified, making that half of the conditional FALSE.
I tried different combinations of AND and OR (not to mention different groupings), but I can't seem to get it to work. I was thinking how great it would be if the first OR could be an exclusive OR (hence my earlier post).
I've been playing with this all morning, and I'm not getting very far. Anyone have any insight?
Edit: I changed my sample data set, because I forgot that it is possible for individuals to have multiple specialties (SpecID) and/or offices (OfficeID). And because the result is from JOIN statements, the primary key wouldn't be set.
Edit #2: added another requirement bullet point.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 11:21 am
Am I misunderstanding you, or would this not work:
select * from #NameList
where (ISNULL(@ByID, '') <> '' AND ISNULL(@BySpecID, '') <> '' AND ISNULL(@ByOfficeID, '') <> '' AND Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
June 3, 2010 at 11:28 am
nabidavid (6/3/2010)
Am I misunderstanding you, or would this not work:
select * from #NameListwhere (ISNULL(@ByID, '') <> '' AND ISNULL(@BySpecID, '') <> '' AND ISNULL(@ByOfficeID, '') <> '' AND Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
I just tried it, and it didn't work. One thing I probably didn't mention: if no parameters are specified, it needs to return everything (as if there was no WHERE clause).
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 11:33 am
Ray K (6/3/2010)
nabidavid (6/3/2010)
Am I misunderstanding you, or would this not work:
select * from #NameListwhere (ISNULL(@ByID, '') <> '' AND ISNULL(@BySpecID, '') <> '' AND ISNULL(@ByOfficeID, '') <> '' AND Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
I just tried it, and it didn't work. One thing I probably didn't mention: if no parameters are specified, it needs to return everything (as if there was no WHERE clause).
I re-read your post and your requirements. I think I had a "Doh!" I should not included the @ByID in the first part of the WHERE clause. If this is not the solution, could you give an example of how it is failing so I can be sure that I do correctly understand the issue?
Try this:
select * from #NameList
where (ISNULL(@BySpecID, '') <> '' AND ISNULL(@ByOfficeID, '') <> '' AND Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
Thanks,
Mike
June 3, 2010 at 11:41 am
nabidavid (6/3/2010)
Try this:
select * from #NameListwhere (ISNULL(@BySpecID, '') <> '' AND ISNULL(@ByOfficeID, '') <> '' AND Name like @ByName and City like @ByCity and County like @ByCounty)
or (ID = @ByID or SpecID = @BySpecID or OfficeID = @ByOfficeID)
Yeah, thanks for the attempt, but still no dice. In this query, if I don't specify any parameters (so it uses the defaults), it's returning an empty result set. Like I said earlier, if no parameters are specified, it needs to return everything (similar to select * from #NameList without a WHERE clause).
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 11:54 am
I really think you should use Dynamic SQL, for handling those special cases. And by the way, you will get a better performance than when using a "Catch All" plan like this.
Do you need an example to build your custom Dynamic SQL Query?
Cheers,
J-F
June 3, 2010 at 12:00 pm
J-F Bergeron (6/3/2010)
I really think you should use Dynamic SQL, for handling those special cases. And by the way, you will get a better performance than when using a "Catch All" plan like this.Do you need an example to build your custom Dynamic SQL Query?
As a matter of fact, I was thinking about dynamic SQL, but I don't have any experience with building them in stored procedures. I've built them many a time within my web apps, but I'm trying to avoid that route for many reasons (not the least of which include security, portability, and ease of maintenance).
If you have an example of how to build dynamic SQL in an SP, I'm all ears!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 12:00 pm
declare @ByName varchar(25)
declare @BySpecID int
declare @ByOfficeID int
declare @ByCity varchar(25)
declare @ByCounty varchar(11)
set @BySpecID = 21
set @ByName = 'r'
set @ByCounty = 'Albany'
select *
from #NameList
where [Name] like '%' + @ByName + '%'
and [City] = coalesce(@ByCity, [City])
and [County] = coalesce(@ByCounty, [County])
and [SpecID] = coalesce(@BySpecID, [SpecID])
and [OfficeID] = coalesce(@ByOfficeID, [OfficeID])
The problem with search procs with values that may or may not be supplied and then doing it in the above fashion is that you're looking at the same execution plan regardless of which parameters have values supplied. So, if the best index is on the SpecID, but a SpecID isn't supplied, the execution plan is still going to use it. On a small system, this isn't really going to matter, but on a large system you can take a crushing performance hit. There are 3 ways that I know of to get around this issue ...
1. You can do IF statements to have separate selects based on which parameters are non null. Each block would have its own cached plan.
2. You can use a recompile hint, but this only works if you have 2k8 with the latest patch. Recompile doesn't work right unless you are running 2008 SP1 CU5 (10.0.2746) or higher.
3. You can use dynamic sql to build your query. Each possible combination of parameters would have its own cached plan.
June 3, 2010 at 12:18 pm
I just found sp_executesql in BOL.
I'm going to mess around with this a little. If I get anywhere with it, I'll be sure to post whatever solution I conjure up.
Thanks for the ideas, everyone!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 12:54 pm
Ray K (6/3/2010)
I just found sp_executesql in BOL.I'm going to mess around with this a little. If I get anywhere with it, I'll be sure to post whatever solution I conjure up.
Thanks for the ideas, everyone!
Hi Ray,
The sp_executeSQL is the best way to use dynamic SQL within a procedure, and here's a little sample to avoid parameter sniffing, and SQL injection, which are the major downfall of dynamic SQL. The key is to pass the parameters as parameters, and not append them directly into the string you are building.
Here's how you can do it:
IF EXISTS ( SELECT *
FROM sysobjects
WHERE TYPE = 'P'
AND name = 'TestDynamicProc' )
BEGIN
DROP PROCEDURE TestDynamicProc
END
GO
CREATE PROCEDURE [dbo].TestDynamicProc
(
@ByName VARCHAR(25) = NULL ,
/*@ByID int = NULL,
@BySpecID int = NULL,
@ByOfficeID int = NULL,
@ByCity varchar(25) = NULL,
@ByCounty varchar(11) = NULL,*/
@DebugMode BIT = 0
)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) -- Hold the Dynamic SQL
DECLARE @ParamList NVARCHAR(MAX) -- Hold the dynamic parameters string that we will pass to sp_ExecuteSQL
SELECT @SQL = N'select * from sys.objects
where 1 = 1'
--=== Possible condition 1
IF @ByName IS NOT NULL
BEGIN
SELECT @SQL = @SQL + ' AND Name LIKE @xByName + ''%'' '
END
--=== Possible condition 2...
/*IF @ByName IS NOT NULL
BEGIN
SELECT @SQL = @SQL + ' AND Name LIKE @xByName + ''%'' '
END */
--=== Possible condition 3...
/*IF @ByName IS NOT NULL
BEGIN
SELECT @SQL = @SQL + ' AND Name LIKE @xByName + ''%'' '
END */
--=== Declare our set of parameters
SELECT @ParamList = '
@xByName varchar(25)
' -- Insert more params here
IF @DebugMode = 1
BEGIN
PRINT @SQL
PRINT @ParamList
END
--=== Passing the parameters to the executeSQL Statement, to ensure they are sent as parameters,
-- and no SQL injection is possible
EXEC sp_executesql @sql, @paramlist, @ByName
END
GO
EXEC TestDynamicProc 'D', 1
Tell me if you have any questions,
Hope that gets you going,
Cheers,
J-F
June 3, 2010 at 1:07 pm
J-F -- yes, this is helpful! Before I read your post, I actually did manage to get my SP working properly using dynamic SQL. However, as you mentioned, I did fall into the trap of appending my parameters rather than passing them.
I'm going to try reworking it so that I'm passing the parameters rather than concatenating them to my SQL string. I'll let you know how I make out!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 1:32 pm
GOT IT TO WORK!!! I set it up to pass the parameters rather than appending them to the string. It works like a champ!
Thanks for your help, everyone!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 1:36 pm
Good, you might want to post the parcel of code if someone else is looking for the same answer?
And we might review some of it, maybe it can be constructive for you!
Glad it worked out for you,
Cheers,
J-F
June 3, 2010 at 1:54 pm
J-F Bergeron (6/3/2010)
Good, you might want to post the parcel of code if someone else is looking for the same answer?And we might review some of it, maybe it can be constructive for you!
My pleasure . . . here it is!
-- These are the SP parameters that are passed by the app that calls it --
declare @ByName varchar(25) = ''
declare @ByID int = ''
declare @BySpecID int = ''
declare @ByOfficeID int = ''
declare @ByCity varchar(25) = ''
declare @ByCounty varchar(11) = ''
-- End parameter list --
set @ByName = @ByName + '%'
declare @sql nvarchar(1024)
declare @PList nvarchar(512) = N'@Name varchar(25), @City varchar(25), @County varchar(11), @ID int, @SpcID int, @OffID int'
set @sql = N'select * from #NameList where Name like @Name '
if rtrim(@ByCity) <> '' set @sql = @sql + 'and City = @City '
if rtrim(@ByCounty) <> '' set @sql = @sql + 'and County = @County '
if @ByID > 0 set @sql = @sql + 'and ID = @ID '
if @BySpecID > 0 set @sql = @sql + 'and SpecID = @SpcID '
if @ByOfficeID > 0 set @sql = @sql + 'and OfficeID = @OffID '
set @sql = @sql + 'order by Name, County, City'
exec sp_executesql @sql,
@PList,
@Name = @ByName,
@City = @ByCity,
@County = @ByCounty,
@ID = @ByID,
@SpcID = @BySpecID,
@OffID = @ByOfficeID
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 3, 2010 at 7:49 pm
Ray K, nice that you have fixed the code, but for soehting called SQL Injection, you will still need to tweak the code a bit.. Please go through these following 2 article from MVP Gail Shaw to understand what i am saying :
Link for Article 1 : Dynamic SQL and SQL injection[/url]
Link for Article 2 : Catch-all queries[/url]
Hope this helps!!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply