RETURN VALUE in stored procedure not always returning to ADO

M

Mark Neilson

I am having trouble with this code:

ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


Loking at the results in profiler, everything executes according to plan,
hower the return value (parameter in ADO) is only populated if this is
executed prior to the UPDATE statement - -i.e. I can never get 0 to populate
in the return parameter but always get -1 to populate

I am trying the SELECT NULL trick in KB194792 but still no result
 
O

oj

Try...
ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
SET NOCOUNT ON

-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



Mark Neilson said:
I am having trouble with this code:

ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


Loking at the results in profiler, everything executes according to plan,
hower the return value (parameter in ADO) is only populated if this is
executed prior to the UPDATE statement - -i.e. I can never get 0 to populate
in the return parameter but always get -1 to populate

I am trying the SELECT NULL trick in KB194792 but still no result
Click to expand...
 
M

Mark Neilson

Thank you but it didn't help. I am still getting the same result

oj said:
Try...
ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
SET NOCOUNT ON

-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



Mark Neilson said:
I am having trouble with this code:

ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


Loking at the results in profiler, everything executes according to plan,
hower the return value (parameter in ADO) is only populated if this is
executed prior to the UPDATE statement - -i.e. I can never get 0 to populate
in the return parameter but always get -1 to populate

I am trying the SELECT NULL trick in KB194792 but still no result
Click to expand...
Click to expand...
 
M

Mark Neilson

Problem appears to manifest after a trigger has executed that inserts values
in to another table

Does this shed any further light on the problem?
oj said:
Try...
ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
SET NOCOUNT ON

-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



Mark Neilson said:
I am having trouble with this code:

ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


Loking at the results in profiler, everything executes according to plan,
hower the return value (parameter in ADO) is only populated if this is
executed prior to the UPDATE statement - -i.e. I can never get 0 to populate
in the return parameter but always get -1 to populate

I am trying the SELECT NULL trick in KB194792 but still no result
Click to expand...
Click to expand...
 
O

oj

Mark,

The after trigger should only affect your query if you depend on an identity
value. If your sproc returns no recordset, your return_value will be
affected as documented by the kb.

If you post ddl+sample data+expected result, we will take a look. Perhaps,
there is another route to this.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Mark Neilson said:
Problem appears to manifest after a trigger has executed that inserts values
in to another table

Does this shed any further light on the problem?
oj said:
Try...
ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
SET NOCOUNT ON

-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE ID =
@ID), @LastAmended)) > 1 Return -1
SELECT NULL

UPDATE Reviews
SET
Scope=@Scope,
SignOffDate=@SignOffDate,
SystemsTaskTableName=@SystemsTaskTableName,
SystemsSubSystemTableName = @SystemsSubSystemTableName,
AircraftID = @AircraftID,
IsStartedAnalysis = @IsStartedAnalysis,
IsStartedSystemsAnalysis = @IsStartedSystemsAnalysis,
CloseOffDate = @CloseOffDate,
CloseOffName = @CloseOffName,
ReviewProjectName = @ReviewProjectName,
TaskViewName = @TaskViewName,
SubSystemViewName = @SubSystemViewName,
SystemsDatabaseName = @SystemsDatabaseName
WHERE ID = @ID
/* After the update, return the updated timestamp */
SELECT @LastAmended=GetDate() from Reviews where ID = @ID
-- SELECT NULL required to get value to populated return parameter back to
ADO!
SELECT * from dbo.reviews where id = @id
RETURN 0


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net



Mark Neilson said:
I am having trouble with this code:

ALTER PROCEDURE [dbo].[updateReview] @ID INT, @UserID VARCHAR (13),
@LastAmended DATETIME, @Scope VARCHAR ( 1000 ),
@SignOffDate SMALLDATETIME, @SystemsTaskTableName VARCHAR(100),
@SystemsSubSystemTableName VARCHAR(100), @AircraftID INT,
@IsStartedAnalysis BIT, @IsStartedSystemsAnalysis BIT,
@CloseOffDate SMALLDATETIME, @CloseOffName VARCHAR(100),
@ReviewProjectName VARCHAR(100), @TaskViewName VARCHAR(100),
@SubSystemViewName VARCHAR(100), @SystemsDatabaseName VARCHAR(100)
AS
-- check to see if the last update was more than a single second ago
--IF ABS(DATEDIFF(second, (SELECT LastAmended FROM dbo.Barcodes WHERE
Click to expand...
Click to expand...
ID
= back
to
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do TEMP tables in Stored Procedures not work with ADO Recordsets? 1
Stored procedure in SQL + vba to return a subset in Excel based onLIKE %varchar% clause 7
how to pass a return value from a sql 2000 stored procedure into access vba routine 2

Top