Solved

Create a Loop Over a Set of String Constants

Posted on 2014-10-21
14
113 Views
Last Modified: 2014-10-27
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
0
Comment
Question by:zorvek (Kevin Jones)
  • 7
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 0 total points
Comment Utility
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 81

Author Closing Comment

by:zorvek (Kevin Jones)
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now