[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