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_Closed
as varchar(15))
SET @.intAND = 1
END
ELSE
BEGIN
SET @.strWHERE = @.strWHERE + ' AND PRG.PRG_Closed = ' +
cast(@.PRG_Closed 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_Printed = convert(char(10),@.ORD_Date,101)
WHEREPRG_ID = cast(@.PRG_ID as varchar(15))
END
GO"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.net> wrote in message
news:91422298.0403041138.1059d960@.posting.google.c om...
> 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_Closed
> as varchar(15))
> SET @.intAND = 1
> END
> ELSE
> BEGIN
> SET @.strWHERE = @.strWHERE + ' AND PRG.PRG_Closed = ' +
> cast(@.PRG_Closed 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_Printed = convert(char(10),@.ORD_Date,101)
> WHERE PRG_ID = cast(@.PRG_ID as varchar(15))
> END
> GO
