One of the most useful T-SQL features is the keyword "IN".  THis keyword allows the user to filter the selected data against a list of comma separated data. For example, using the Northwind sample database:

    select * from Customers where Country IN ('Canada', 'Mexico')
   
However, this is rather limited syntax as the parameter list is static.  T-SQL does not support variable substitution with the IN keyword so the following will NOT work

    declare @CList varchar(100)
  set @Clist = 'Canada, Mexico'
  select * from Customers where Country IN (@Clist)  -- will fail!
   
Is there another way?  Yes.  The IN keyword will accept a sub-select. So we could use a temporary table and a sub-select to implement something like this:

    declare @Ctable table ( Cname varchar(100) )
    Insert @Ctable Values ( 'Canada')
    Insert @Ctable Values ( 'Mexico')
    select * from Customers where Country IN
        ( Select Cname from @Ctable )

This works, but still is a bit cumbersome to implement. However, this example does not have the same flexiblity of a comma-delimited list.

So how can this functionality be implemented?  Use a User Defined Function (UDF) to return a table of paramters!

This is not a new concept. I recently came across this article (http://www.4guysfromrolla.com/webtech/031004-1.shtml) posted in 2004.  In the article, the author (Corey Aldebol) defines a function capable of taking a comma-delimited parameter list and returning a table of paramters.  Here is the Function

    CREATE FUNCTION dbo.Split
    (
      @List nvarchar(2000),
      @SplitOn nvarchar(5)
    ) 
    RETURNS @RtnValue table
    (
      Id int identity(1,1),
      Value nvarchar(100)
    )
    AS 
    BEGIN
      While (Charindex(@SplitOn,@List)>0)
        Begin
        Insert Into @RtnValue (value)
        Select
          Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
          Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
        End
        Insert Into @RtnValue (Value)
        Select Value = ltrim(rtrim(@List))

        Return
    END

Using this function, we can then change the failed example into the follow:

    declare @CList varchar(100)
    set @Clist = 'Canada, Mexico'
    select * from Customers where Country in
        (Select Value from dbo.Split(@Clist,','))

or even simpler

    select * from Customers where Country in
      (Select Value from dbo.Split('Canada, Mexico',','))

So how can this be used by SGSV (SQLGridSelectedView)?  IF you implement the User Defined Function, then the function can be used by SGSV using a Text Search parameter filter. 

  
which results in

You can test the function at www.tressleworks.ca/samples

Hope this helps
Paul.
   

Update: MarkXA has provided an alternative solution for the Split function. I called it Split2.  This version requires SQL2005 or better.

CREATE FUNCTION dbo.Split2 (
  @List nvarchar(max), @SplitOn nvarchar
)
RETURNS TABLE
AS
RETURN (
   WITH Split AS (
      SELECT CONVERT(nvarchar(max), NULL) AS Value, @List AS List
      UNION ALL
      SELECT
                CASE WHEN CHARINDEX(',', List) = 0 THEN List 
             ELSE LEFT(List, CHARINDEX(',', List) - 1) END,
        CASE WHEN CHARINDEX(',', List) = 0 THEN ''
             ELSE RIGHT(List, LEN(List) - CHARINDEX(',', List)) END
      FROM Split
      WHERE List <> ''
   )
   SELECT LTRIM(RTRIM(Value)) as [Value]
   FROM Split
   WHERE Value IS NOT NULL
)
 

Thanks Mark.