September 22, 2013 at 11:36 am
Hi All,
Can someone help me on how to tackle this.
I have a stored procedure on my server called usp_RPT_HSG_JobType. This procedure returns the job type code and name that I want to use in another procedure. I have this other procedure which I called usp_RPT_HSG_Repairs by declaring a table variable as follows:
CREATE PROC usp_RPT_HSG_Repairs
AS
BEGIN
DECLARE @Repairs TABLE
Jobnumber INT
, Jobtype nvarchar (20)
INSERT INTO @Repairs
SELECT r.jobnumber
re.code
FROM repheader r
INNER JOIN repairtype re ON re.code = r.code
-- Output for details
SELECT *
FROM @Repairs
WHERE Jobtype IN (How do I then call the other procedure usp_RPT_HSG_JobType here?)
END
Thanks for answering
EO
September 22, 2013 at 4:08 pm
Since the SQL to return the records you want is really simple, I would just join this...
SELECT r.jobnumber
re.code
FROM repheader r
INNER JOIN repairtype re ON re.code = r.code
to your other query. if you really need to simplify, maybe you could create this part as a View, and then filter later (in the stored procedure).
September 23, 2013 at 8:01 am
I agree with pietlinden. Assuming your SP that returns a job type (or types) doesn't have any complex logic in it I would implement any parameters passed to usp_RPT_HSG_JobType in usp_RPT_HSG_Repairs and just have a single select statement in usp_RPT_HSG_Repairs. Something like this:
CREATE PROCEDURE usp_RPT_HSG_Repairs
(
[parameter list]
)
AS
BEGIN;
SET NOCOUNT ON;
SELECT
R.jobnumber,
R.code AS Jobtype
FROM
dbo.repheader AS R
WHERE
EXISTS (SELECT 1 FROM dbo.repairType AS RT WHERE [col list = paramter list] AND R.code = RT.code)
END;
I'm assuming that usp_RPT_HSG_JobType is just a query of repairType that returns either a list of codes or a single code. By using EXISTS instead of IN and eliminating the JOIN to repairType (not needed since you are just returning code which is in repHeader) you should get a more efficient query that required fewer reads.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply