Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Can You make this code Shorter??.. Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 8:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191

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



Post #1499149
Posted Thursday, September 26, 2013 10:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499166
Posted Thursday, September 26, 2013 10:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499169
Posted Friday, September 27, 2013 1:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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 ...
Post #1499217
Posted Friday, September 27, 2013 1:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499225
Posted Friday, September 27, 2013 1:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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...
Post #1499226
Posted Friday, September 27, 2013 1:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499229
Posted Friday, September 27, 2013 1:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Thanks Dwain...so should i use CASE Statement instead??..
Post #1499231
Posted Friday, September 27, 2013 1:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
That is my recommendation.

I find the resulting code to be a bit easier to understand.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1499235
Posted Friday, September 27, 2013 2:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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...






















Post #1499242
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse