Dynamic parameters for sql query with "IN" operator

Question

I have an ASP.NET website (c#) and in the code-behind I want to use the IN operator in SQL http://www.w3schools.com/sql/sql_in.asp to get data from my database.

The syntax is:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

I use a stored procedure to get data, inside this procedure I run, in the end, the select.

My question is how to build dynamically the (value1, value2, ...) part of the query? How do I send as SqlParameter(s) the values that appear in the IN operator?

The stored procedure is defined like this:

CREATE PROCEDURE [dbo].[GetAllUsers]
    @ORGANIZATION_ID int = null
AS
BEGIN
    SET NOCOUNT ON;

    begin
       SELECT * 
       from USERS
       where ORGANIZATION_ID = @ORGANIZATION_ID
    end
END

I'd like to replace the WHERE clause with something like:

WHERE ORGANIZATION in (xxxxxxxxxxxxx)

How to do this?

Thank you.

what DBMS (SQLServer/MySQL/Oracle) are you using?

Vikdor
– Vikdor

2012-10-24 11:50:41 +00:00
Commented Oct 24, 2012 at 11:50 — Vikdor
– Vikdor, Commented Oct 24, 2012 at 11:50

Robert · Accepted Answer · 2012-10-24 12:12:32Z

1

You can use dynamic sql:

CREATE PROCEDURE [dbo].[GetAllUsers]
    @ORGANIZATION_ID varchar(max) = null
AS
BEGIN
    declare @sql varchar(max)
    @sql = 'SELECT * 
            from USERS
            where ORGANIZATION_ID in ('+@ORGANIZATION_ID+')' 
    SET NOCOUNT ON;
        begin
          exec(@sql)  
        end
END

You have to change type of @ORGANIZATION_ID to varchar and run the procedure with

one id:

exec '1'

or list of ids:

exec '1,2,3,4'

edited Oct 24, 2012 at 12:12

answered Oct 24, 2012 at 11:58

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zelter Ady Over a year ago

Thank you. Should I use ORGANIZATION_ID varchar(max) or ORGANIZATION_ID varchar(10000).

Sklivvz Over a year ago

It's bad practice to do so, however

Robert Over a year ago

@Sklivvz Can you tell me, what is better practice? I will consider it, then I will add new solution.

Collectives™ on Stack Overflow

Dynamic parameters for sql query with "IN" operator

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related