[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL SERVER 2005 Filter

Posted on 2007-08-03
11
Medium Priority
?
187 Views
Last Modified: 2010-03-19
I see that things has changed in SQL Server 2005, maybe I am going to need to learn XML, I am not sure, but will do whatever it takes.

Let me simplify my question
Lets say I have a State, Car, City Table and have 5 records Like this
TX Ford Houston
TX Chevy Dallas
LA Mazda NewOrleans
GA Ford Atlanta
NY Ford NewYork

On Visual Studio, I have a distinct State Checkboxes and distinct car checkboxes seperate for each.
And the user only want to see the cities in (TX and NY) and (Fords and Mazda)
So if we apply the filters the result is
TX Ford         Houston
NY Ford        NewYork

So I need to pass parameters (TX,NY) AND (FORD, MAZDA) to my Stored Procedure somehow.
I currently have (TX, NY) in an ArrayList (500) and Ford, Mazda in another ArrayList(500) in VB.NET.
SQL side needs to be dynamic in size because I have many things as you see.
I am using VS2005 and SQL Server 2005
Thanks bunch for help.
0
Comment
Question by:Midnight2005
  • 5
  • 5
11 Comments
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19628736
The dirty solution would be: join your arrays, creating comma-delimited lists "TX,NY" and "FORD,"MAZDA"; pass them to the proc.
Then you could do
SELECT * FROM MyTable WHERE State IN (@stateList) AND Car IN (@carList)
declare the @stateList and @carList parameters as NVARCHAR(4000) so you should be good until they are greater than 4000 characters long.
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19628759
The clean solution would be to pass XML with all the states and cars and parse it in the SP, but I'd try the dirty solution first :)
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 19628766
Interesting, I tired that, this is exactly how I tired to do, passing them in a comma delimited string.
In this situation, I think I can say I passed in to two different varchar, and I tired to use the "IN" command, I thought the reason why I got the error was because SQL Server 200 doesn't like IN anymore.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Midnight2005
ID: 19628772
The best is maybe to create this simple database in SQL only and see it can be worked with this 5 records...You sound like you are sure it would work...
As long as it works...:)
0
 
LVL 4

Accepted Solution

by:
bamboo7431 earned 1040 total points
ID: 19628881
You learn something every day. Apparently dynamic IN is not allowed!

Here's a good article I found: http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings
Try to use his iter_charlist_to_tbl function
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19628907
Just for fun, here's my script with dynamic SQL - NOT something I'd like to put on a production system...

DROP TABLE #Test
CREATE TABLE #Test(State CHAR(2), Car VARCHAR(10), City VARCHAR(20))
SET NOCOUNT ON
INSERT INTO #Test VALUES ('TX', 'Ford', 'Houston')
INSERT INTO #Test VALUES ('TX', 'Chevy', 'Dallas')
INSERT INTO #Test VALUES ('LA', 'Mazda', 'New Orleans')
INSERT INTO #Test VALUES ('GA', 'Ford', 'Atlanta')
INSERT INTO #Test VALUES ('NY', 'Ford', 'New York')
DROP PROCEDURE getRecords
GO
CREATE PROCEDURE getRecords(@carList VARCHAR(2000), @stateList VARCHAR(2000))
AS BEGIN
DECLARE @dynamicSQL NVARCHAR(4000)
      SET @dynamicSQL = 'SELECT * FROM #Test WHERE State IN(''' +
      + Replace(@stateList, ',', ''',''') + ''') AND Car IN(''' +
      + Replace(@carList, ',', ''',''') + ''')'
      --PRINT @dynamicSQL
      EXECUTE sp_executesql @dynamicSQL
END
GO
EXEC dbo.getRecords 'Ford,Mazda', 'TX,NY'
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 19630022
Thanks for shraing your script, I also see what you did. I will try at my computer tomorrow..Currently, I am going to wait for other people's inputs, too, in case anybody would like to add.
What would be the best way to solve this?
I am going to put this on our production server, we have some speed problem, too so I'd like to get it in the right way.
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 19631988
Bamboo,
Did you mean you wouldn't put Dynamic SQL solution in production server or you meant you wouldn't put this temporary test (script) at the production server?
If the best way is XML, could you maybe give me a small sample, so that we would done with this? I need to implement something soon.
0
 
LVL 1

Author Comment

by:Midnight2005
ID: 19634657
I see why you said it, dynamic Sql's are slower compare to static ones.
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19635134
I wouldn't do this crazy string manipulation coupled with dynamic SQL on a production server. Use the link I gave above - he has a good article and a nice function for you to use.
0
 
LVL 3

Assisted Solution

by:Minna
Minna earned 960 total points
ID: 19642738
The best solution is to use to write a function that returns an array table.  Below is code you can use for your function.
ALTER FUNCTION [dbo].[udf_VarcharArray]
(-- Add the parameters for the function here
    @arrayList varchar(8000)
)
RETURNS @Array TABLE
(
  [Value] varchar(100) NOT NULL
)
AS
BEGIN
   -- Declare the return variable here
   IF (@arrayList IS NOT NULL)
   BEGIN
      DECLARE
         @index smallint,
         @arrayValue varchar(100)

      WHILE @arrayList <> ''
      BEGIN
         SELECT @index = CHARINDEX(',', @arrayList)

         IF @index > 0
            SELECT
               @arrayValue = LEFT(@arrayList, @index - 1) ,
               @arrayList = RIGHT(@arrayList, LEN(@arrayList) - @index)
         ELSE
            SELECT
               @arrayValue = @arrayList ,
               @arrayList = ''
   
         -- insert into table
         INSERT INTO @Array
         SELECT LTRIM(RTRIM(@arrayValue))
      END
   END
RETURN;
END

Then you can create your stored procedure that passes the string (TX, NY), (Ford, Mazda) similar to
CREATE PROCEDURE storedProc
(
  @State varchar(4000),
  @Car varchar(4000))
)
AS
BEGIN
     SELECT DISTINCT
     *
   FROM
     [Table]
   WHERE
     (State IN (SELECT Value FROM dbo.udf_VarcharArray( @State ))
     AND
     (Car IN (SELECT Value FROM dbo.udf_VarcharArray( @Car ))
END

Much quicker, cleaner and more maintenance friendly and no use of dynamic stored procs.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question