September 20, 2016 at 3:52 pm
Hi folks,
I am trying to implement a procedure to delete a row from the table which is joined to 2 other tables. I have following logic so far, but I intend to use another filter condition from the outer table which is inner joined to 2 other tables. Not sure how to do the same.
I am unable to use this: where AD.ROW_NUMBER = @nROW_NUMBER;
Here's what I have so far:
CREATE PROCEDURE [hsip].[DELETE_A_ROW](
@cRegion CHAR(2) = '00',
@cState_Code CHAR(2) = '00',
@nFY NUMERIC(4,0) = 0,
@nREPORT_ID NUMERIC(2,0) = 0,
@nSECTION_ID NUMERIC(2,0) = 0,
@nSUBSECTION_ID NUMERIC(2,0) = 0,
@nDISPLAY_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
@nROW_NUMBER numeric(38,0)=0,
@sUSER_ID VARCHAR(25) = NULL)
AS
BEGIN
SET NOCOUNT ON;
delete UPLOAD_TEMP_DATA
from UPLOAD_TEMP_DATA as AD
inner join FY_ST_QUESTION_DETAIL as qd
on ad.FY_ST_QUESTION_DTL_TABLE_ID= qd.FY_ST_QUESTION_DTL_TABLE_ID
inner join FY_ST_QUESTION_INFO as qi
on qd.FY_ST_QUESTION_INFO_TABLE_ID=qi.FY_ST_QUESTION_INFO_TABLE_ID
where (qi.region = @cRegion
and qi.state_code = @cState_Code
and qi.fy = @nFY
and qi.report_id = @nREPORT_ID
and qi.section_id = @nSECTION_ID
and qi.subsection_id = @nSUBSECTION_ID
and qi.display_number = @nDISPLAY_NUMBER
and qi.QUESTION_NUMBER = @nQUESTION_NUMBER
and ( QI.REPORTER_ID = @sUSER_ID or
QI.DELEGATE_ID = @sUSER_ID or
QI.SUB_DELEGATE_ID = @sUSER_ID )
and QD.QUESTION_PART_NUMBER = @nQUESTION_PART_NUMBER)
where AD.ROW_NUMBER = @nROW_NUMBER;
END
GO
September 20, 2016 at 4:13 pm
Fixed it. I did this and query works fine:
USE [HSIP]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [hsip].[DELETE_A_ROW](
@cRegion CHAR(2) = '00',
@cState_Code CHAR(2) = '00',
@nFY NUMERIC(4,0) = 0,
@nREPORT_ID NUMERIC(2,0) = 0,
@nSECTION_ID NUMERIC(2,0) = 0,
@nSUBSECTION_ID NUMERIC(2,0) = 0,
@nDISPLAY_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
@nROW_NUMBER numeric(38,0)=0,
@sUSER_ID VARCHAR(25) = NULL)
AS
BEGIN
SET NOCOUNT ON;
delete UPLOAD_TEMP_DATA
from UPLOAD_TEMP_DATA as AD
inner join FY_ST_QUESTION_DETAIL as qd
on ad.FY_ST_QUESTION_DTL_TABLE_ID= qd.FY_ST_QUESTION_DTL_TABLE_ID
inner join FY_ST_QUESTION_INFO as qi
on qd.FY_ST_QUESTION_INFO_TABLE_ID=qi.FY_ST_QUESTION_INFO_TABLE_ID
and (qi.region = @cRegion
and qi.state_code = @cState_Code
and qi.fy = @nFY
and qi.report_id = @nREPORT_ID
and qi.section_id = @nSECTION_ID
and qi.subsection_id = @nSUBSECTION_ID
and qi.display_number = @nDISPLAY_NUMBER
and qi.QUESTION_NUMBER = @nQUESTION_NUMBER
and ( QI.REPORTER_ID = @sUSER_ID or
QI.DELEGATE_ID = @sUSER_ID or
QI.SUB_DELEGATE_ID = @sUSER_ID )
and QD.QUESTION_PART_NUMBER = @nQUESTION_PART_NUMBER)
where AD.ROW_NUMBER = @nROW_NUMBER;
END
GO
September 21, 2016 at 8:49 am
Why did you change the first WHERE to an AND rather than changing the second? For INNER JOINs, it is best for legibility to clearly separate your matching conditions from your filtering conditions. The conditions with a comparison to a parameter are clearly filtering conditions and therefore belong in the WHERE clause, not the ON clause under those guidelines.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply