Can You make this code Shorter??..

  • 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

  • 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![/I]

    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

  • 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![/I]

    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

  • 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 ...

  • 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![/I]

    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

  • 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...

  • 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![/I]

    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

  • Thanks Dwain...so should i use CASE Statement instead??..

  • 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![/I]

    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

  • 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...

  • this is it

  • 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)


    CASE

    WHEN @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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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)


    CASE

    WHEN @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??.... 🙁

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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