September 26, 2013 at 8:25 pm
DECLARE @sqlquery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max)
SET @sqlquery = ''
SET @SqlQueryStatus = ''
SET @SqlQueryFirstname = ''
SET @SqlQueryMiddlename = ''
SET @SqlQueryLastName = ''
SET @SqlQuerySex = ''
SET @SqlQueryStatus = ''
IF @sexID <> 0
SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)
IF @statusID <> 0
BEGIN
IF LEN(@SqlQuerySex) > 0
SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID)
ELSE
SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID)
END
IF LEN(@firstname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0
SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%'''
ELSE
SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%'''
END
IF LEN(@middlename) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0
SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%'''
ELSE
SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%'''
END
IF LEN(@lastname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0
SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%'''
ELSE
SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%'''
END
SET @sqlquery = ' SELECT * FROM TestMyView ' + @SqlQuerySex + @SqlQueryStatus + @SqlQueryFirstname + @SqlQueryMiddlename + @SqlQueryLastname
September 26, 2013 at 10:13 pm
The short answer is yes, however I have several issues with what you're trying to do.
1. [Issue #1] In the statement below, you're setting the right part of the quality to a characters string but unless @SexID already contains embedded quotes the dynamic SQL you're constructing is going to fail.
SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)
2. [Issue #2] What happens if more than one of the query parameters has been sent through? It seems that you'll have a WHERE keyword in multiple places in your dynamic SQL.
3. You can get around issue #2 quite easily (and answer you're original question) by simply starting with the basic SELECT/FROM in a string and then using CASE to attach to it the additional query parameters. Include the WHERE only if any of the filter parameters are specified.
4. [Issue #3 and solution to issue #1] Instead of assigning the value to the SQL string for filter parameters, consider checking for field = @Filter, and then passing @Filter to sp_executesql. Check BOL for a description of how to do that (I'm guessing that you were planning on using EXEC (@SQL) instead).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 26, 2013 at 10:19 pm
And I just noted that while your question is different, the answers to your earlier post:
http://www.sqlservercentral.com/Forums/Topic1498669-1292-1.aspx
will cross over with the answers here.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 1:03 am
dwain.c (9/26/2013)
The short answer is yes, however I have several issues with what you're trying to do.1. [Issue #1] In the statement below, you're setting the right part of the quality to a characters string but unless @SexID already contains embedded quotes the dynamic SQL you're constructing is going to fail.
SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)
2. [Issue #2] What happens if more than one of the query parameters has been sent through? It seems that you'll have a WHERE keyword in multiple places in your dynamic SQL.
3. You can get around issue #2 quite easily (and answer you're original question) by simply starting with the basic SELECT/FROM in a string and then using CASE to attach to it the additional query parameters. Include the WHERE only if any of the filter parameters are specified.
4. [Issue #3 and solution to issue #1] Instead of assigning the value to the SQL string for filter parameters, consider checking for field = @Filter, and then passing @Filter to sp_executesql. Check BOL for a description of how to do that (I'm guessing that you were planning on using EXEC (@SQL) instead).
Hi Dwain
Can you elaborate more?..i can't understand the issue 🙁 poor me ...
September 27, 2013 at 1:20 am
Perhaps a rather incomplete example will help:
SET @sqlquery = ' SELECT * FROM TestMyView WHERE 1 = 1 ' +
CASE WHEN @sexID <> 0 THEN ' AND sexID = @sexID' ELSE '' END +
<More CASE statements - one for each part of the WHERE clause
-- Then call sp to execute
EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID' -- one for each of your filters
,@sexID=@sexID
,@statusid=@statusid
Note how I include WHERE with 1=1 so that subsequent concatenates to the string can be started with AND.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 1:21 am
Dwain,,,,
My sexID will never contain embedded quotes...because with this table
*Bio
bioIB
fname
mname
lname
sexID
statusID
*sex
sexID
sex(male or female only)
*status
statusID
status(single,married or divorced only)
sexID and statusID from bioID belongs to different table...
so my View table would be
*MyViewTable
(Contains all in table bio, sex and status)..
i can just pass sexID and statusID to my SP...
September 27, 2013 at 1:28 am
enriquezreyjoseph (9/27/2013)
Dwain,,,,My sexID will never contain embedded quotes...because with this table
*Bio
bioIB
fname
mname
lname
sexID
statusID
*sex
sexID
sex(male or female only)
*status
statusID
status(single,married or divorced only)
sexID and statusID from bioID belongs to different table...
so my View table would be
*MyViewTable
(Contains all in table bio, sex and status)..
i can just pass sexID and statusID to my SP...
Yes, sorry. I realized on my last post that SexID is probably a number like 1,2. So you can ignore that issue that I raised. I think the others are still valid, as is the partial example I posted.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 1:40 am
Thanks Dwain...so should i use CASE Statement instead??..
September 27, 2013 at 1:50 am
That is my recommendation.
I find the resulting code to be a bit easier to understand.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 2:06 am
dwain.c (9/27/2013)
That is my recommendation.I find the resulting code to be a bit easier to understand.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]
@firstname nvarchar(50),
@middlename nvarchar(50),
@lastname nvarchar(50),
@sexID nchar,
@statusID nchar
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery varchar(max)
SET @sqlquery = ''
SET @sqlquery = ' SELECT * FROM TestMyView ' +
CASE
WHEN @sexID <> 0 OR @statusID <> 0 OR LEN(@firstname) > 0 OR LEN(@middlename) > 0 OR LEN(@lastname) > 0
THEN ' WHERE 1=1 '
ELSE ''
END +
CASE
WHEN @sexID <> 0
THEN ' AND sexID = ' + convert(varchar(20), @sexID)
ELSE ''
END +
CASE
WHEN @statusID <> 0
THEN ' AND statusID = ' + convert(varchar(20), @statusID)
ELSE ''
END +
CASE
WHEN LEN(@firstname) > 0
THEN ' AND firstname like ''%' + @firstname + '%'''
ELSE ''
END +
CASE
WHEN LEN(@middlename) > 0
THEN ' AND middlename like ''%' + @middlename + '%'''
ELSE ''
END +
CASE
WHEN LEN(@lastname) > 0
THEN ' AND lastname like ''%' + @lastname + '%'''
ELSE ''
END
EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname' -- one for each of your filters
,@sexID=@sexID
,@statusID=@statusID
,@firstname=@firstname
,@middlename=@middlename
,@lastname=@lastname
END
it wont work dwain 🙁 poor me...
September 27, 2013 at 2:11 am
this is it
September 27, 2013 at 2:14 am
enriquezreyjoseph (9/27/2013)
SET @sqlquery = ' SELECT * FROM TestMyView ' +CASE
WHEN @sexID <> 0 OR @statusID <> 0 OR LEN(@firstname) > 0 OR LEN(@middlename) > 0 OR LEN(@lastname) > 0
THEN ' WHERE 1=1 '
ELSE ''
END +
Above can be changed to just:
SET @sqlquery = ' SELECT * FROM TestMyView WHERE 1=1' +
Then just looking at one of the CASE statements:
enriquezreyjoseph (9/27/2013)
CASEWHEN @sexID <> 0
THEN ' AND sexID = ' + convert(varchar(20), @sexID)
ELSE ''
END +
You should be able to change this to:
CASE
WHEN @sexID <> 0
THEN ' AND sexID = @Sexid'
ELSE ''
END +
Thus allowing you to pass @SexID into sp_executesql and avoid the SQL injection issue GilaMonster (Gail Shaw) chastised you about in another thread.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 2:17 am
enriquezreyjoseph (9/27/2013)
this is it
Never seen that error but it looks like it is complaining about the typing of one of the parameters to your SP.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 27, 2013 at 2:25 am
dwain.c (9/27/2013)
enriquezreyjoseph (9/27/2013)
SET @sqlquery = ' SELECT * FROM TestMyView ' +CASE
WHEN @sexID <> 0 OR @statusID <> 0 OR LEN(@firstname) > 0 OR LEN(@middlename) > 0 OR LEN(@lastname) > 0
THEN ' WHERE 1=1 '
ELSE ''
END +
Above can be changed to just:
SET @sqlquery = ' SELECT * FROM TestMyView WHERE 1=1' +
Then just looking at one of the CASE statements:
enriquezreyjoseph (9/27/2013)
CASEWHEN @sexID <> 0
THEN ' AND sexID = ' + convert(varchar(20), @sexID)
ELSE ''
END +
You should be able to change this to:
CASE
WHEN @sexID <> 0
THEN ' AND sexID = @Sexid'
ELSE ''
END +
Thus allowing you to pass @SexID into sp_executesql and avoid the SQL injection issue GilaMonster (Gail Shaw) chastised you about in another thread.
What should be the syntax in passing to sp_executesql dwain??.... 🙁
September 27, 2013 at 2:29 am
enriquezreyjoseph (9/27/2013)
EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname' -- one for each of your filters,@sexID=@sexID
,@statusID=@statusID
,@firstname=@firstname
,@middlename=@middlename
,@lastname=@lastname
END
You already had it in your prior post.
You probably should read BOL's examples on this subject:
http://technet.microsoft.com/en-us/library/ms188001.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply