Stored Proc - returning a combined recordset from two separate tables

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 Proc - returning a combined recordset from two separate tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-10 : 08:18:12

Paul Stewart writes "I have created a stored procedure in SQL 2000. This procedure quries a few different tables and output results to another table.

In the same proc I have anther SQL statement which queries and updates the newly create table. Code is show below.

I have a seperate program which then retrieves the data.

What I want to do is to acheive the same results, without using a temp table. I want to simply return the data to the external program.

I'm not sure how to do the 2nd query without creating a temp table. Hope you can point me in the right direction. Thanks.

CREATE PROCEDURE Test (@plocation varchar(10), @pseries varchar(10),@pbore real,@pstandard varchar(1),@pstroke real)
AS
DECLARE @pmatrix varchar(1)
SELECT @pmatrix=1
DROP TABLE TBL_Results

CREATE TABLE TBL_Results (manuloc nvarchar(25) NULL, Pref BIT NULL)

INSERT INTO TBL_Results
SELECT ManLoc,0
FROM TBL_CDDMS_Data INNER JOIN TBL_CDDMS_Rules ON TBL_CDDMS_Data.ManRule = TBL_CDDMS_Rules.[No] INNER JOIN TBL_CDDMS_Manu_Country ON TBL_CDDMS_Data.ManLoc = TBL_CDDMS_Manu_Country.Location
WHERE TBL_CDDMS_Data.Series=@pseries AND TBL_CDDMS_Rules.[Max Bore]>=@pbore AND TBL_CDDMS_Rules.[Max Stroke]>=@pstroke AND TBL_CDDMS_Rules.[Standard/Special]>=@pstandard AND TBL_CDDMS_Manu_Country.[Matrix enabled]=@pmatrix;

UPDATE TBL_Results
SET Pref = 1
WHERE manuloc = (SELECT PrefManLoc FROM TBL_CDDMS_PSO_Country WHERE PSO Like @plocation)
GO"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 08:29:08

maybe a case statement (read in BOL)


INSERT INTO TBL_Results
SELECT 
	ManLoc,
	Pref = Case when manuloc = (SELECT PrefManLoc FROM TBL_CDDMS_PSO_Country WHERE PSO Like @plocation) then 1 else 0 end
FROM TBL_CDDMS_Data 
INNER JOIN TBL_CDDMS_Rules 
ON TBL_CDDMS_Data.ManRule = TBL_CDDMS_Rules.[No] 
INNER JOIN TBL_CDDMS_Manu_Country 
ON TBL_CDDMS_Data.ManLoc = TBL_CDDMS_Manu_Country.Location
WHERE TBL_CDDMS_Data.Series=@pseries 
AND TBL_CDDMS_Rules.[Max Bore]>=@pbore 
AND TBL_CDDMS_Rules.[Max Stroke]>=@pstroke 
AND TBL_CDDMS_Rules.[Standard/Special]>=@pstandard 
AND TBL_CDDMS_Manu_Country.[Matrix enabled]=@pmatrix;

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-10 : 10:20:24

quote:
What I want to do is to acheive the same results, without using a temp table. I want to simply return the data to the external program.

I take it that "TBL_Results" is what you are refering to as a temp table, this is more commonly known as a staging table. #TBL_Results would be a temp table

Anyway to get to what you want just remove the INSERT statement from Corey's code above, just leave the SELECT............

Andy

Beauty is in the eyes of the beerholder

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-10 : 10:44:44

oops

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources