Extremly difficult dynamic Store Proc!!!!!

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)

Extremly difficult dynamic Store Proc!!!!!

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-08-03 : 21:45:27

Hi, I have 2 tables:

CREATE TABLE Categories(
  cID int IDENTITY PRIMARY KEY,
  Name varchar(30),
  parent int)
CREATE TABLE GROUPS(
  gID int IDENTITY PRIMARY KEY,
  Name Varchar(30)
  cID int)

What i would like to do send from the web site a @parent int that is a parent cat to values in the category table. I would like to get back the name of the categories under the @parent and concat another field called qString (example qString = 'pI=1019&cat=1003'). One last thing is if one of those cats that it pulls back has only one group, that it would concat to the end of the string 'gr=1005' so that, that value comeing back would be 'pI=1019&cat=1003&gr=1005'.
the numbers in the concat string are also pulled from the tables too.

an example would be:


INSERT Categories VALUES(1001,MLB,1019)
INSERT Categories VALUES(1003,WorldSeries,1019)
INSERT Categories VALUES(1019,BaseBall,null)

INSERT Groups VALUES(11,Colorado Rockies,1001)
INSERT Groups VALUES(12,Atlanta Braves,1001)
INSERT Groups VALUES(13,2001 World Series,1003)

I send the the stored Procedure the Parent 1019
and i should receive back a table like this
 --------------------------------------
|'MLB'         |'pA=1019&cA=1001'      |
|'World Series'|'pA=1019&cA=1003&gA=13'|  
---------------------------------------
where the field 'world Series' gets the '&gA=13' Concatenated to the end of the string because it only has 1 group where MLB has 2 or more.

I get pretty far on this but run into problems when doing a if else clause to concat the extra string to the end.
Could someone help?
-john

ps. so far this is my code. I cant figure out the last Concat part. the part where it should concat the '&gA=13'



Declare @i int
Select @i = 1019
CREATE TABLE #TempTable
(
  tID int IDENTITY PRIMARY KEY,
cID int,
  Name varchar(30),
  qString nvarchar (30)
)

INSERT INTO #TempTable
(
name,cID,
qString
)(Select name,cid, 'pI=' + Cast(@i as nvarchar(5)) + '&cI=' + cast(Categories.cID as nvarchar(5))
FROM Categories
WHERE parent =@i) 

Select * from #temptable
Drop Table #TempTable

Edited by - johnhamman on 08/03/2002 22:52:05

Johnhamman
Starting Member

37 Posts

Posted - 2002-08-04 : 01:32:21

Hallaluyah, I beleive i got it.
Does anyone see a way to make this run faster and/or more efficiant?



Declare @i int
Select @i = 1019
CREATE TABLE #TempTable
(
  tID int IDENTITY PRIMARY KEY,
cID int,
  Name varchar(30),
  qString nvarchar (30)
)
Create Table #temptable2
(gID int,
cID int,
)
Declare @j int
Select @j = Categories.cID FROM Categories
WHERE parent = @i
Declare @z nvarchar(1000)

INSERT INTO #temptable2
SELECT groups.gID,groups.cID
FROM groups
Where groups.cID =  @j GROUP BY groups.gID, groups.cID
HAVING (((Count(groups.cID))=1))

INSERT INTO #TempTable
(
name,cID,
qString
)(Select name,cid,'pI=' + Cast(@i as nvarchar(5)) + '&cI=' + cast(Categories.cID as nvarchar(5))
FROM Categories
WHERE parent =@i) 

Update #temptable
Set qString = ('pI=' + Cast(@i as nvarchar(5)) + '&cI=' + cast(t.cID as nvarchar(5))+'&gI=' + Cast(e.gID as nvarchar(4)))
from #temptable t INNER JOIN #temptable2 e ON t.cID = e.cID


Select * from #temptable
Drop Table #TempTable
Drop Table #TempTable2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources