1

What is the best approach? I have a C# .NET Web Forms website which gathers a list of user profiles based on one or more tags. The site connects to a SQL database and uses Stored Procedures for most data retrieval.

My first (half-retarded) thought would be to build a list of parameters: tag1, tag2, tagN. Pass that list to a SP. In the SP, build the WHERE clause by looping through the parameters.

Before i can even do the WHERE loop, how would I build my parameters in SQL?

It'd be nice if I could just pass the SP an array or list to then bust apart and build my query. However, I don't know how to do this - if I did, I doubt the efficiency would be desirable.

I can use CommandType.Text and spit out a huge query right there in code. That doesn't sound very efficient either. I want to keep the SQL/C# stuff as separated as possible. This site is already a mess and a half..

Do any of you have thoughts on this?

casperOne
  • 73,706
  • 19
  • 184
  • 253
BueKoW
  • 926
  • 5
  • 18
  • 33
  • you could try an IN query (pass in an array of the tags), select * from XX where in ( @array ). That could get messy depending your schema – Prescott Feb 09 '11 at 15:57

1 Answers1

6

Table Valued Parameters would be the ideal solution here (assuming you are on SQL Server 2008). They allow you to send sets of data to SQL Server (in 2008 and above) and then process that data as a set in your stored procs (which means you can actually do joins on the set, use EXISTS perhaps, etc).

If you are not on SQL Server 2008, you have two options, the first being to construct an XML document and then use SQL Server's XML support (2005 and above) to parse the document entered (through a string or XML parameter) into a data set you can perform your joins/filters on.

Or, you can also send a custom format which you could use SQL CLR methods to process (any kind of procedural code like parsing generally more performant in SQL CLR methods; SQL Server is better for set-based operations).

Finally, since you are dynamically generating this query in code, you don't have to pass a set as a parameter, you can always tack on parameters. However, it goes without saying that you shouldn't just call ToString and then attach the parameters; you should create a valid parameter name (not hard to do) append that to the set referenced in the IN clause and then add a parameter to the command with the proper value. This makes the assumption that the number of values referenced by your IN clause doesn't exceed the number of parameters allowed in a command.

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • In case you are going to opt for XML. Understand that you can just build the XML as string (even using concatenation) and then pass the string for an XML parameter in your SP. Just my 2 cents. – The King Feb 09 '11 at 16:04
  • 1
    @The King: It's not clear in all cases. If you are on SQL Server 2008, then you should use Table-Valued Parameters, period. It's the native way for SQL Server to allow set-based input data. On 2005, you can use XML, but there are definite performance implications. If you had a series of integers, for example, a comma-delimited list would be much, much faster than creating an XML document. – casperOne Feb 09 '11 at 16:15
  • I understand, and my starting words are "In case you are going to opt for XML" – The King Feb 10 '11 at 01:53