I have the following sp which works:

USE [SIS]
GO
/****** Object:  StoredProcedure [dbo].[update_dept_mstr]   Script Date: 04/26/2011 12:15:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[update_dept_mstr] (@code char(2))
as 
begin
set nocount on;

DECLARE @MyTableVar table(
    NewLastReceipt char(6),
    OldLastReceipt char(6)
);

update DM set LastReceipt = RIGHT( '0000000' + cast(cast(dm.LastReceipt as int) + 1 as varchar(10)), 6)
OUTPUT INSERTED.LastReceipt,
       DELETED.LastReceipt
into @MyTableVar
from dbo.DEPT_MSTR dm
where Code = @code

select *
from @MyTableVar

end

I just want to know if this can be done with pure llbl code. The cool part is that it outputs the value after the update is made.

Thanks,

Fishy

Hmmm. I'm struggling to see how it can be, at least simply and transactionally. Doesn't it rely on the INSERTED and DELETED virtual tables, which are only available in SPs and triggers ?

Is there any reason why you need it in code, and can't just map a SP call to it ?

Matt

MTrinder wrote:

Hmmm. I'm struggling to see how it can be, at least simply and transactionally. Doesn't it rely on the INSERTED and DELETED virtual tables, which are only available in SPs and triggers ?

Is there any reason why you need it in code, and can't just map a SP call to it ?

Matt

I know very little about sps (a friend of mine just wrote this sp and hour ago). I'm ok with just using the sp and moving on but i thought it would be cool if this is something that could be done with straight llbl

The other way I was thinking is to: 1. fetch the entity 2. update using an expression to increment and format it. 3. refetch it and compare with original fetch.

If #3 value did not match #1 value +1 then i would call the routine until it did.

I don't know how to write the update expressing for #2.

So, if you could point me in the right direction for that then that would be great.

Try something like this:

// fetch the entity
DepartmentMasterEntity dept = new DepartmentMasterEntity();
adapter.FetchEntity(dept);

// set the expression
dept.Fields.Salary.ExpressionToApply =
     new DBFunctionCall("RIGHT( '0000000' + CAST(CAST({0} as int) + 1 AS VARCHAR(10)), 6)"
          , new object[]{ DepartmentMasterFields.LastReceipt});
dept.Fields.Salary.IsChanged = true;

// save it with refetch
adapter.SaveEntity(dept, true);