Solved

Create a Loop Over a Set of String Constants

Posted on 2014-10-21
14
123 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
ID: 40396029
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)
ID: 40396041
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
ID: 40396349
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 0 total points
ID: 40397790
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)
ID: 40397802
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
ID: 40397832
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)
ID: 40397889
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40398351
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)
ID: 40398363
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
ID: 40398397
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
ID: 40398412
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)
ID: 40398688
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
ID: 40400679
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)
ID: 40405944
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

828 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