Create a Loop Over a Set of String Constants

I need T-SQL to do the following:

FOR EACH @Parameter in ARRAY('A', 'B', 'C')
    SELECT
        ...
        FROM Table
        WHERE
            Parameter  = @Parameter
NEXT @Parameter

Kevin
LVL 81
zorvek (Kevin Jones)ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
While you can do that with T-SQL, there are better ways of skinning that cat.  So if you are interested, post some more details such as front-end language, Stored Procedure code, etc.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
I have a table with five columns:

ID      bigint
ServiceGroupNumber      int
Commodity      varchar(50)
FilterName      varchar(50)
Value      varchar(50)

In a stored procedure I need to generate multiple recordsets when each recordset is a specific FilterName value.

What I have is:

      SELECT
            DISTINCT
                  Value
            FROM FilterValues
            WHERE
                  (
                        @ServiceGroupNumber IS NULL
                              OR
                        @ServiceGroupNumber = ServiceGroupNumber
                  )
                        AND
                  (
                        @Commodities IS NULL
                              OR
                        '|' + @Commodities + '|' LIKE '%|' + Commodity + '|%'
                  )
                        AND
                  FilterName = 'A'
            ORDER BY
                  Value

      SELECT
            DISTINCT
                  Value
            FROM FilterValues
            WHERE
                  (
                        @ServiceGroupNumber IS NULL
                              OR
                        @ServiceGroupNumber = ServiceGroupNumber
                  )
                        AND
                  (
                        @Commodities IS NULL
                              OR
                        '|' + @Commodities + '|' LIKE '%|' + Commodity + '|%'
                  )
                        AND
                  FilterName = 'B'
            ORDER BY
                  Value

etc.

Note that the only difference between each SELECT is the FilterName value. I have about 15 FilterName values for which I want to create recordsets.

A loop is more compact and maintainable than what I have.

What I have created since asking the question:

      DECLARE @FilterNames TABLE(FilterNameIndex int IDENTITY(1,1), FilterName varchar(50))

      INSERT INTO @FilterNames (FilterName)
            SELECT 'A' UNION
            SELECT 'B' UNION
            SELECT 'C'

      DECLARE @FilterNameIndex int
      DECLARE @Count int
      DECLARE @FilterName varchar(50)

      SELECT @FilterNameIndex = MIN(FilterNameIndex) - 1, @Count = max(FilterNameIndex) from @FilterNames

      WHILE @FilterNameIndex < @Count
      BEGIN
            SELECT @FilterNameIndex = @FilterNameIndex + 1
            SET @FilterName = (SELECT FilterName FROM @FilterNames WHERE FilterNameIndex = @FilterNameIndex)
            SELECT
                  DISTINCT
                        Value
                  FROM FilterValues
                  WHERE
                        (
                              @ServiceGroupNumber IS NULL
                                    OR
                              @ServiceGroupNumber = ServiceGroupNumber
                        )
                              AND
                        (
                              @Commodities IS NULL
                                    OR
                              '|' + @Commodities + '|' LIKE '%|' + Commodity + '|%'
                        )
                              AND
                        FilterName = @FilterName
                  ORDER BY
                        Value
      END

It works. Do you have a simpler alternative?

Kevin
0
SharathData EngineerCommented:
Why do you want to loop it? You can simply apply the condition in WHERE clause.
SELECT
                  DISTINCT
                        Value
                  FROM FilterValues
                  WHERE
                        (
                              @ServiceGroupNumber IS NULL
                                    OR
                              @ServiceGroupNumber = ServiceGroupNumber
                        )
                              AND
                        (
                              @Commodities IS NULL
                                    OR
                              '|' + @Commodities + '|' LIKE '%|' + Commodity + '|%'
                        )
                              AND
                        FilterName IN (SELECT FilterName FROM @FilterNames)
                  ORDER BY
                        Value

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zorvek (Kevin Jones)ConsultantAuthor Commented:
I want the results in multiple different recordsets.

