Stored Procedure Q?

Not at all understand your question

"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 "

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

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

<<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(@BreadCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb

GO

"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?
yes !
you can and you have to pass the column name and table name as a parameter for the stored procedure.

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.querystring("pCat"))
      if pCat = "" OR ISNULL(pCat) OR pCat = "0" then
            pCat = 0
      else
            pCat = pCat
      end if
      Set cnNav = createobject("ADODB.Connection")
            cnNav.Open inrsConn
                  write cnNav.Execute ("Execute gcDirNav " & Cstr(pCat) & ",' > '").Fields("Breadcrumb").Value
            cnNav.Close
      Set cnNav = Nothing
      %>

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(@BreadCrumb) - 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?

ok, that is out of the question, I cannot allow IUSR to have that kind of access to my db

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

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(@BreadCrumb) - 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

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(@BreadCrumb) - Len(@Delimiter))
Select @BreadCrumb As BreadCrumb

GO

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(@BreadCrumb) - 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

I also figured it wouldn't work because of this line too:If (Select Cat From ModPicCats Where [cID] = @PageID) <> 'Top'

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