| Author |
 Topic  |
|
|
nic
Posting Yak Master
209 Posts |
 Posted - 03/31/2004 : 12:18:03
|
Hi,
Is there anyway you can pass in a comma delimited string into a sproc and use it directly in a sql statement (without dynamic sql)? I want to return a list of states but also have the ability to limit the result set. I can do this via dynamic sql but I was curious if there was a better way.
declare
@sql1 varchar(50)
,@sql2 varchar(50)
-- I'm trying to mimick this
select * from states where state in ('WA','AL','UT')
-- single value works fine
set @sql1 = 'WA'
select * from states where state in (@sql1)
-- this doesn't work as expected (doesn't treat them as seperate values)
set @sql2 = '''WA'',''AL'',''UT'''
select * from states where state in (@sql2)
The sproc needs to be able to recieve more than one "limiting" value (an array of some sort)
Any ideas?
Nic |
|
|
jsmith8858
Dr. Cross Join
USA
7402 Posts |
|
|
nr
SQL Server MVP & SQLTeam MVY
United Kingdom
12531 Posts |
Posted - 03/31/2004 : 12:48:55
|
If you have a small table to search (whaich sounds like the case here) then
create proc ..
@csv varchar(1000) ,
@delim varchar(10)
as
select *
from States
where @delim + @csv + @delim like '%' + @delim + state + @delim + '%'
go
this expects @csv to be
'WA,AL,UT'
for a , @delim.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
X002548
Not Just a Number
USA
12559 Posts |
Posted - 03/31/2004 : 16:30:21
|
USE Northwind
GO
CREATE PROC mySPROC99
@csv varchar(1000)
, @delim varchar(10)
AS
BEGIN
SELECT *
FROM Suppliers
WHERE @delim + @csv + @delim like '%' + @delim + region + @delim + '%'
END
GO
EXEC mySproc99 'LA,MI,OR',','
GO
SELECT COUNT(*) FROM Suppliers WHERE Region IN ('LA','MI','OR')
GO
DROP PROC mySproc99
GO
Now to see why that works...very nice
Brett
8-) |
|
|
|
X002548
Not Just a Number
USA
12559 Posts |
Posted - 03/31/2004 : 16:36:12
|
How very clever...I didn't know that would parse
,LA,MI,OR, like %,LA,%
,LA,MI,OR, like %,MI,%
,LA,MI,OR, like %,OR,%
Brett
8-) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 03/31/2004 : 18:20:25
|
I've never seen it done quite like that before. That's just freakin kewl Nigel.
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA. |
|
|
|
nr
SQL Server MVP & SQLTeam MVY
United Kingdom
12531 Posts |
Posted - 04/01/2004 : 02:03:08
|
Only useful for small tables as won't use indexes - for large ones better to create a table and join to it.
(I've posted the method on this site loads of times).
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
USA
12559 Posts |
Posted - 04/01/2004 : 10:13:16
|
quote:
Originally posted by nr
Only useful for small tables as won't use indexes - for large ones better to create a table and join to it.
(I've posted the method on this site loads of times).
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Really?
First time I remeber it.
Why does it parse?
Brett
8-) |
|
|
|
jsmith8858
Dr. Cross Join
USA
7402 Posts |
Posted - 04/01/2004 : 10:41:21
|
why wouldn't it parse? from your post:
,LA,MI,OR, like %,LA,%
,LA,MI,OR, like %,MI,%
,LA,MI,OR, like %,OR,%
all of those evaluate to TRUE, right? so it's a valid LIKE expression, and it's true, so the rows are returned.
And of course indexes cannot be used due to the LIKE '%...' with the leading %.
Nigel has posted this before, and I've used it quite a bit back in my Access days ... it can be really handy, but like he states, only use it for small tables.... and never in place of normalization !
- Jeff |
|
|
|
nr
SQL Server MVP & SQLTeam MVY
United Kingdom
12531 Posts |
Posted - 04/01/2004 : 10:47:23
|
It's
',LA,MI,OR,' like '%,LA,%'
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
USA
12559 Posts |
Posted - 04/01/2004 : 12:36:18
|
quote:
Originally posted by nr
It's
',LA,MI,OR,' like '%,LA,%'
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
True...but Jeff, I don't mean that
I mean why does it accept the variables in the predicate...
Since
USE Northwind
GO
DECLARE @Tablename sysname
SELECT @Tablename = 'Orders'
SELECT *
FROM @Tablename
GO
Doesn't work...
Brett
8-) |
|
|
|
nr
SQL Server MVP & SQLTeam MVY
United Kingdom
12531 Posts |
Posted - 04/01/2004 : 12:55:17
|
That's trying to use a variable to contain the tablename.
The query given uses the column name but just concatenates the value with a string
same as
create table #a (s varchar(10))
insert #a select 'ab'
insert #a select 'ac'
select * from #a where s + 'b' = 'ab'
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
USA
12559 Posts |
Posted - 04/01/2004 : 13:48:18
|
quote:
Originally posted by nr
That's trying to use a variable to contain the tablename.
The query given uses the column name but just concatenates the value with a string
same as
create table #a (s varchar(10))
insert #a select 'ab'
insert #a select 'ac'
select * from #a where s + 'b' = 'ab'
Yeah, makes sense...The predicate is suppose to utilize variables anyway, so why not I guess....
Just very creative....Thanks...
Brett
8-) |
|
|
| |
Topic |
|
avoid dynamic sql?