Here is my finished solution:

      DECLARE @FilterNames TABLE(FilterNameIndex int IDENTITY(1,1), FilterName varchar(50))

      INSERT INTO @FilterNames (FilterName)
            SELECT 'A' UNION ALL
            SELECT 'B' UNION ALL
            SELECT 'C'

      DECLARE @FilterNameIndex int
      DECLARE @Count int
      DECLARE @FilterName varchar(50)

      SELECT @FilterNameIndex = MIN(FilterNameIndex) FROM @FilterNames
      SELECT @Count = MAX(FilterNameIndex) FROM @FilterNames

      WHILE @FilterNameIndex <= @Count
      BEGIN
            SET @FilterName = (SELECT FilterName FROM @FilterNames WHERE FilterNameIndex = @FilterNameIndex)
            SELECT
                  DISTINCT
                        Value
                  FROM FilterValues
                  WHERE
                        (
                              ...
                        )
                              AND
                        (
                              ...
                        )
                              AND
                        FilterName = @FilterName
                  ORDER BY
                        Value
            SELECT @FilterNameIndex = @FilterNameIndex + 1
      END

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zorvek (Kevin Jones)ConsultantAuthor Commented:
Note that in the above solution I use the ALL option on the UNION operator. This ensures that the inserted values are placed in the table in the order specified and prevents SQL Server from re-ordering the records as it sees fit.

Kevin
0
Anthony PerkinsCommented:
This ensures that the inserted values are placed in the table in the order specified and prevents SQL Server from re-ordering the records as it sees fit.
I am afraid that is not the way the SQL engines operates.  You have no control over how the rows are ordered in the table,  You can only control how they are output by using ORDER BY.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
I am afraid that is not the way the SQL engines operates.  You have no control over how the rows are ordered in the table,  You can only control how they are output by using ORDER BY.

Actually, I do. UNION ALL disables the default DISTINCT SORT functionality of the UNION operator. During the INSERT operation, I assign the incoming records with a sequential unique ID which allows access of the records by that index, thus ensuring the order after the INSERT.

My (limited) tests confirm this.

Kevin
0
Anthony PerkinsCommented:
During the INSERT operation, I assign the incoming records with a sequential unique ID which allows access of the records by that index, thus ensuring the order after the INSERT.
There is no guarantee in SQL Server that this will be the case, nor is there promise of this ever happening in any future version.  Contrary to popular belief this holds true even if you create a CLUSTERED PRIMARY KEY on the IDENTITY column.

If you would rather hear this from someone in the Microsoft Product Group (Connor Cunningham comes to mind) or the MVP group I can certainly ask them.

But if you are satisfied that it works for you that is all that counts.  I am just warning you and more importantly future readers that this is not guaranteed behavior and never has been (at least since v6.0)
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
I'm not depending on the randomness of the SELECT statement. I'm selecting a specific key value--a key value set when the records were inserted. So the question becomes: does the UNION, not the SELECT, collect and transfer the values in the order specified. If it does, and I've proven it does, then the identity that is assigned as the records are inserted is in the sequence I desire and, when I select a specific record using a specific identity key, I will get the desired record.

Your responses seem to assume that I'm using the SELECT to return the values as a recordset and hoping they come out in the same order I inserted them. I agree that that order is not reliable. But that is not what I'm doing. This is my SELECT statement:

SELECT FilterName FROM @FilterNames WHERE FilterNameIndex = @FilterNameIndex

There is no ambiguity as to which record I will get.

Kevin
0
Anthony PerkinsCommented:
So the question becomes: does the UNION, not the SELECT, collect and transfer the values in the order specified.
This has nothing to do with the UNION (that is just an operator) let alone the SELECT statement.

If it does, and I've proven it does
Yes, you are right , you have "proved" it in the very limited case of a few rows for as many times as you have tried it.
Let's put it another way, supposing you inserted 1 row would you also claim that it was stored in order?

the identity that is assigned as the records are inserted is in the sequence I desire and, when I select a specific record using a specific identity key, I will get the desired record.
That is incorrect.  Again there is no guarantee that the rows will be sorted unless an ORDER BY clause is used.

But again, if you are satisfied with the results, then more power to you.  I am merely making sure that any future readers do not take this as gospel truth.  It is not and never has been despite any anecdotal results to the contrary.  No one should rely on this behavior, even if it is true for your limited tries and rows and your specific version of SQL Server.
0
Anthony PerkinsCommented:
This is one article that Conor has written on the subject:
No Seatbelt - Expecting Order without ORDER BY

