SQL Server Performance

passing a comma delimited list to an SP

Discussion in 'T-SQL Performance Tuning for Developers' started by peterswan, Dec 17, 2003.

  1. peterswan New Member

    Hello,<br /><br />I'm trying to pass a comma delimited list from a ColdFusion page to a stored procedure. <br /><br />I'm running the exec like this:<br /><br />exec get_Dealer_Search_PRI_Detail '#StartDate#', '#EndDate#', #ID#, '#IDType#', #OID#, '#cBrandID#'<br /><br />where #cBrandID# is a comma delimited list.<br /><br />I'm declaring my variable like this (along with the others):<br /><br />(@cBrandID varchar(50))<br /><br />and using the variable in the SQL like this:<br /><br />AND prf.Brand_id_int in (@cBrandID)<br /><br />but I get an error saying I can't convert a varchar to an int. <br /><br />Is there any way to do this?<br /><br />Thanks, [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Peter
  2. Luis Martin Moderator

    Check Convert funtion in BOL.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. peterswan New Member

    Didn't work. I tried:<br /><br />AND prf.Brand_id_int in (convert (int,@cBrandID))<br /><br />but still got this error:<br /><br />Syntax error converting the varchar value '1,2' to a column of data type int.<br /><br />The reason this is happening is because it's not a single digit that I'm trying to load into the parentheses, it's a comma delimited list.<br /><br />Any other ideas?<br /><br />Thanks for your help, [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Peter
  4. Luis Martin Moderator

    Would you give an example of what do you expect prf.Brand_id_int have?

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. gaurav_bindlish New Member

    Write a function to split the Comma Saperated List into table of values. Write Code like this...

    AND prf.Brand_id_int in dbo.Fn_SplitCommaSaperatedString(@cBrandID)

    HTH.


    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  6. peterswan New Member

    Hi Luis,<br /><br />Thanks for your response. @cBrandID will be any combinations of the numbers 1-5, in single quotes. So it could be just a '1' or a '2', but it also could be '1,2' or '2,5' or '1,2,3,4' etc. etc.<br /><br />I just tried converting the database column to a varchar like this:<br /><br />AND convert(varchar(50),prf.Brand_id_int) in (@cBrandID)<br /><br />and I got no error, but I got no results.<br /><br />Please help, [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Peter
  7. peterswan New Member

    Hi Gaurav,

    You might have to work with me a bit here, I'm a bit of a neo at T-SQL. What is the code to get the list into the table?

    Thanks,

    Peter

  8. gaurav_bindlish New Member

    I don't have the exact code but I cn give u some directions.

    See syntax for creating functions, and then see explaination of CHARINDEX, LEFT, RIGHT. This should give you a fair idea of writing a function to split the comma saperated value and return as a table.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. gaurav_bindlish New Member

    Another option is to create dynamic SQL like

    ...
    ...
    SELECT @Select_Statement = @Select_Statement + "AND prf.Brand_id_int in (" + @cBrandID + ")"
    ....
    ....
    SP_EXECUTESQL(@Select_Statement)

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. FrankKalis Moderator

  11. peterswan New Member

    All is good. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] I solved it by inserting the list into a table and joining the prf table onto this temp table. <br /><br />Here is the code I used to create the table:<br /><br />DECLARE @pos int<br />CREATE TABLE #list (val varchar(10))<br />SET @cBrandID = @cBrandID + ','<br />WHILE CHARINDEX(',', @cBrandID) &gt; 0<br />BEGIN<br /> SET @pos = CHARINDEX(',', @cBrandID)<br /> INSERT #list VALUES (LTRIM(RTRIM(LEFT(@cBrandID, @pos - 1))))<br /> SET @cBrandID = STUFF(@cBrandID, 1, @pos, '')<br />END<br /><br />And here's the code for the join.<br /><br />join #list li (nolock) on li.val = prf.brand_id_int<br /><br />Bascally what this amounts to is I can declare @cBrandID as a varchar, insert each value between commas into a table, and then join on this table.<br /><br />Thanks to gaurav_bindlish and FrankKalis for their help. The pages Frank sent over were especially useful. Why didn't those pages appear on yahoo! when I did a search on them? It doesn't matter. <br /><br />Thanks again,<br /><br />Peter Swanson
  12. bambola New Member

  13. FrankKalis Moderator

  14. brimba New Member

    You could do it like this:<br /><br /><pre>WHERE CHARINDEX(',' + CAST(prf.Brand_id_int as varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />) + ',',',' + case @cBrandID when '' then CAST(prf.Brand_id_int as varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />) else @cBrandID end + ',')&gt;0</pre><br /><br />If you send an empty list it will return all posts...<br /><br />Good luck!

Share This Page