Stored Procedure Help... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Help...

Author

Topic

infrontweb
Starting Member

13 Posts

Posted - 2002-05-17 : 20:33:31

My apologies if this post is in the wrong category.

I need some help tuning my stored procedure.

I am trying to collect data from 2 related tables ('projects' and 'projectPhotos') and return the results to the asp page where 'projects' = Request.QueryString("vesselMakeID")

-- Scenario:
-- Select the vesselMake (from Table vesselMake) by vesselMakeID from a list on our *.asp page
-- Goto to the Projects list page with Request.QueryString("vesselMakeID") and List all queried projects from TempTable

-- WHILE Not EOF Start loop
-- Query1 - Return projectID, projectName, projectDetails, vesselMakeID (from Table 'projects') WHERE = @vesselMakeID
-- Query2 - Return projectPhotoID, photoPath, projectID (from Table 'projectPhotos') WHERE = @projectID From Query1
-- Save results to TempTable
-- Loop
-- Return Recordset1 From TempTable to *.asp page
-- Drop TempTable

CREATE PROCEDURE spProject_by_Make

@vesselMakeID int,
@projectID int,
@projectPhotoID int

AS

CREATE TABLE #TempTable
(
rowID int IDENTITY(1,1) PRIMARY KEY,
projectID int,
projectName varchar(50),
projectDetails varchar(100),
vesselMakeID int,
projectPhotoID int
)

INSERT INTO #TempTable

SELECT projectID, projectName, details, vesselMakeID, projectPhotoID FROM projects
WHERE vesselMakeID = @vesselMakeID

SELECT projectPhotoID, photo, projectID FROM projectPhotos
WHERE projectID = @projectID

DROP TABLE #TempTable
GO
--------------------------------------
Thanks in Advance for any help.

Nazim
A custom title

1408 Posts

Posted - 2002-05-18 : 03:47:17

CREATE PROCEDURE Project_by_Make_sp

@vesselMakeID int,
@projectID int,
@projectPhotoID int
AS
SELECT p.projectID, p.projectName, p.details, f.projectPhotoID, f.photo
projectPhotoID FROM
projects p
inner join projectPhotos f
on f.projectid=f.projectid
and p.vesselMakeID = @vesselMakeID and p.projectid=@projectid
and f.projectPhotoID=@projectPhotoID

Go

you can omit the unwanted parameters from your sp. and remember NEVER start your stored procedure name with sp_ it will add the search cost by first searching in the master database.

HTH

--------------------------------------------------------------

infrontweb
Starting Member

13 Posts

Posted - 2002-05-18 : 17:23:14

Thanks for pointing me in the right direction Nazim.
I modified the original code a bit and it worked like a champ.

New Code:
------------------------------------------------------------

CREATE PROCEDURE Projects_sp
@vesselMakeID int
AS
SELECT p.projectID, p.name, p.description, p.vesselMakeID, f.projectPhotoID, f.photo
FROM projects p
INNER JOIN projectPhotos f ON
p.projectID = f.projectID
and p.vesselMakeID = @vesselMakeID

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources