Avoidance of sp_executesql()

All,
No flames please, just a question that I haven't been able to figure out yet
.
Is there an efficient way to do something like this:
Select A, B From luTable Where A IN (@.AList)
WITH OUT having to resort to the nefarious level of setting role permissions
on luTable (and using sp_executesql and it's merry band)? We use
SQLAuthentication and roles and don't want to have sp_executesql, etc. use a
specific account or role (don't ask...).
I've tried various boondoggles like:
Declare @.AList as varchara(20)
Set @.AList = '1,2,3'
Select A, B From luTable Where A IN (Select @.AList)
and other even more unpleasant concoctions, but, woe to behold, no luck.
Reason I'm asking is I'm trying to wean programmers from using sp_executesql
et. al. and avoid putting select permissions directly on user tables, but
they always throw the "we need to select multiple values using an IN clause
and the users can select multiple values from the select list..." in my
face... and, as of yet, there is no joy in Mudville.
Thankshttp://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Check out:
http://www.sommarskog.se/arrays-in-sql.html
HTH
Jerry
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:1B3FC4E2-9B80-42AE-86DF-C9047E855940@.microsoft.com...
> All,
> No flames please, just a question that I haven't been able to figure out
> yet.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role
> permissions
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
> a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using
> sp_executesql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN
> clause
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||yes you can
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
create table #temp (varcharField varchar(50))
--String manipulation with a pivot table
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='abc,fgt,ddd,ghj'
insert into #temp
SELECT SUBSTRING(',' + @.chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @.chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @.chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @.chvGroupNumbers + ',', NumberID, 1) = ','
Select A, B From luTable l join #temp t on l.A =t.varcharField
more here http://sqlservercode.blogspot.com
"B@.DJJ" wrote:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||Thanks! I had a feeling I was looking under the wrong tree...!
"B@.DJJ" wrote:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||Am Tue, 11 Oct 2005 11:40:05 -0700 schrieb B@.DJJ:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
maybe something in this way:
declare @.testtable table (a varchar(10), b varchar(10))
insert into @.testtable values('1','aaa')
insert into @.testtable values('11','ccc')
insert into @.testtable values('12','ddd')
insert into @.testtable values('23','eee')
insert into @.testtable values('3','fff')
Declare @.AList as varchar(20)
Set @.AList = ',1,2,3,'
Select A, B From @.testTable Where charindex(','+A+',',@.AList) > 0
For this purpose "we need to select multiple values using an IN clause and
the users can select multiple values from the select list..." this should
work if you have separators not included in the normal values [for example
char(255)?]
bye,
Helmut