need help with dynamic SQL

  • the sp below works fine, the thing is I need a little help to add one more piece, I'd like to add something at the end which is

    and [Platform] = 'PDA'

    I've tried all sorts of combinations but still can't get it to work, in deperation I'm turning to you guys for help

    CREATE PROCEDURE spSearch_For_Software @sTitle as VarChar(200),@sField as varchar(200)

    AS

    set nocount on

    declare @sSQL as varchar(200)

    set @sSQL = 'select * from [Club CDs] where [' + '' + @sfield + '' + '] like ' + '''' + @sTitle + ''''

    exec (@sSQL)

    GO

  • CREATE PROCEDURE spSearch_For_Software @sTitle as VarChar(200),@sField as varchar(200)

    AS

    set nocount on

    declare @sSQL as varchar(200)

    set @sSQL = 'select * from [Club CDs] where [' + '' + @sfield + '' + '] like ' + '''' + @sTitle + ''' AND and [Platform] = ''PDA'''

    exec (@sSQL)

    GO


    N 56°04'39.16"
    E 12°55'05.25"

  • thanks for the quick reply Peter, unfortunatly it didn't work

  • it's the extra And "+ ''' AND and [Platform] = ''PDA''' "

    change it to + ''' and [Platform] = ''PDA'''

    also is PDA always the filter if not do this:

    CREATE PROCEDURE spSearch_For_Software @sTitle as VarChar(200),@sField as varchar(200),@sPlatform Varchar(200)

    AS

    set nocount on

    declare @sSQL as varchar(200)

    set @sSQL = 'select * from [Club CDs] where [' + '' + @sfield + '' + '] like ' + '''' + @sTitle + ''' and [Platform] = '''+@sPlatform+''''

    exec (@sSQL)

    -

  • Peter used two "AND"s just due to a cut'n'paste error... remove one of the "AND"s and it'll probably work just fine.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks people, the sp that Jason posted was exactly what I needed. What would people like me do without people like you. Thanks again

  • Sorry for my obvious post, Jason... I pulled to reply to Mick's post and got distracted... in that time, you answered and I didn't see it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply