?
Solved

Stored Procedure Q?

Posted on 2006-04-07
17
Medium Priority
?
275 Views
Last Modified: 2008-03-03
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
0
Comment
Question by:kevp75
  • 11
  • 5
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16402048
Not at all understand your question
0
 
LVL 25

Author Comment

by:kevp75
ID: 16402171
"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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16406378

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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 25

Author Comment

by:kevp75
ID: 16412141
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16415688
<<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
 
LVL 25

Author Comment

by:kevp75
ID: 16416536
"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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16424863

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
 
LVL 25

Author Comment

by:kevp75
ID: 16425883
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
 
LVL 25

Author Comment

by:kevp75
ID: 16441007
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
 
LVL 28

Accepted Solution

by:
imran_fast earned 1500 total points
ID: 16448483
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
 
LVL 25

Author Comment

by:kevp75
ID: 16449081
ok, that is out of the question, I cannot allow IUSR to have that kind of access to my db
0
 
LVL 25

Author Comment

by:kevp75
ID: 16449199
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
 
LVL 25

Author Comment

by:kevp75
ID: 16449311
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16463297
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
 
LVL 25

Author Comment

by:kevp75
ID: 16465131
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
 
LVL 25

Author Comment

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

Author Comment

by:kevp75
ID: 16473720
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question