I have a simple stored procedure which ends by SELECT 'YES' AS 'RESULT'
It seems like when running this code (from a calling app, incidentally)
exec StoredProcedurename 'param', 'param'
...Yesterday, doing that would return the actual value selected. I would get a 1-row resultset.
Today, it doesn't. Why is this and how can I make it always return the 1-row resultset?
post the stored procedure definition
USE [BIAnalytics]
GO
/****** Object: StoredProcedure [dbo].[PED_CheckPEDEffectiveDate] Script Date: 9/4/2015 11:04:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PED_CheckPEDEffectiveDate]
@AgentID varchar(20),
@EffectiveDate date
AS
BEGIN
/*
PURPOSE: Make sure an application being scrubbed in PED belongs to an agent qualified to
sell for that year. If not, return "NO". If so, return "YES".
*/
if @AgentID = 'CMS.GOV' or @AgentID='MAIL-IN' or @AgentID='INNOVA' or @AgentID='UNKNOWN' or @AgentID='WALK-IN'
select 'YES' as 'RESULT'
return
if exists
(
select
*
from
PED_SALESSENTINEL_2016_FINAL f
where
cast(left(ltrim(rtrim(f.program)),4) as int)>=year(@EffectiveDate)
and f.WRITING_CODE=@AgentID
)
select 'YES' as 'RESULT'
else
select 'NO' as 'RESULT'
END
You need to add a BEGIN and END as shown below
USE [BIAnalytics]
GO
/****** Object: StoredProcedure [dbo].[PED_CheckPEDEffectiveDate] Script Date: 9/4/2015 11:04:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PED_CheckPEDEffectiveDate]
@AgentID VARCHAR(20) ,
@EffectiveDate DATE
AS
BEGIN
/*
PURPOSE: Make sure an application being scrubbed in PED belongs to an agent qualified to
sell for that year. If not, return "NO". If so, return "YES".
*/
IF @AgentID = 'CMS.GOV'
OR @AgentID = 'MAIL-IN'
OR @AgentID = 'INNOVA'
OR @AgentID = 'UNKNOWN'
OR @AgentID = 'WALK-IN'
BEGIN ----------ADD THIS
SELECT 'YES' AS 'RESULT'
RETURN
END ----------ADD THIS
IF EXISTS ( SELECT *
FROM PED_SALESSENTINEL_2016_FINAL f
WHERE CAST(LEFT(LTRIM(RTRIM(f.program)), 4) AS INT) >= YEAR(@EffectiveDate)
AND f.WRITING_CODE = @AgentID )
SELECT 'YES' AS 'RESULT'
ELSE
SELECT 'NO' AS 'RESULT'
ENDAh.....Yes, of course. In fact, Begin's and End's surrounding every Result of the various conditions (If/then statements).
Can't believe I overlooked that. Thank you!!
We use BEGIN / END in all instances of IF / ELSE. In particular for one-line sections - on the grounds that they might become multi-line in the future and then there would be a chance that adding the BEGIN / END might be overlooked.
We are fussy about alignment / indentation too ... but that's obviously personal preference. Consistency more important than a war about mine-is-better-than-yours !!
IF xxx
BEGIN
IF yyy
BEGIN
yyyy
END
END
ELSE IF zzz
BEGIN
zzzz
END
ELSE
BEGIN
raiserror(...)
END
Ditto for me, except, in my case there is no "We", its just "Me" 
Yes - previous post did in fact set me straight, appreciate your comment as well, I totally agree about proper indentation. Thankfully (I guess?) my background is much more VB. So, as much as there are disadvantages to having a VB background going to SQL (thinking too much loop vs. set based), one of the major advantages to that is that I was already extremely picky about indentation, so much that the first thing I do when I get a hold of someone's SQL statement I usually reformat the whole thing. I have noticed, curiously, that there's very little consensus about indentation in the sql world, which is very curious to me.
To me it's very simple and universal: indentation is based on hierarchy and belonging. Whereas many people seem to just indent based on "that looks good", or "that seems right".
But I agree with you ultimately, for SQL at least, consistency is the most important I suppose. You couldn't get away with that in VB, though...if you can't see hierarchy and belonging via the indentation, it's practically IMPOSSIBLE to interpret code.
Anyway thanks again everyone, I am today reminded about begin/end on conditional statements, especially when returning varchar stuff from stored procs (something I'm not sure I should be doing all that much anyway, but that's another convo).
Thanks -
I came to SQL from ISAM and had to do the brain-reLoad from Loops to Sets ... it becomes second nature 
I see an unbelievable amount of SQL code that is to all intents and purposes not-indented / not-formatted - it looks "thrown together" - and I wonder how the Author / Author's Team manage to develop and maintain the code. For me consistent indentation & formatting is about Defensive Programming - I use it deliberately to make bugs easier to see - particularly the ones which are non-obvious, and the ones that would otherwise occur during code maintenance in the months and years to come.
Well ... there's "Kristen's Way"
I think part of the inconsistency is the way that people choose to "see" certain things.
The old C-Coding style of
if (some condition) {
xxx
}
(which I hate) has lead colleagues of mine to code SQL as
IF (some condition) BEGIN
xxx
END
and a range of other "trailing" keywords
My style uses the leading-keyword approach. I do not want ANYTHING at the end of a line which has a meaning that could mislead me if I was unable to see it (because screen window not wide enough to show the whole line).
FROM MyTable AS A
JOIN OtherTable AS B ON
B.SomeID = A.SomeID AND
B.OtherID = A.OrderID OR
B.ThirdID = A.ThirdID
the mixing of AND and OR here is very dangerous and, I think, is hard to see - in real-world the lines will be longer / uneven-length and more complex so harder to spot than the following leading-keyword style:
FROM MyTable AS A
JOIN OtherTable AS B
ON B.SomeID = A.SomeID
AND B.OtherID = A.OrderID
OR B.ThirdID = A.ThirdID
then you get to continuation operators, such as string concatenation, for which I think there are a variety of possible styles, and this, again, leads to the sort of variation that you are referring to
SELECT ThisIsAVeryLongColumnName +
ThisIsAnotherColumnName AS Foo,
YestAnotherColumnName AS Bar
I think this can be mistaken for being a three-column resultset, and the alias names can be off the edge of the screen and thus not seen 
I favour continuation operators at the start of the line:
SELECT ThisIsAVeryLongColumnName
+ ThisIsAnotherColumnName AS Foo
, YestAnotherColumnName AS Bar
and in fact I use the, non-standard, "Assignment" syntax of Alias names so that I don't overlook them:
SELECT [Foo] = ThisIsAVeryLongColumnName
+ ThisIsAnotherColumnName
, [Bar] = YestAnotherColumnName
One other point I feel strongly about is Join Condition layout. It seems to me to be more common to use:
FROM MyTable AS A
JOIN OtherTable AS B
ON A.SomeID = B.SomeID
AND A.OtherID = B.OrderID
whereas I favour putting the Joined Table's (B in the case) columns on the LEFT:
FROM MyTable AS A
JOIN OtherTable AS B
ON B.SomeID = A.SomeID
AND B.OtherID = A.OrderID
my rationale is that I want to be sure that I have included ALL the columns, in "Table-B", that need conditional tests - otherwise I may get more rows matching than I expect, or a Cartesian-join. Generally I will be matching all the Primary Key columns in Table-B - if they are on the Left I think it is easier to eyeball / audit that all of the required columns have been included.
To me, even that is problematic. I use:
FROM
MyTable AS A
Any other tables
The FROM keyword is the parent of the lines below it...nothing else goes on FROM except FROM.
Then again, all we are doing is proving the "no consensus" way.
I suppose one test works out OK...When people see my code, they usually can interpret it very quickly and I often even receive comments about how easy it is to read. That helps reaffirm.
Note, that does NOT apply to forum code, where it's often difficult to get the stupid thing to come out right - this forum no exception. I think I spend more time fighting this website messing up my code than I do actually composing the post. All of the pre-formatted, blockquote, and other abstract/meaningless formatting buttons need to disappear and be replaced with ONE, SIMPLE THING: "CODE".
I think the debate is useful for newbies - where people describe a style, and a reason "why", then a newbie can decide what works best for them. For us old-hands "our way is best" probably pervades!! but ... following various discussions I have started doing "leading comma". I find that it works in many places, but is more troublesome in others, so I am not sure ... and I hate having some-and-some in my code ... but it is an instance where, after many decades, I have made a change to my coding style in an effort to catch more bugs.
Either way, I think consistency is the most important thing. Once someone reads your code they can understand all your code - they don't have to say "What did s/he do THIS time"!!. And as you say "one test works out OK...When people see my code, they usually can interpret it very quickly" which I think is an excellent definition of good code style.
That makes me very angry too ... I'll post separately on that rather than hijack this thread. (link)