kevp75
asked on
Stored Procedure Q?
I have a web application that I have built. I use 2 tables.
The first is a category table which is simply
catID
parent_catID
categoryName
I use a stored procedure to Display a breadcrumb type of navigation, and it works great.
What I'm wondering is what would I need to do to this stored procedure (pasted below) to make it work with another category table (the given example is for a photo gallery, I would like to re-use the same procedure for a links directory)
/****** Object: Stored Procedure dbo.gcDirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure gcDirNav(@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0"> Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
The first is a category table which is simply
catID
parent_catID
categoryName
I use a stored procedure to Display a breadcrumb type of navigation, and it works great.
What I'm wondering is what would I need to do to this stored procedure (pasted below) to make it work with another category table (the given example is for a photo gallery, I would like to re-use the same procedure for a links directory)
/****** Object: Stored Procedure dbo.gcDirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure gcDirNav(@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0">
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
Not at all understand your question
ASKER
"I use a stored procedure to Display a breadcrumb type of navigation, and it works great.
What I'm wondering is what would I need to do to this stored procedure (pasted below) to make it work with another category table "
What I'm wondering is what would I need to do to this stored procedure (pasted below) to make it work with another category table "
May be you need to change this too but dont know what are you doing
==========================
Set @BreadCrumb = '' <--- Need to change this
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb <--------- and need to change this
ASKER
this stored procedure (coupled with some ASP code) allows me to use a breadcrumb type navigatio for my photo gallery.
It pulls the info from a table structured like this:
gcID
parentID
gCat
In it is stired the categories for the photos, but it is structured like this
1 0 Photos
2 1 Stills
3 1 Nature Shots
4 0 Graphics
5 4 Web
6 4 Wallpapers
7 4 Icons
doing it like this allows me to use 1 table and have an unlimited amount of depth to the categories of the photo gallery. The ASP script takes the data, so if I am in a category and click on the next child it displays the navigation at the top of the page structured like this:Top -> Photos ->Stills
I would like to use this same method, but with a link directory. Basically what it boils down to is what do I need to do to the stored procedure above to make it so I can pull the data from any table that is structured the same as the photo gallery's categories table
It pulls the info from a table structured like this:
gcID
parentID
gCat
In it is stired the categories for the photos, but it is structured like this
1 0 Photos
2 1 Stills
3 1 Nature Shots
4 0 Graphics
5 4 Web
6 4 Wallpapers
7 4 Icons
doing it like this allows me to use 1 table and have an unlimited amount of depth to the categories of the photo gallery. The ASP script takes the data, so if I am in a category and click on the next child it displays the navigation at the top of the page structured like this:Top -> Photos ->Stills
I would like to use this same method, but with a link directory. Basically what it boils down to is what do I need to do to the stored procedure above to make it so I can pull the data from any table that is structured the same as the photo gallery's categories table
<<what do I need to do to the stored procedure above to make it so I can pull the data from any table that is structured the same as the photo gallery categories table>>
In that case you need to change the table name and the column name thats it.
to be more precise check the column and table below which are in <<>>
but you have to create new procedure for each table if you want to use the same procedure then you need to use dynamic sql inside the procedure
CREATE Procedure gcDirNav(@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select <<[gCat]>> From <<ModPicCats>> Where <<[gcID]>> = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + <<gCat>> + '</a>' From <<ModPicCats>> Where <<[gcID]>> = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select <<ParentID>> From <<ModPicCats>> Where <<[gcID]>> = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0"> Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
In that case you need to change the table name and the column name thats it.
to be more precise check the column and table below which are in <<>>
but you have to create new procedure for each table if you want to use the same procedure then you need to use dynamic sql inside the procedure
CREATE Procedure gcDirNav(@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select <<[gCat]>> From <<ModPicCats>> Where <<[gcID]>> = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + <<gCat>> + '</a>' From <<ModPicCats>> Where <<[gcID]>> = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select <<ParentID>> From <<ModPicCats>> Where <<[gcID]>> = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0">
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
ASKER
"but you have to create new procedure for each table if you want to use the same procedure then you need to use dynamic sql inside the procedure"
so I can't just use 1 procedure for multiple tables?
so I can't just use 1 procedure for multiple tables?
so I can't just use 1 procedure for multiple tables?
yes !
you can and you have to pass the column name and table name as a parameter for the stored procedure.
ASKER
ok, but
->"but you have to create new procedure for each table if you want to use the same procedure then you need to use dynamic sql inside the procedure"<-
you just said that I have to create a new procedure for each table?
How would I pass the column name and table as parameters for the procedure? I use ASP right now to get the values from the procedure like this:
<%
pCat=killC(request.queryst ring("pCat "))
if pCat = "" OR ISNULL(pCat) OR pCat = "0" then
pCat = 0
else
pCat = pCat
end if
Set cnNav = createobject("ADODB.Connec tion")
cnNav.Open inrsConn
write cnNav.Execute ("Execute gcDirNav " & Cstr(pCat) & ",' > '").Fields("Breadcrumb").V alue
cnNav.Close
Set cnNav = Nothing
%>
->"but you have to create new procedure for each table if you want to use the same procedure then you need to use dynamic sql inside the procedure"<-
you just said that I have to create a new procedure for each table?
How would I pass the column name and table as parameters for the procedure? I use ASP right now to get the values from the procedure like this:
<%
pCat=killC(request.queryst
if pCat = "" OR ISNULL(pCat) OR pCat = "0" then
pCat = 0
else
pCat = pCat
end if
Set cnNav = createobject("ADODB.Connec
cnNav.Open inrsConn
write cnNav.Execute ("Execute gcDirNav " & Cstr(pCat) & ",' > '").Fields("Breadcrumb").V
cnNav.Close
Set cnNav = Nothing
%>
ASKER
I've been told that I can pass another parameter to the SP. Updated SP is:
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From @TName Where [gcID] = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From @TName Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From @TName Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0"> Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
However this keeps putting out an error when I hit 'OK'
the error is Error:137 Must declare the variable @TName
any ideas?
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From @TName Where [gcID] = @PageID) <> 'Top'
Begin
Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From @TName Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From @TName Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0">
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
However this keeps putting out an error when I hit 'OK'
the error is Error:137 Must declare the variable @TName
any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, that is out of the question, I cannot allow IUSR to have that kind of access to my db
ASKER
someone suggested to me doing something like this
CASE
WHEN @TName = 'ModPicCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID
WHEN @TName = 'ModLinksCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID
END
CASE
WHEN @TName = 'ModPicCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID
WHEN @TName = 'ModLinksCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID
END
ASKER
ok, this:
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
CASE
WHEN @TName = 'ModPicCats' THEN @BreadCrumb = (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
WHEN @TName = 'ModLinksCats' THEN @BreadCrumb = (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
END
/*Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb*/
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0"> Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
gives me multiple errors now.
Error 156:Incorrect syntax near the keyword 'CASE'
Line 10 Incorrect sytax near '+'
Line 11 Incorrect sytax near '+'
Incorrect syntax near the keyword 'End'
Cannot use a BREAK statement outside the scope of a WHILE statement
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
CASE
WHEN @TName = 'ModPicCats' THEN @BreadCrumb = (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
WHEN @TName = 'ModLinksCats' THEN @BreadCrumb = (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
END
/*Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb*/
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0">
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
gives me multiple errors now.
Error 156:Incorrect syntax near the keyword 'CASE'
Line 10 Incorrect sytax near '+'
Line 11 Incorrect sytax near '+'
Incorrect syntax near the keyword 'End'
Cannot use a BREAK statement outside the scope of a WHILE statement
SHOULD BE OK NOW
==============
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
SELECT @BreadCrumb = CASE
WHEN @TName = 'ModPicCats'
THEN (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
WHEN @TName = 'ModLinksCats' THEN (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
END
/*Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb*/
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0"> Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
==============
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@TName nvarchar(255) = 0,@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select gCat From ModPicCats Where [gcID] = @PageID) <> 'Top'
Begin
SELECT @BreadCrumb = CASE
WHEN @TName = 'ModPicCats'
THEN (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
WHEN @TName = 'ModLinksCats' THEN (Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb
END
/*Set @BreadCrumb = (Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID) + @Delimiter + @BreadCrumb*/
Set @PageID = (Select ParentID From ModPicCats Where [gcID] = @PageID) -- This iterates round until we get to the top (lcID = 0)
End
Else
Break
End
Set @BreadCrumb = '<a href="piccats.asp?pCat=0">
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
ASKER
K. I've done that, and no more errors, however now my Breadcrumb nav doesn't work. So I've modified the SP a bit to this:
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@PageID nvarchar(255) = 0,@TName nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select Cat From ModPicCats Where [cID] = @PageID) <> 'Top'
Begin
SELECT @BreadCrumb = CASE
WHEN @TName = 'ModPicCats'
THEN (Select '<a href="?Cat=' + @PageID + '">' + Cat + '</a>' From ModPicCats Where [cID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModPicCats Where [cID] = @PageID)
WHEN @TName = 'ModLinksCats'
THEN (Select '<a href="?Cat=' + @PageID + '">' + Cat + '</a>' From ModLinksCats Where [cID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModLinksCats Where [cID] = @PageID)
END
End
Else
Break
End
Set @BreadCrumb = '<a href="?Cat=0">Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea dCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb
GO
which now puts out an error:Error 156 Incorrect sytax near the keyword 'Set'
Incorrect syntax near 'WHEN'
Incorrect syntax near '+'
Incorrect syntax near 'Else'
Cannot use a BREAK statement outside the scope of a WHILE statement
/****** Object: Stored Procedure dbo.DirNav Script Date: 2/7/2006 1:06:39 PM ******/
CREATE Procedure DirNav(@PageID nvarchar(255) = 0,@TName nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @BreadCrumb nvarchar(4000)
Set @BreadCrumb = ''
WHILE @PageID > 0
Begin
If (Select Cat From ModPicCats Where [cID] = @PageID) <> 'Top'
Begin
SELECT @BreadCrumb = CASE
WHEN @TName = 'ModPicCats'
THEN (Select '<a href="?Cat=' + @PageID + '">' + Cat + '</a>' From ModPicCats Where [cID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModPicCats Where [cID] = @PageID)
WHEN @TName = 'ModLinksCats'
THEN (Select '<a href="?Cat=' + @PageID + '">' + Cat + '</a>' From ModLinksCats Where [cID] = @PageID) + @Delimiter + @BreadCrumb
Set @PageID = (Select ParentID From ModLinksCats Where [cID] = @PageID)
END
End
Else
Break
End
Set @BreadCrumb = '<a href="?Cat=0">Top</a>' + @Delimiter + @BreadCrumb
Set @BreadCrumb = Left(@BreadCrumb,Len(@Brea
Select @BreadCrumb As BreadCrumb
GO
which now puts out an error:Error 156 Incorrect sytax near the keyword 'Set'
Incorrect syntax near 'WHEN'
Incorrect syntax near '+'
Incorrect syntax near 'Else'
Cannot use a BREAK statement outside the scope of a WHILE statement
ASKER
I also figured it wouldn't work because of this line too:If (Select Cat From ModPicCats Where [cID] = @PageID) <> 'Top'
ASKER
Looks like I had no choice but to use the exec method. I got it to work (finally!), just not quite the way I had it planned.
Thank you for at least pointing me in the right direction
Thank you for at least pointing me in the right direction