CODEWITHSUNDEEP
c#sqlsql-server-2008
Dave
Dave

Reputation: 1775

Building a SQL stored procedure query with list inputs

How do I pass a list of names to a SQL parameter so I can do WHERE IN (@myList)?

I'm making an "advanced search" for our webpage where the user can list, separated by commas, things they would like to find for several inputs. For example there could be 5 input's (First Name, account number, Last name, ...) and for each of those inputs they can list several and separate them by commas.

My approach so far is to take in this string for each input, and format it so I can use the "IN" clause on that string. Is this possible?

IE the user for "First Name" enters "Bob, Joe,Fred", I would transform that input into 

"'Bob', 'Joe', 'Fred'"

and then send that as a parameter to a stored procedure. 

Declare @firstNames

Select * from Users
where User.FirstName in firstNames

OR should I put all these names into a DataTable in C# and pass that to SQL? If I should go this route, some examples would help a lot!

Thanks!

Upvotes: 0

Views: 1586

Answers (3)

BClaydon
BClaydon

Reputation: 1970

In C# you can add a list of parameters to your SQLCommand.CommandText. Assuming customerName is a string of "'Bob', 'Joe', 'Fred'" you do something like this:

Dim command As New SqlCommand(commandText, connection)
        ' Add FirstName parameter for WHERE clause.
        command.Parameters.Add("@FirstName", SqlDbType.nvarchar)
        command.Parameters("@FirstName").Value = FirstName

In Query Analyzer you can't have a list in a @parameter, which is a nuisance, but you can pass one in from another source; for example your C# calling code. In your WHERE clause you do WHERE IN (@Name). In my testing I create a temp table and do WHERE in (SELECT FirstName FROM #MyCustomerTempTable), then when hooking it up replace the sub-query with the singleton parameter.

Source: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

Another approach (which I used most frequently) to adding parameters to the command is:

' Add the input parameter and set its properties. 
Dim parameter As New SqlParameter()
parameter.ParameterName = "@FirstName"
parameter.SqlDbType = SqlDbType.NVarChar
parameter.Value = firstName

' Add the parameter to the Parameters collection.
command.Parameters.Add(parameter)

Source (scroll down to the example): http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx

You can also dynamically build your query in C# or do a string.replace and replace @parameter with 'my list of names', but neither of these methods are preferred to adding parameter objects to the SQL Command object. I would suggest getting a solid understanding of the SQL Command object so you can build that instead of manipulating strings.

Upvotes: 0

Miller
Miller

Reputation: 1156

Assuming you are using SQL Server, you can create a tabular function like to split comma seperated values into table 

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
--     ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Then you can call the function like this in your SP

Select * from Users where User.FirstName in ( SELECT items FROM [dbo].[Split] (@firstNames, ','))

Upvotes: 1

Saeed Neamati
Saeed Neamati

Reputation: 35842

Whenever my query gets a little complicated like this, I prefer to choose either of these ways:

  1. Create a query dynamically in C#, based on string concatenation for better readability rather than LINQ expression trees, and send it to SQL Server, or
  2. Create a SP, or a Table-Valued Function in SQL Server, and then call it from C#, passing arguments to it.

In case of creating the query in C#, it's not extensible, and every time you want to change the logic, you should recompile your application.

In case of SP, because it's going to be a dynamic query and where clause should be created based on input parameters, I use exec (@query)

Upvotes: 1

Related Questions