avoid dynamic sql? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

avoid dynamic sql?

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2004-03-31 : 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

7423 Posts

Posted - 2004-03-31 : 12:21:50

check it out:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-31 : 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

15586 Posts

Posted - 2004-03-31 : 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

15586 Posts

Posted - 2004-03-31 : 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

4184 Posts

Posted - 2004-03-31 : 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
SQLTeam MVY

12543 Posts

Posted - 2004-04-01 : 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

15586 Posts

Posted - 2004-04-01 : 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

7423 Posts

Posted - 2004-04-01 : 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
SQLTeam MVY

12543 Posts

Posted - 2004-04-01 : 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

15586 Posts

Posted - 2004-04-01 : 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

[CODE]
USE Northwind
GO

DECLARE @Tablename sysname
SELECT @Tablename = 'Orders'
SELECT *
FROM @Tablename
GO
[/CODE]

Doesn't work...

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-01 : 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

15586 Posts

Posted - 2004-04-01 : 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-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources