asked on

ASP w/Dynamic Stored Procedure?

I am trying (unsuccessfully) to get an answer to thie question posted here:https://www.experts-exchange.com/questions/21805764/Stored-Procedure-Q.html

Basically:
I have a table called ModPicCats, and it is structured like this:

catID
parent_ID
gCat

I use the following stored procedure and ASP to display a "Breadcrumb" navigation from this table.
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

the ASP Code I use to display the nav is as follows:
<%
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
%>

what I'd like to do is make the stored procedure dynamic. So all I'd have to do is pass the table name (the other tables will be structured the exact same as above), and the correct URL. However I do not know how.

Can anyone help?

SOLUTION

deighc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kevp75

ASKER

ok, so how would I pass a parameter to the SP? The code I am currently using to get the info from the SP is in the question.

ASKER CERTIFIED SOLUTION

ThinkPaper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kevp75

ASKER

ok. I'm all set with making the procedure I need.

Question is how do I PASS a value to it using ASP. The code I currently use above GETS values from it and displays the data how I need. I just need to know how to PASS something to it, specifically TableName.

Right now I am (guessing) I pass @PageID to it via this ASP code:
write cnNav.Execute ("Execute gcDirNav " & Cstr(pCat) & ",' > '").Fields("Breadcrumb").Value

-where @PageID = Cstr(pCat)

where in that line am I supposed to pass @TableName?

kevp75

ASKER

hold that thought, I think I may have (guessed) it right. BRB while I experimant

kevp75

ASKER

ok. I'm lost. I tried this:
CREATE Procedure DirNav(@PageID nvarchar(255) = 0,@Delimiter varchar(5) = ' > ') As
Declare @TName nvarchar(255)
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

but all it keeps giving me is an error:137 Must Delcare the variable @TName

kevp75

ASKER

i have also tried it like:
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

and I get the same error:137 Must Declare the variable @TName

deighc

You ARE declaring the variable so I think the problem is something else - the error message is a bit misleading.

I think that this is the problem:

Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From @TName Where [gcID] = @PageID

You can't do that in a stored procedure. You can't interpolate a parameter value as a table name (or a field name for that matter).

[In theory you could build a string inside your SP and execute it with an EXEC statement, but I don't recommend this].

As I mentioned in my first post, you have to do something like this:

CASE @TName
WHEN 'TableA' THEN Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From TableA Where [gcID] = @PageID
WHEN 'TableB' THEN Select '<a href="piccats.asp?pCat=' + @PageID + '">' + gCat + '</a>' From TableB Where [gcID] = @PageID
END

Clunky, but it works.

kevp75

ASKER

CASE @TName
WHEN 'ModPicCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModPicCats Where [gcID] = @PageID
WHEN 'ModLinksCats' THEN Select '<a href="?pCat=' + @PageID + '">' + gCat + '</a>' From ModLinksCats Where [gcID] = @PageID
END

gives me an incorrect syntax error near keyword case, and near keyword end

Clunky, but it doesn't work either

deighc

Strange - your syntax looks OK.

You can use CASE statements in two ways:

CASE <variable or value to check against>
WHEN <test value 1> THEN <statements>
WHEN <test value 2> THEN <statements>
-- optional
ELSE <default statements>
END

or

CASE
WHEN <variable or value to check against> = <test value 1> THEN <statements>
WHEN <variable or value to check against> = <test value 2> THEN <statements>
-- optional
ELSE <default statements>
END

So maybe this syntax will work:

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

Clunky, and not sure if it works....

kevp75

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

kevp75

ASKER

got it!

seems that I did not have a choice but to use the 4guys method and make it a dynamic SP.

thank you folks
~kev