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;

Tony C. wrote:
No. If all a SELECT statement does is assign values to variables

SELECT @var=col1, @var2=col2 from table

it will not return a resultset. Otherwise, yes, it will always return a
resultset, empty or otherwise.


See above

See above.
>

Thank You.

However, this does return a resultset.


select @BeginProcessControlDate as BeginProcessControlDate,
@EndProcessControlDate as EndProcessControlDate,
count(*) as UsageGroupCount
from CabsUsageGroupHC;


only diff is SELECT @variable AS instead
of SELECT @variable =

could that be the difference?

Tony C. (me (AT) here (DOT) com) writes:
This is not variable assignment.

Yes, the second is variable assignment. The first produces a regular
result set. You simply return the value of the variable to the client.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks Again...

Tony C. wrote:
Almost, but not quite. These two statements are equivalent:

select @BeginProcessControlDate as BeginProcessControlDate
select BeginProcessControlDate = @BeginProcessControlDate

The point is, you have to analyze what is happening. In both of these
statements, a column called "BeginProcessControlDate" is being created, set
to the value of @BeginProcessControlDate, and being returned in the
resultset.

With a statement like:
select @BeginProcessControlDate = BeginProcessControlDate

the variable @BeginProcessControlDate is being assigned the value contained
in a table column called BeginProcessControlDate. If that column does not
exist in the table, an error will result.

So it's not simply a matter of looking for select clauses containing "="
operators as opposed to "AS" operators. You have to analyze what the
statements are doing.

Tony C. schreef op 02-10-2011 20:32:
If I need to assign data from a table to a variable, I use SELECT
@VariableX = ColumnX FROM TableX WHERE ConditionX

If I need to assign data to a variable where the data does not come from
a table (literal value, some calculation), I use SET @VariableX =
'literal value' / 2 * 146 / GETDATE()

Just one of my coding standards that help improve overall code quality
and probably a standard that can be argued about, but it's my standard.

Just my 2 cents.

Best,
Henk

Ok,
I'm a dot net developer who hasn't worked
with SQL server much lately that just inherited a huge
project with hundreds of tables and stored procedures.
I'll have to be diving back into it now and this has helped a lot.
Thanks again,
Tony C.