Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    60

    Question Unanswered: Store Proc can't pass parameter correctly

    I'm trying to create a proc (SQL 2000) that takes a single input paramter from a front Cold Fusion app.

    The parameter should hold a comma delimited string that should look like this:
    ('00012201', 00055445', '33315511', ...(n))

    so essentially the proc definitiion would look like this:

    CREATE PROC getInfo
    @List varchar(8000)
    AS

    DECLARE @StringSQL nvarchar(4000)
    SET @StringSQL =
    (
    'SELECT <COLUMN_NAMES>
    FROM <TABLE_NAME
    WHERE <COLUMN_NAME IN (''' + @List + ''') AND<COLUMN_NAME <>''' + '1' + ''''
    )

    EXECUTE sp_executesql @StringSQL


    I'm having problems getting the app to pass the list correctly, or it throws and error saying it can't convert from datatype to datatype.

    I realize this is a little vague but I don't have experience with Cold Fusion.

    Any and all suggestions are welcome.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Store Proc can't pass parameter correctly

    It's the quote in your string.

    You should escape the quote by a second one. Try

    SET @Val = '(''0123'', ''113143'', ....)'
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    May 2003
    Posts
    60

    Re: Store Proc can't pass parameter correctly

    My IN experession in the WHERE clause after the string is passed should look like this:

    WHERE column_name IN ('2', '3', '4', '5')

    Somewhere after the user chooses the values to pass the Cold Fusion app passes the string like this '2', '3', '4', '5' but with out the () so I am not creating the string its being passed to the parameter.

    I think the call would look something like this from Cold Fusion

    <CFQUERY name="RESSET" datasource="Server02" dbtype="ODBC">
    {call sp_ProcName (default, '#cookie.sd#','#cookie.ed#', default)}
    </CFQUERY>
    Hope this helps and thanks for the reply
    Mike
    Last edited by mkal; 08-22-03 at 18:06.

Posting Permissions

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