Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #707

One more thing I don't understand in large Stored Procedure

Show all headers | View raw

My Question is now:

1) Does a SELECT Statement ALWAYS return a result set?
2) Is there a way I can tell by looking at the code which select
statements  should return results sets from and which not? 

I have several SELECT statements that don't appear to return anything.
Sometimes a SELECT statments sets a local variable and doesn't return
anything and sometimes it does.....how do I tell the difference?


Here is some of the code: They apparantly don't return resultsets.


Help is much appreciated,
Tony C. 

==========================================================
(Header omitted)
(Variable Declarations Excluded)

select @curDate = getdate();

SELECT @locDAPOICode = locDa.POICode, @ra8xxPOICode = ra8xx.POICode,
@dfexcPOICode = dfexc.POICode, @dftgPOICode = dftg.POICode
  FROM CmnConfiguration cc
  join CmnPOI locDA on locDA.CmnPOIID = cc.LocalDACmnPOIID
  join CmnPOI ra8xx on ra8xx.CmnPOIID = cc.Intralata8xxCmnPOIID
  join CmnPOI dfexc on dfexc.CmnPOIID = cc.DefaultExchangeCmnPOIID
  join CmnPOI dftg on dftg.CmnPOIID = cc.DefaultTrunkGroupCmnPOIID;


select @vcMsg = 'P_CabsBillRun_BillComputeGetData - Input
CabsBillRunID ' + CAST(@CabsBillRunID as Varchar(10));

select @version = Version, @UsagePeriodStartDate =
UsagePeriodStartDate, @UsagePeriodThroughDate =
UsagePeriodThroughDate, @PIUYearMonth = BillDate
  from CabsBillRun where CabsBillRunID = @CabsBillRunID  and
CabsBillRunStatusHCID in (3);		-- 3	Compute In Progress
if (@@rowcount <> 1) begin
  select @vcMsg = @vcMsg + ' does not exist  or its
CabsBillRunStatusHCID in not Compute In Progress (3). ';
  goto raiserrorExit;
end;

select TOP 1 @BeginProcessControlDate = ProcessControlDate 	-- 
  from SrcProcessControlDate 
 where ProcessControlDate >= @UsagePeriodStartDate
   and IsApproved = 'T' 
 order by  ProcessControlDate asc;
if(@BeginProcessControlDate is null) begin 
  select @vcMsg = @vcMsg + ' has NO approved source files in the date
range. Changing CabsBillRunStatusHCID to SetupInProgress (1).';
  update CabsBillRun set Version = Version +1, ModifyDate = @curDate,
UserID = @UserID, CabsBillRunStatusHCID = 1, ErrorMsg = 'There are NO
approved source files for this export.' where CabsBillRunID =
@CabsBillRunID;
  goto raiserrorExit;
end;


select TOP 1 @EndProcessControlDate = ProcessControlDate 	-- 
  from SrcProcessControlDate 
 where ProcessControlDate >= @UsagePeriodThroughDate
   and IsApproved = 'T' 
 order by  ProcessControlDate asc;
if(@EndProcessControlDate is null) begin 
  select @vcMsg = @vcMsg + ' SrcProcessControlDate greater than or
equal to the UsagePeriodThroughDate must be approved. Changing
CabsBillRunStatusHCID to SetupInProgress (1).';
  update CabsBillRun set Version = Version +1, ModifyDate = @curDate,
UserID = @UserID, CabsBillRunStatusHCID = 1, ErrorMsg =
'SrcProcessControlDate greater than or equal to the
UsagePeriodThroughDate must be approved.' where CabsBillRunID =
@CabsBillRunID;
  goto raiserrorExit;
end;


if(@EndProcessControlDate <> @UsagePeriodThroughDate) begin
	select TOP 1 @EndProcessControlDate = ProcessControlDate -- 
	  from SrcProcessControlDate 
	 where ProcessControlDate <= @UsagePeriodThroughDate
	 order by  ProcessControlDate desc;
end;


select @CabsAccountingImportRunID = CabsAccountingImportRunID from
CabsAccountingImportRun where CabsBillRunID = @CabsBillRunID and
CabsAccountingImportRunStatusHCID = 4;
if(@@rowcount <> 1)  begin   select @vcMsg = @vcMsg + '
CabsAccountingImportRun does not exist or
CabsAccountingImportRunStatusHCID is not APPROVED (4).';
  update CabsBillRun set Version = Version +1, ModifyDate = @curDate,
UserID = @UserID, CabsBillRunStatusHCID = 1, ErrorMsg =
'CabsAccountingImportRun does not exist or
CabsAccountingImportRunStatusHCID is not APPROVED (4).' where
CabsBillRunID = @CabsBillRunID;
  goto raiserrorExit;
end;

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

One more thing I don't understand in large Stored Procedure Tony C. <me@here.com> - 2011-10-02 12:34 -0500
  Re: One more thing I don't understand in large Stored Procedure "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-10-02 13:44 -0400
    Re: One more thing I don't understand in large Stored Procedure Tony C. <me@here.com> - 2011-10-02 12:47 -0500
    Re: One more thing I don't understand in large Stored Procedure Tony C. <me@here.com> - 2011-10-02 13:05 -0500
      Re: One more thing I don't understand in large Stored Procedure Erland Sommarskog <esquel@sommarskog.se> - 2011-10-02 20:31 +0200
        Re: One more thing I don't understand in large Stored Procedure Tony C. <me@here.com> - 2011-10-02 13:32 -0500
          Re: One more thing I don't understand in large Stored Procedure Henk van den Berg <me@myplace.net> - 2011-10-02 21:28 +0200
      Re: One more thing I don't understand in large Stored Procedure "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-10-02 15:25 -0400
        Re: One more thing I don't understand in large Stored Procedure Tony C. <me@here.com> - 2011-10-02 15:31 -0500

csiph-web