• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

ASP w/Dynamic Stored Procedure?

I am trying (unsuccessfully) to get an answer to thie question posted here:http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21805764.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?
0
kevp75
Asked:
kevp75
  • 8
  • 3
2 Solutions
 
deighcCommented:
Unless you want to use an EXEC statement in your stored procedure (which is a bad idea for many reasons) then there's no elegant way to do this.

I can't think of a way that you can make the SP entirely dynamic (ie. pass it any table name and have the SP use that).

But the best you could do is create a new input parameter in the SP - let's say it's called @tableName. Then, in your SP, you can use a CASE statement to determine which table to query.

Something like this (simplified example obviously)

CASE @tableName
   WHEN 'TableA' THEN SELECT * FROM TableA
   WHEN 'TableB' THEN SELECT * FROM TableB
   ELSE SELECT * FROM ModPicCats
END

This, unfortunately, means duplicating a lot of code for each table. But, as I said, I don't know of any alternatives.
0
 
kevp75Author Commented:
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.
0
 
ThinkPaperCommented:
Try looking at: http://www.4guysfromrolla.com/webtech/020600-1.shtml

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)
   
    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM " +
                   @TableName + "WHERE FirstName = '"
                   + @FirstName + "' AND LastName = '"
                   + @LastName + "'"
   
    -- Execute the SQL statement
    EXEC(@SQLStatement)

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kevp75Author Commented:
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?
0
 
kevp75Author Commented:
hold that thought, I think I may have (guessed) it right.  BRB while I experimant
0
 
kevp75Author Commented:
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
0
 
kevp75Author Commented:
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
0
 
deighcCommented:
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.
0
 
kevp75Author Commented:
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
0
 
deighcCommented:
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....
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
 
kevp75Author Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now