How to apply a 2nd Store Procedure within the same web page

I  have a product details page that user goes to when he wants more information about an individual product.
The url looks something like this. www.mywebsite.com?product.asp?ID=2
and my store procedure looks something like this:
ALTER PROCEDURE [my].[sp_details]
@ID varchar (255)
As
SELECT LINK_ID, CAT_ID, LINK_NAME
FROM tableProducts
WHERE LINK_ID = @ID

On the same page I want to call up 10 similar products that have the same "CAT_ID" as the product being displayed.

I  tried added the below SP but I'm not getting the results I need.
ALTER PROCEDURE [my].[sp_RandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)    
As
SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts
WHERE LINK_ID = @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY NEWID()

I work with: Classic ASP VB, SQL 2005, SQL Server Express, DreamweaverCS3, newbie to SQL
swaggerkingAsked:
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:
ALTER PROCEDURE [my].[sp_RandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)    
As
EXEC( 'SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts WHERE LINK_ID IN ( ' +@ID + ') AND CAT_ID LIKE ''%'+@MMColParam+'%''
ORDER BY NEWID()'  )
0
swaggerkingAuthor Commented:
aneeshattingal:
I'll give that shot. Unfortunately they just shut down my testing server for maintenance so I won't be able to get back to you with a response for a couple of hours.
0
Aneesh RetnakaranDatabase AdministratorCommented:
ohh ok
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

swaggerkingAuthor Commented:
aneeshattingal:
I inserted your statement but I'm still not getting the results I had hoped for. When I run both stored procedures on my web page I'm only getting identical matching records. I want to display 10 related products that contain the same CAT_ID for any ID that I pull up

If I go to the details page for product.asp?ID=2 which for example purposes is Milk and it has CAT_ID = Beverages it would pull 10 related beverages.
Orange Juice
Water
Dr. Pepper
Sprite
Coffee
etc...

If product.asp?ID=44 was Hotdog and had CAT_ID = Meat I might see related 10 related meat products.
Sirloin
Ground beef
Chicken
Turkey
etc...                                      
0
Aneesh RetnakaranDatabase AdministratorCommented:
can this ID contain more than one IDs separated with commas ? if this is not a comma separated list then you dont really need that dynamic sql

ALTER PROCEDURE [my].[spRandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)    
As
SELECT TOP 10 * FROM (
SELECT LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts
WHERE LINK_ID = @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
UNION 
SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts WHERE LINK_ID <> @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY NEWID()
)
 

Open in new window

0
swaggerkingAuthor Commented:
aneeshattingal:
I really appreciate you helping a newbie like myself and I'm sure I'm making this harder than it need be.

First, I keep getting an error message:
spRandombyCat Line 13 Incorrect syntax near ')'.

Second, As a layman, I'm trying to comprehend your provided statement. I was wondering why you are calling TOP 10 twice from Line 5 and Line 10?

Third, Can this ID contain more than one IDs separated with commas?
Are you referring to LINK_ID or CAT_ID?
CAT_ID can contain more than 1 CAT_ID. Some categories will overlap so that is why I'm using LIKE in the WHERE statement. I'm using alpha characters.
Fruits = a
Vegetables = b
Fruits & Vegetables = ab
Chicken = c
Rice = d
MealPlan = acd

I'm providing jpg page example to what exactly I'm trying accomplish. Maybe this will help.


product.jpg
0
Aneesh RetnakaranDatabase AdministratorCommented:
i forgot to put the table aliase
>I was wondering why you are calling TOP 10 twice from Line 5 and Line 10?
you can see 2 select statements separated with a UNION Statement, in case the first statement returns no records, you still need to show 10 records, right. thats why i put a top 10 there.

ALTER PROCEDURE [my].[spRandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)    
As
SELECT TOP 10 * FROM (
SELECT LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts
WHERE LINK_ID = @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
UNION 
SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts WHERE LINK_ID <> @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY NEWID()
)A

Open in new window

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
swaggerkingAuthor Commented:
Weird, I still only get 1 record showing. For testing purposes I changed all of the CAT_ID to the same value in my table and still only 1 record shows. The record that is showing is based upon it's unique ID.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="\Connections\mystring.asp" -->
<%
 
Dim Command1__ID
Command1__ID = "1"
if(Request("ID") <> "") then Command1__ID = Request("ID")
 
Dim Command1__MMColParam
Command1__MMColParam = "a"
if(Request("MMColParam") <> "") then Command1__MMColParam = Request("MMColParam")
 
%>
<%
 
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_CONN_mystring_STRING
Command1.CommandText = "mydatabase.sp_RandombyCat"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@ID", 200, 1,255,Command1__ID)
Command1.Parameters.Append Command1.CreateParameter("@MMColParam", 200, 1,5,Command1__MMColParam)
set RS_Random = Command1.Execute
RS_Random_numRows = 0
 
%>
 
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = -1
Repeat1__index = 0
RS_Random_numRows = RS_Random_numRows + Repeat1__numRows
%>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<% 
While ((Repeat1__numRows <> 0) AND (NOT RS_Random.EOF)) 
%>
 
<%=(RS_Random.Fields.Item("LINK_NAME").Value)%>
 
<% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RS_Random.MoveNext()
Wend
%>
</body>
</html>

Open in new window

product2.jpg
0
Aneesh RetnakaranDatabase AdministratorCommented:
Did you try running from sqlsever
0
swaggerkingAuthor Commented:
Yes, it works PERFECTLY in SQL Server. Something getting lost in translation to DW. I guess I will have to work that one out and/or start a new thread.
0
swaggerkingAuthor Commented:
Statement works exactly how I needed it to work. I still need to work out some bugs on my end but the provide statement work perfectly as intended in SQLserver. Thanks so much for your help.
0
Aneesh RetnakaranDatabase AdministratorCommented:
i think so, i have no idea of dreamweaver ;)
0
swaggerkingAuthor Commented:
I got it to work so I wanted to share it in case some one else has problems running stored procedures in Dreamweaver CS3.

After of day of pulling my hair out and googling I finally figured out why the stored procedure wasn't executing properly in DWcs3.

I needed to define my schema and catalog values in my connection string under the "Advanced" button. Once I did that my SP ran as the gods intended.
advConString.gif
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
Query Syntax

From novice to tech pro — start learning today.