Post reply

Convert and call Function to Insert Statement

  • November 8, 2011 at 11:29 pm

    #259655

    I am new to SQL Server and would like to convert one procedure to function and call that function into select statement.

    Procedure listed as below:

    CREATE PROCEDURE Test_NextEmployee

    AS

    SET NOCOUNT ON

    DECLARE @iReturn int

    BEGIN TRANSACTION

    SELECT @iReturn = Sequence FROM .dbo.EMPSEQUENCE (TABLOCKX) -- set exclusive table lock

    UPDATE dbo.EMPSEQUENCE SET Sequence = ( Sequence + 1 )

    COMMIT TRANSACTION

    SELECT @iReturn

    RETURN @iReturn

    And after that i have to call that function into below Insert Statement in place of AutoKey column.

    Insert Into EmpSequence (KeySegment, Keystn, KeySegmentType, FinlSegment, Status)

    select '[highlight=#ffff11]Auto key[/highlight]', eop.Keystn, 0, a.FinlSegment, 2

    from KeyEOP KP

    join KeyEOPSegmentDetail a on a.KeyEOP=kP.KeyEOP and a.updop < 2

    join Kfinl f on eop.keyeop = f.keyfeop

    join FinlSegmentDetail ff on f.keyfinl = ff.keyfinl and ff.updop < 2

    and ff.finlsegment = a.finlsegment

    join stn I on i.Keystn=EOP.Keystn and Class in ( 8,15)

    where EOP.UpdOp<>2

    Can anyone please help with that?

    Thanks

  • November 9, 2011 at 2:18 am

    #1405031

    Can anyone reply on this?

    Thanks

  • November 9, 2011 at 2:21 am

    #1405033

    Hello and welcome to SSC!

    It seems that your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.

    When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

    Thanks.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 9, 2011 at 2:33 am

    #1405038

    UPDATE dbo.EMPSEQUENCE SET Sequence = ( Sequence + 1 )

    You don't have any specific logic to generate EMPSEQUENCE (just +1). If the function is to generate IDs please use IDENTITY columns.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply