SQL Server: Parameterize IN clause

1143015 pts.
Tags:
SQL Server
Stored Procedures
XML
Is there a way to parameterize an SQL query that contains an IN clause with a variable number of arguments? Something along the lines of this:
select * from Tags 
where Name in ('ruby','rails','scruffy','rubyonrails')
order by Count desc
I don't want to use a stored procedure/XML so what's the best way to go about this?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

What is unsatisfactory with the statement as you have written it?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    If running in a .NET app, have you tried building a string for the SQL statement? Something like this:

    strSQL = "SELECT * FROM TAGS WHERE name in ( " & "'" & strMYPARM.ToString & "'" & ")""
    132,840 pointsBadges:
    report
  • TheRealRaven
    There is no "best" way.

    In programming, there's almost never a "best" way. Different ways may be better for different environments; and without knowing environment/requirements, there's no way we can know what fits well (and not necessarily "best"). Much more needs to be included in the problem statement (your question).

    In your case, there really isn't even a "best" that's reasonably possible. It's closer to "only" than to "best" if you want to use a list of values in the statement. And it's not even "good".

    You'll need to use a dynamic SQL statement. Pass the string of IN() parameters in, PREPARE the statement, and EXECUTE it.

    One potential alternative is to place the values in a separate table. Then use a sub-SELECT.
    35,130 pointsBadges:
    report
  • danieljones
    In ColdFusion

    <cfset myvalues = "ruby|rails|scruffy|rubyonrails">
        <cfquery name="q">
            select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
        </cfquery>
    2,840 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: