'Version' in where clause fails to match record

[email protected] · January 4th, 2010, 01:05 PM

When I include the version in the where clause of a stored procedures, the record is not found for Update, select , retrieve.
The insert works fine for stored procs...but it does not contain a version.

Thanx

ZeroFactorial · January 5th, 2010, 04:26 PM

Can you be more specific? I think the prob you are running into is due to Version being a binary field. You would need to extract the version data to know what to search for specifically.

Provide a sample of your SP.

[email protected] · January 5th, 2010, 05:48 PM

The sp and Table use a timestamp data type ...The ENTRoleCapabilityUpdate uses a binary dataType. Are your sp , table and ENTRoleCapabilityUpdate different from this?

ALTER PROCEDURE [dbo].[ENTRoleCapabilityUpdate]
(
@ENTRoleCapabilityId int,
@ENTRoleId int,
@ENTCapabilityId int,
@AccessFlag tinyint,
@UpdateENTUserAccountId int,
@Version timestamp
)
AS
SET NOCOUNT ON
UPDATE ENTRoleCapability
SET ENTRoleId = @ENTRoleId,
ENTCapabilityId = @ENTCapabilityId,
AccessFlag = @AccessFlag,
UpdateDate = GetDate(),
UpdateENTUserAccountId = @UpdateENTUserAccountId
WHERE ENTRoleCapabilityId = @ENTRoleCapabilityId
-- AND Version = @Version

tdewey · January 9th, 2010, 08:58 PM

I have found that the "SET NOCOUNT ON" eliminates the return of the number of rows affected. On Update and Delete Stored Procedures, the command effectively returns a False. I removed this command for update/delete stored procedures.

The Stored Procedures provided with the book uses SET NOCOUNT ON, but these SP have a "RETURN @@ROWCOUNT" as the last statement of the procedure. I noticed in the example that you provided the Return was not specified. I used the downloaded SP examples without much change.

Hope this helps.