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

SQL Server Development (2000)

Dynamic SQL?

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-16 : 19:59:05

I have the following stored proceedure:

CREATE PROCEDURE spSearchItems
@sqlstr varchar(255) = "xyz"
as

DECLARE @temp VARCHAR(255);
DECLARE @searchstring VARCHAR(255);
DECLARE @searchstring1 VARCHAR(255);
DECLARE @searchstring2 VARCHAR(255);
DECLARE @searchstring3 VARCHAR(255);
DECLARE @searchstring4 VARCHAR(255);

SET @searchstring1 = 'xqxqxq';
SET @searchstring2 = 'xqxqxq';
SET @searchstring3 = 'xqxqxq';
SET @searchstring4 = 'xqxqxq';

DECLARE @counter int
select @counter =0
DECLARE @index int
select @temp = @sqlstr
select @temp = rtrim(ltrim(replace(@temp,char(34), " ")))
if @temp is null return
while @temp is not null
begin
select @index = charindex(',', @temp)
if @index = 0
begin
select @searchstring = ltrim(rtrim(@temp))
select @temp = null
end
else
begin
select @searchstring = ltrim(rtrim(left(@temp, @index-1)))
select @temp = right(@temp,len(@temp) - @index)
end
select @counter = @counter + 1
if @counter = 1 select @searchstring1 = '%' + @searchstring + '%'
if @counter = 2 select @searchstring2 = '%' + @searchstring + '%'
if @counter = 3 select @searchstring3 = '%' + @searchstring + '%'
if @counter = 4 select @searchstring4 = '%' + @searchstring + '%'
END

select ITEM, PTDRAW,DESC1,STATUS, LEFT(ITEM, 5) AS item_5d
FROM mfgpro.dbo.[ANA No Dups]
where item like @searchstring1
or item like @searchstring2
or item like @searchstring3
or item like @searchstring4
GO

Now, I want the result of the stored procedure to pass into the query below. I tried implementing them together and it worked, but it takes far too long. What the best whay to do this?

SELECT P1.ITEM, P2.PTDRAW, P1.DESC1, P1.STATUS, P1.PTDRAW AS mfg_draw
FROM dbo.[ANA No Dups] P1 INNER JOIN
dbo.[ANA No Dups] P2 ON LEFT(P1.ITEM, 5) + '-000' = P2.ITEM

Thanks!
LAne

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-16 : 21:17:22

Sorry - for dummys like me, could you include a brief plain text explanation of what you're trying to achieve - just 3 lines...Oh and how long is too long? How many records are we talking about?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-17 : 10:02:58

A LIKE comparison is one of the more expensive ops in sql, especially when you put '%' wildcards on both sides.

Do a search on CSV and take a look at Graz's sp_parsearray proc. If you are on SQL 2k you could write this as a table-valued function and join to it . . .

<O>

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-17 : 11:18:09

First, thanks for your responses!

What I am trying to do:

The stored procedure simply searches and returns a recordset. The second part:

SELECT P1.ITEM, P2.PTDRAW, P1.DESC1, P1.STATUS, P1.PTDRAW AS mfg_draw FROM dbo.[ANA No Dups] P1 INNER JOIN
dbo.[ANA No Dups] P2 ON LEFT(P1.ITEM, 5) + '-000' = P2.ITEM

takes the ptdraw field that is returned for numbers endind with "-000" and applies it to numbers ending in -001,-002,-003,etc. I have to do this on the fly because I can't update the table. I implemted this operation into the sp but it took about 20 times longer than it did without out. There are about 250,000 records in the table. I want to apply the second part only after I have a smaller recordset to deal with. Hope that makes sense.

Thanks Again!
Lane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources