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

GO
LVL 25
kevp75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Not at all understand your question
0
kevp75Author Commented:
"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 "
0
imran_fastCommented:

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kevp75Author Commented:
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
0
imran_fastCommented:
<<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
0
kevp75Author Commented:
"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?
0
imran_fastCommented:

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.
0
kevp75Author Commented:
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
      %>
0
kevp75Author Commented:
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?
0
imran_fastCommented:
no you have to use dynamic sql you cannot use it like
  If (Select gCat From @TName Where [gcID] = @PageID) <> 'Top'

check sp_executle_sql  and exec inside books online

for example statement like
  If (Select gCat From @TName Where [gcID] = @PageID) <> 'Top'
will be
exec('if (select gcat from '+ @TName +' where [gcid] = '+@PageId+'<> ''top''
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kevp75Author Commented:
ok, that is out of the question, I cannot allow IUSR to have that kind of access to my db
0
kevp75Author Commented:
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
0
kevp75Author Commented:
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
0
imran_fastCommented:
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
0
kevp75Author Commented:
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
0
kevp75Author Commented:
I also figured it wouldn't work because of this line too:If (Select Cat From ModPicCats Where [cID] = @PageID) <> 'Top'
0
kevp75Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.