SQL Server Performance

Param passed to Stored Proc is failing

Discussion in 'General Developer Questions' started by mkal, Aug 22, 2003.

  1. mkal New Member

    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)) where the string passed should include the single tick marks and commas.


    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


    Where the first <COLUMN_NAME> in the WHERE CLAUSE is varchar(50) and the second <COLUMN_NAME> is varchar(10)

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

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

    Any and all suggestions are welcome.
  2. bambola New Member

    you can change this
    > COLUMN_NAME <>''' + '1' + ''''
    to this
    COLUMN_NAME <> '1'

    Do you have a list of numbers in the @List variable? if so, are they wrapped with quotes?
    it should be IN ('2','3','8','15') and not IN (2, 3, 8, 15)

    Bambola.

  3. mkal New Member

    Thanks, and yes the list will come from user input at the front end and get passed to the proc. The expression should look like: IN ('2','3','8','15') after the data is passed.

    Mike
  4. bambola New Member

    Try to print the call to the sproc and see if there is anything wrong there. Otherwise, instead of calling sp_executesql(sql), just print the string in the stored procedure and try to run it in QA or post it here.

    Bambola.
  5. mkal New Member



    I believe I have the syntax correct my string when I print the @StringSQL looks like this:

    declare @List varchar(8000)
    Set @List = '005512''' + ',' + '''4455550'

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


    Print @StringSQL

    note: you have to stick something in the @list otherwise its null and you can't conacatenate a string value with null so I have just replaced a real value with the param's name (@list).

    The Cold Fusion code that calls the proc and passes the params looks like this:


    <CFQUERY name="RESSET" datasource="Server02" dbtype="ODBC">
    {call sp_ProcName( '#cookie.sd#','#cookie.ed#', ...(n))}
    </CFQUERY>


    Where (n) could be more '#cookie.sd#'

    Hope this helps some and thanks for the reply.
    Mike

  6. bambola New Member

    It should be Set @List = '''005512''' + ',' + '''4455550'''

    What was the output of Print @StringSQL?

    > it throws and error saying it can't convert from a datatype to a datatype.
    Can you be more specific about the datatypes?

    Bambola.
  7. mkal New Member

    I see what your saying but the number of values being returned might vary, how am I to know how many commas I must use (concatenate) in the string or is this something that the Cold Fusion coder should take care of?

    Thanks again,
    Mike



  8. bambola New Member

    What you can do in this case is pass the string without the quotes, parse it in a function that will return a result set, and join it with the other table, or use IN.

    joechang has a nice procedure to split a string. You can find it here
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1057

    Your procedure will look like this

    CREATE PROC getInfo
    @List varchar(8000)
    AS

    SET NOCOUNT ON

    IF ISNULL(@List, '') = ''
    RETURN

    DECLARE @len int
    SELECT @len = LEN(@list)

    SELECT COLUMN_NAMES
    FROM TABLE_NAME
    WHERE COLUMN_NAME IN (SELECT ITEM FROM dbo.split_string(@list, ',', @len))

    OR

    SELECT COLUMN_NAMES
    FROM TABLE_NAME
    INNER JOIN dbo.split_string(@list, ',', @len) a
    ON TABLE_NAME.COLUMN_NAME = a.ITEM

    This way you can avoid the sp_executesql and you don't have to worry about @list if empty.

    Bambola.
  9. mkal New Member

    It's always simple once somebody shows you how.

    Some day I want to be as good as you guys [grin].

    Many mamy thanks,
    Mike

Share This Page