Procedure Stopping

[John Shaw]

Hi,

Please look at the code at the bottom of this procedure (UPDATE
COVENANT PRINT DATE). When I put it at the tope of this section, it
does the update and fails to return the SELECT results. When I put it
at the bottom, the SELECT returns results and the UPDATE fails.

Can anyone suggest what might be causing my problem?

Thanks

/************ SPECIFIC PROGRAM SEARCH *************** *********/
IF @PRG_ID != 0 -- PRG_ID supplied
BEGIN
SET @strWHERE = 'WHERE PRG.PRG_ID = ' + cast(@PRG_ID as varchar(15))
EXECUTE (@strSELECT + ' ' + @strFROM + ' ' + @strWHERE + ' ' +
@strORDERBY)
RETURN(0)
END

/************ NON-SPECIFIC PROGRAM SEARCH *************** *********/

IF @ORG_ID != 0 -- Add ORG_ID to where
IF @intAND = 0
BEGIN
SET @strWHERE = @strWHERE + 'ORG.ORG_ID = ' + cast(@ORG_ID as
varchar(15))
SET @intAND = 1
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + ' AND ORG.ORG_ID = ' + cast(@ORG_ID as
varchar(15))
END

IF @PRG_Closed != 2 -- Add PRG_Closed to where
IF @intAND = 0
BEGIN
SET @strWHERE = @strWHERE + 'PRG.PRG_Closed = ' + cast(@PRG_Close d
as varchar(15))
SET @intAND = 1
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + ' AND PRG.PRG_Closed = ' +
cast(@PRG_Close d as varchar(15))
END

IF @strWHERE != 'WHERE ' -- if some parameters supplied..
BEGIN -- Execute search
EXECUTE (@strSELECT + ' ' + @strFROM + ' ' + @strWHERE + ' ' +
@strORDERBY)
RETURN(0)
END


/************ UPDATE COVENANT PRINT DATE *************** *********/
-- Added by JSHAW 02/25/2004
IF @PRG_Covenant != 0 -- PRG_Covenant supplied

BEGIN
--SET @ORD_Date = GETDATE()
UPDATE PROGRAMS
SET Covenant_Printe d = convert(char(10 ),@ORD_Date,101 )
WHERE PRG_ID = cast(@PRG_ID as varchar(15))
END
GO

[Igor Raytsin]

Re: Procedure Stopping

"RETURN is immediate and complete and can be used at any point to exit from
a procedure, batch, or statement block. Statements following RETURN are not
executed."


HTH
Igro Raytsin

"John Shaw" <jmshaw@weir.ne t> wrote in message
news:91422298.0 403041138.1059d 960@posting.goo gle.com...[color=blue]
> Hi,
>
> Please look at the code at the bottom of this procedure (UPDATE
> COVENANT PRINT DATE). When I put it at the tope of this section, it
> does the update and fails to return the SELECT results. When I put it
> at the bottom, the SELECT returns results and the UPDATE fails.
>
> Can anyone suggest what might be causing my problem?
>
> Thanks
>
> /************ SPECIFIC PROGRAM SEARCH *************** *********/
> IF @PRG_ID != 0 -- PRG_ID supplied
> BEGIN
> SET @strWHERE = 'WHERE PRG.PRG_ID = ' + cast(@PRG_ID as varchar(15))
> EXECUTE (@strSELECT + ' ' + @strFROM + ' ' + @strWHERE + ' ' +
> @strORDERBY)
> RETURN(0)
> END
>
> /************ NON-SPECIFIC PROGRAM SEARCH *************** *********/
>
> IF @ORG_ID != 0 -- Add ORG_ID to where
> IF @intAND = 0
> BEGIN
> SET @strWHERE = @strWHERE + 'ORG.ORG_ID = ' + cast(@ORG_ID as
> varchar(15))
> SET @intAND = 1
> END
> ELSE
> BEGIN
> SET @strWHERE = @strWHERE + ' AND ORG.ORG_ID = ' + cast(@ORG_ID as
> varchar(15))
> END
>
> IF @PRG_Closed != 2 -- Add PRG_Closed to where
> IF @intAND = 0
> BEGIN
> SET @strWHERE = @strWHERE + 'PRG.PRG_Closed = ' + cast(@PRG_Close d
> as varchar(15))
> SET @intAND = 1
> END
> ELSE
> BEGIN
> SET @strWHERE = @strWHERE + ' AND PRG.PRG_Closed = ' +
> cast(@PRG_Close d as varchar(15))
> END
>
> IF @strWHERE != 'WHERE ' -- if some parameters supplied..
> BEGIN -- Execute search
> EXECUTE (@strSELECT + ' ' + @strFROM + ' ' + @strWHERE + ' ' +
> @strORDERBY)
> RETURN(0)
> END
>
>
> /************ UPDATE COVENANT PRINT DATE *************** *********/
> -- Added by JSHAW 02/25/2004
> IF @PRG_Covenant != 0 -- PRG_Covenant supplied
>
> BEGIN
> --SET @ORD_Date = GETDATE()
> UPDATE PROGRAMS
> SET Covenant_Printe d = convert(char(10 ),@ORD_Date,101 )
> WHERE PRG_ID = cast(@PRG_ID as varchar(15))
> END
> GO[/color]