September 22, 2016 at 8:28 am
I have written a procedure in T-SQL which returns errorcount based on state code,fiscal year etc..., but it has 3 pieces involved: 1. it returns inner join of 2 tables to pull selective columns 2. a case statement with multiple value possibility for coloffset 3. function to return errorcount.
When I run the code it gives output with 3 separate tables, but, I intend to get errorcount as the only output. How can I accomplish this. The oracle equivalent used cursor. Do I need to use it here?
Here's the code:
ALTER PROCEDURE [hsip].[ErrorCount] (
@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,
@suser_id varchar(25) =NULL,
@nFY_ST_QUESTION_DTL_TABLE_ID numeric(38,0))
AS
BEGIN
SET NOCOUNT ON;
--declare nfy_st_question_dtl_table_id as integer
--declare ncolumn_index cursor for
declare @coloffset as integer
--@temptbl as numeric(38,0)
select qi.REGION, qi.STATE_CODE, qi.FY, qi.REPORT_ID, qi.SECTION_ID, qi.SUBSECTION_ID, qi.DISPLAY_NUMBER, qi.QUESTION_NUMBER, qd.QUESTION_PART_NUMBER
from FY_ST_QUESTION_DETAIL qd inner join FY_ST_QUESTION_INFO 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 ;
set @coloffset=
case
when (@nREPORT_ID = 1 and @nQUESTION_NUMBER = 21) then 17
when (@nREPORT_ID = 1 and @nQUESTION_NUMBER = 32) then 16
when (@nREPORT_ID = 3 and @nQUESTION_NUMBER = 11) then 15
when (@nREPORT_ID = 3 and @nQUESTION_NUMBER = 12) then 27
end;
select [hsip].[getErrorCount](@nFY_ST_QUESTION_DTL_TABLE_ID, 0, @coloffset)as ErrCount;
END
Thanks
September 22, 2016 at 9:01 am
If you don't intend to return it, why are you including the SELECT in the code?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply