Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    15

    Unanswered: What's wrong with this stored proc

    Can somebody please debug what is wrong with this stored proc.
    When I execute this procedure I get an error
    Invalid column name 'abcd'.


    CREATE PROCEDURE SearchTest

    @LName varchar (25) = 'abcd',
    @ID varchar(9) = null

    AS

    Declare @query varchar(100)
    Set @query = 'Select * From Document '

    IF ( @LName IS NOT NULL)
    set @query = @query + ' Where LName = ' + @LName

    exec (@query)

    GO

    Thanks

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    Hi,

    you need to enclose abcd in quotes to be taken as a string, right now your @query is

    Select * From Document Where LName = abcd

    instead of

    Select * From Document Where LName = 'abcd'

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think the quotes are not present in your final query ie you execute :
    Code:
    Select * From Document Where LName = abcd
    Instead of :
    Code:
    Select * From Document Where LName = 'abcd'
    In the first case, abcd is considered to be a column of the Document table, whereas you want to compare to the static string 'abcd'. Unfortunately I don't know what character to use in order to say that ' is in the string.

    Try

    @LName varchar (25) = '\'abcd\''

    (all simple quotes, no double quote). Or

    @LName varchar (25) = '''abcd'''

    (three simple quotes).


    Please let me know if one or both work. I know much more about Oracle than SQL Server, but I'm interested in how the latter works.

    HTH & Regards,

    RBARAER

  4. #4
    Join Date
    Jul 2004
    Posts
    15
    Thanks for the prompt response.

    I was able to run the query with triple single quotation marks

    \' doesnot work, I did try that before

    Thanks again

  5. #5
    Join Date
    Mar 2005
    Location
    Philippines, Makati
    Posts
    11
    itrap2003,

    your query should look like this.

    set @query = @query + ' Where LName = ''' + @LName + ''''

    Regards,
    K3n

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •