Post reply

need help with dynamic SQL

  • June 22, 2006 at 12:13 pm

    #190968

    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

  • June 22, 2006 at 12:21 pm

    #645344

    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"

  • June 22, 2006 at 1:04 pm

    #645357

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

  • June 22, 2006 at 8:04 pm

    #645425

    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)

    -

  • June 22, 2006 at 8:25 pm

    #645427

    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)
    Intro to Tally Tables and Functions

  • June 23, 2006 at 6:34 am

    #645511

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

  • June 23, 2006 at 7:02 am

    #645516

    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)
    Intro to Tally Tables and Functions

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

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