[thelist] [SQL] using a char string as a list

darren darren at web-bitch.co.uk
Fri Jun 7 10:33:01 CDT 2002


On Friday, June 7, 2002 at 05:54, Sean G. wrote:

SG> As far as I can tell, the variable is okay.  When I print and cut-n-paste
SG> the value into a query, I get the expected results.  However calling the
SG> variable in the query always returns 0 matches.

you can't just drop the variable into the select like that.  you have
to either build up an sql string and execute it using exec or
sp_executesql:

   DECLARE @SQLString varchar(2000)

   SET @SQLString = 'select
                        count(UnitKey) as blah
                     from
                        vUnitSort
                     where
                        UnitAbbrev in (@UnitList)'

   EXEC(@SQLString)

(i can't remember the exact syntax for sp_executesql at the mo, but
look in bol for a detailed description...it's more efficient if you're
running the same sql string several times with different parameter as
you shouldn't have the sproc recompiling)

or you can chop up your string on a delimiter:

   DECLARE @UnitList varchar(50)
   SET @UnitList = 'AME,RE'

   DECLARE @tblUnits table (Unit varchar(10))
   DECLARE @len int, @currPos int, @prevPos int

   SET @len = len(@UnitList) + 1

   if @len = 1
   begin
      set @ReturnVal = 1
      return
   end
   else
   begin
      set @currPos = 1
      set @prevPos = @currPos

      while @currPos < @len + 1
      begin
         if substring(@UnitList + ',', @currPos, 1) = ','
         begin
            INSERT INTO @tblUnits(Unit)
               SELECT substring(@UnitList, @prevPos, @currPos - @prevPos)

            set @prevPos = @currPos + 1
         end
         set @currPos = @currPos + 1
      end
   end

then do your select taking into account the values in the temp table:

   select
      count(UnitKey) as blah
   from
      vUnitSort as us
   where
      exists (select null from @tblUnits where Unit = us.UnitKey)

or

   select
      count(UnitKey) as blah
   from
      vUnitSort as us
   inner join @tblUnits as tu
      on us.UnitKey = tu.Unit

sql server seems to optimize them to the same thing.

in the case of the exec and sp_executesql you need to make sure that
the user specified in the connection string has permissions to select
from the relevant tables as the execute runs under *their* permissions
not those of the sproc owner.  the second method will run under the
permissions of the sproc owner (usually dbo).  this may be an issue if
you're trying to restrict the access of the users.

hth,

darren




More information about the thelist mailing list