I realize that this is about using SELECT and your case is an INSERT, the end result is the same.  What I am suggesting that the only way you can guarantee the sort order is by instead of this:
       INSERT INTO @FilterNames (FilterName)
            SELECT 'A' UNION ALL
            SELECT 'B' UNION ALL
            SELECT 'C'

Open in new window

You do this:
INSERT INTO @FilterNames(FilterName)
SELECT  FilterName
FROM    (SELECT 1 SortOrder,
                'A' FilterName
            UNION ALL
            SELECT 2,
                'B'
            UNION ALL
            SELECT 3,
                'C'
        ) A
ORDER BY SortOrder

Open in new window


His last phrase says it all:
If you need order in your query results, put in an ORDER BY.  It's that simple.  Anything else is like riding in a car without a seatbelt.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
I've tried my solution on 2005 and 2012 with 15 random values and I get the same results. When I leave out the ALL modifier the values are inserted in a sorted order and assigned an index that same order. With the ALL modifier the records are inserted in the exact order specified between the UNION operators.

You are confused by my use of the SELECT statement. I am not using the SELECT statement to create the table. Although there are multiple SELECT statement within the INSERT, each selects only one value which is then unioned with the following SELECT result and so on. These unioned results are inserted into the table in the order specified and are assigned a unique sequential index (identity). This index is what is used to pull the values out of the table one by one. Yes, a SELECT statement is used to pull the records but they are pulled one at a time using the index as the condition.

I agree that if I were to SELECT multiple records from the table without an ORDER BY clause then the order cannot be pre-determined. But that is not what I am doing. Why you keep insisting it is what I am doing is a mystery to me.

In this article, you can see that when using the ALL modifier with the UNION operator, SQL Server does not include the DISTINCT SORT step and inserts the records in the order they are specified between the UNION operators:

Title: SQL Server – What exactly are UNION and UNION All?
Link: http://www.codeproject.com/Articles/716224/SQL-Server-What-exactly-are-UNION-and-UNION-All

Another article that discusses the subject and specifically states that the UNION ALL is performed without the DISTINCT SORT step:

Title: SQL SERVER – Introduction and Example of UNION and UNION ALL
Link: http://blog.sqlauthority.com/2008/10/15/sql-server-introduction-and-example-of-union-and-union-all/

Let's put it another way, supposing you inserted 1 row would you also claim that it was stored in order?
Of course not. I inserted 15 rows multiple times on two different versions. The order was maintained in every case. That's a lot closer to a proof than thinking one row is a proof. Really?

That is incorrect.  Again there is no guarantee that the rows will be sorted unless an ORDER BY clause is used.
Again, you are confusing the SELECT and the UNION. The UNION is not a SELECT. The UNION ALL joins multiple values into a list which is inserted into the table in the order specified. I take advantage of this and assign the records an identity as they are inserted which allows me to access them in the same order. The SELECT is never used to return more than one record.

Now, if you tell me that the UNION ALL does not always produce a set in the order specified then we can have a real discussion. Of course I would want evidence that you were correct. I've given evidence that the order is controllable without the use of specific sorting. All you are doing is rehashing what we all know about SELECT results which has nothing to do with my solution and claims.

Kevin
0
Anthony PerkinsCommented:
You are confused by my use of the SELECT statement.
Not at all, as I have stated multiple times.

But there is no point in continuing this discussion it is becoming less and less productive.  

So now I will leave it up to future readers to make their decision as to what is the correct path to take.

Good luck in all your endeavors.
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
One post was a question. Another did not answer my question.

To the two who participated: I've been answering questions (Excel and VB/VBA) for a long, long time and one thing that I've learned is that askers almost always do not have the patience to be second guessed. They are usually on tight time frames with deadlines and people counting on them being productive. They're stuck and just need a quick solution. I'm no exception. To this end I suggest that in the future you not question the asker as much, read their question carefully, and answer it. Once the asker is satisfied and moving forward you can then challenge the asker as to alternative approaches, best practices, etc.

I gave you everything needed to answer the question in the original post: I needed the solution in T-SQL and I needed to loop over a set of string values doing a SELECT for with each one.

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.