June 21, 2002 at 1:40 pm
Here's part of a stored procedure I'm trying to get working. I need to submit a list of states and have the query run it, but I think it needs to "pre-evaluate" the string. Can anyone help?
Assuming @States='AL','AR','AZ'
SELECT EventID FROM e_EventState WHERE (StateCD IN (@States))
June 22, 2002 at 4:45 am
I've seen this done horribly with dynamic sql.
i.e.
@MyString = 'SELECT EventID FROM e_EventState WHERE (StateCD IN (' + @States + '))'
EXEC (@MyString)
It requires that you grant select on the underlying table to the user/role that is running the proc... so probably best to call it direct from the client rather than going through a stored procedure if that is all the stored proc does.
I'm sure there's a better way, but it's not springing to mind at the moment.
June 22, 2002 at 7:20 am
One alternative is to pass the params in as an XML document, then use OpenXML to treat it like a table.
Andy
June 22, 2002 at 12:41 pm
I also have posted a method of creating a temp table parse the values into it then use IN (SELECT colvals FROM tmpTbl). I will find a thread later or you can search the threads on this type of issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 11:42 am
Nick, other than the select permissions, how bad is the dynamic query option? Does it prevent the optimizer from speeding up these kinds of queries (since it doesn't know what the query will be)?
Andy, how would I make that work in SQL7?
Antares686, how effient is that kind of solution (the tmp table) in an high traffic environment? Will SQL lock the temp table so simultaneous hits don't over lap? How does it compare to a dynamic query?
Just for the record, I'm appauled that there's no support for this kind of query. What good are stored proc params if we can use them to dynamically alter the parameters of a query?
June 25, 2002 at 12:47 pm
Its a trade off in terms of both security and building query plans I think. Making it work in SQL7...hmm. Been a while since I looked at the XML add on for SQL7, I'd check that first to see if it supports OpenXML. If it doesn't, then I'd say Antares method of creating a temp table (either before the call or inside) is your best bet.
Andy
June 25, 2002 at 5:13 pm
end-user,
Go with Antares686's suggestion of pumping the @States string in to a temporary table.
You lose all advantages of stored procedures using dynamic sql, and the SQL has to be compiled on each run. Error checking and control must also be done with in the dynamic sql batch, making returning information to the client more difficult.
Microsoft supposedly designed sp_executesql to allow the optimiser to reuse execution plans but I _suspect_ that this is only on itterations of the dynamic sql with-in a particular procedure call, not among procedure calls - but that's just a hunch.
I'm just very against dynamic sql in any application code - Though of course, it's ok to use it in code I write for myself 🙂
June 26, 2002 at 5:48 am
Well, then I guess the next question is how do I put it in a temp table? I'm trying to find a list, "AL,AZ,AK", that's dynamically sent to the SP each time. There's no list functions in SQL that would allow me to loop through the list and dump each individual item to a table (which I'd need to do with INSERT). How do I handle this?
June 26, 2002 at 6:56 am
you can try something like this. i have used this Article. to write this. i have written it originally for space as a delimiter. feel free to modify it for , .
create table tally(id int)
declare @i int
set @i=1
while @i < 8001
begin
insert into tally(id) values(@i)
set @i=@i+1
end
create procedure ConvertProper(@mstring varchar(8000))
as
set nocount on
declare @m varchar(8000)
declare @mStr varchar(8000)
declare @Si int
create table #quotes(phrase varchar(8000))
insert into #quotes values(@mString)
SELECT identity(int,1,1) as no,NullIf(SubString(' ' + Phrase + ' ' , ID , CharIndex(' ' , ' ' + Phrase + ' ' , ID) - ID) ,
'') AS Word into #test
FROM Tally, #Quotes
WHERE ID <= Len(' ' + Phrase + ' ') AND SubString(' ' + Phrase + ' ' , ID - 1, 1) = ' '
AND CharIndex(' ' , ' ' + Phrase + ' ' , ID) - ID > 0
set @m=''
set @mStr=''
while Exists (select * from #test)
begin
select @Si=min(no) from #test
select @m=word from #test where no=@si
set @mStr=@mStr+rtrim(SUBSTRING(UPPER(@m),1,1)+SUBSTRING(Lower(@m),2,len(@m)))+' '
delete from #test where no=@si
end
select @mStr as Word
go
HTH
June 26, 2002 at 7:20 am
-- Allowing for variable length, comma delimited fields.
declare @states varchar(2000)
select @states = 'AL,AZ,AK,MO,FE,ARIZONA,ZA'
Declare @MyState VARCHAR(10)
create table #states (state varchar(10) not null)
WHILE (charindex(',', @states)) > 0 BEGIN
insert #states (state) select substring( @states , 1, charindex(',', @states)-1 )
SELECT @states=substring( @states , charindex(',', @states)+1 , len(@states))
END
insert #states (state) select substring(@states , 1, len(@states))
select * from #states
drop table #states
-- With fixed length items (ie. each state can only be CHAR(2)), you could hardcode some of those values:
declare @states varchar(2000)
select @states = 'AL,AZ,AK,MO,FE,AR,ZA'
Declare @MyState CHAR(2)
create table #states (state char(2) not null)
WHILE (charindex(',', @states)) > 0 BEGIN
insert #states (state) select substring( @states , 1, 2)
SELECT @states=substring( @states , 4, len(@states))
END
insert #states (state) select substring(@states , 1, 2)
select * from #states
drop table #states
June 27, 2002 at 6:15 am
Excellent, thanks very much everyone! I've got it up and working. I really appreciate all the help. I still can't believe that this kind of basic functionality was overlooked.
Anyway, thanks again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply