February 15, 2007 at 2:42 am
More a puzzle than a need to fix, since now sorted. Had a SP in the structure below in SQL2005 (n.b. not complete SP shown below just rough structure) - essentially does different query depending on @qType - but one of the statements then had a nested logic blocks which did a different query depending on the month difference, anyway the ELSE statement did not have a BEGIN..END (here at point ****) . When run in Management Studio for 'type1' I got the desired result i.e. the single SELECT and the summary select, when run though as an SP through the app it returned all following selects (effectively timing out the connection) - putting a BEGIN..END block around the ELSE processing commands sorted the issue. Still trying to work out why it caused different behaviours in Management Studio and a called SP though.
....
IF @qType = 'type1'
BEGIN
IF DATEDIFF(mm,@StartDate,@EndDate) > 0
BEGIN
SELECT......
END
ELSE
****
SELECT......
END
IF @qType = 'type2'
BEGIN
SELECT......
END
IF @qType = 'type3'
BEGIN
SELECT......
END
SELECT Summary....
February 19, 2007 at 8:00 am
This was removed by the editor as SPAM
February 20, 2007 at 1:46 am
Was the ELSE ***** a sql statement, or several statements?
February 20, 2007 at 5:07 am
The ***** part of the ELSE was two SELECT statements
February 20, 2007 at 9:41 am
Multiple statements in IF / ELSE need to be wrapped in BEGIN...END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply