473,322 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Procedure Stopping

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
Jul 20 '05 #1
1 1480
"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" <jm****@weir.net> wrote in message
news:91**************************@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

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Shaun Stuart | last post by:
I've got a webpage that calls some stored procedures with input variables. The procedures return recordsets and also some output variables. We're trying to get the values of the output variables....
5
by: CG | last post by:
Hi I have developed a Windows Service When I try to start the Service it tells me that it cannot start as there may not be any work to do When I comment out below line of code in my OnStart...
6
by: Dave | last post by:
I have a situation where I want to react to a ctrl-click on a <span> and it works in Netscape and Firefox browsers but in IE I have a problem. In IE I do catch the ctrl-click but IE also renders...
7
by: Peter D.C. | last post by:
Hi I want to update data hold in several textbox controls on an asp.net form. But it seems like it is the old textbox values that is "re-updates" through a stored procedure who updates a SQL...
0
by: Glen Wolinsky | last post by:
I am creating a Windows service that will check a request queue (database) for pending requests. It will then process each individual request until completed, wait a set time interval and then...
1
by: juleni | last post by:
Hello, is there a possibility starting or stopping postgres database and creating database instance by JAVA? Is there some API available for this? If yes, can you please write some example, how...
0
by: eldamiani | last post by:
Ineed a help. I want to ask, how I can do dialog box window which is a normal form, but causes stopping the code execution in form which call out this dialog box. I have ...
3
by: Jon Harrop | last post by:
So I'm just having a play with RPCs from C# using WSDL to generate lots of C# code, compile it, load it in as a DLL and then call the functions. It occurs to me that .NET should facilitate the...
3
by: Carmine [www.thetotalsite.it] | last post by:
Hi, I'm trying to use gmail smtp server for an asp.net application, but when I try to send an email I get this error: "The remote certificate is invalid according to the validation procedure." ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.