Using Microsoft Query and ODBC to SQL Server, comp

Question

I have a view in SQL Server that is somewhat similar to the following example.

         SELECT * 
           FROM PEOPLE
LEFT OUTER JOIN (SELECT ID 
                   FROM OTHER_TABLE 
                  WHERE SOME_FIELD = 'x' 
                     OR SOME_FIELD = 'y' 
                     OR SOME_FIELD = 'z') AS PEOPLE_TO_EXCLUDE ON PEOPLE.ID = PEOPLE_TO_EXCLUDE.ID
          WHERE PEOPLE_TO_EXCLUDE.ID IS null

The hassle:

I am perfectly capable of adding and modifying "OR SOME_FIELD = 'w'" countless numbers of times. However, I am making this view for a user to pull up in excel via ODBC. The user needs to be able to modify the inner select to her liking, to match whatever she happens to be limiting on at that time of the day/week/month/year/etc. I need to make this in a way that allows her to easily limit on SOME_FIELD.

Does anyone have suggestions on how to accomplish this? Ideally I could give her a view, which she could put a comma separated list of values that SOME_FIELD cannot be. Since people may have multiple rows in OTHER_TABLE I can't just have her limit off of that table specifically. For example someone may have SOME_FIELD = 'x' but also have a row in the table where SOME_FIELD = 's'. This person should be excluded because they have 'x' even though they also have 's'. So that is why the inner select is necessary.

Thanks for your help.

Answer 1

Don't create queries for EXCEL users, they always break them and then you have to debug them. Instead, create a stored procedure, pass in a CSV. In the stored procedure split the CSV using a split function and join to it. The user will only have an EXCEL query like:

EXEC YourProcedure 'x,y,z'

As a result, they will not break the query.

To help with the split function, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO 

You can now easily split a CSV string into a table and join on it:

Create Procedure YourProcedure
@Filter VARCHAR(1000)
AS
SELECT 
    p.* 
    FROM PEOPLE  p
        LEFT OUTER JOIN (SELECT 
                             o.ID 
                             FROM OTHER_TABLE o
                                 INNER JOIN (SELECT 
                                                 ListValue 
                                                 FROM dbo.FN_ListToTable(',',@Filter )
                                            ) f ON o.SOME_FIELD=f.ListValue
                        ) x ON p.ID=x.ID
    WHERE x.ID IS null
GO