Updation using fuction

  • Hi I want to perform updation using fuction but I am not able to do it and it throw following error:

    Msg 443, Level 16, State 15, Procedure GetSTNValue, Line 21

    Invalid use of a side-effecting operator 'UPDATE' within a function.

    CREATE FUNCTION dbo.GetSTNValue(@FromStore int, @ToStore int)

    RETURNS varchar(10)

    AS

    BEGIN

    Declare @STN varchar(10)=0,

    @count int=0,

    @AWBNo varchar(10)=0

    SET @AWBNo = (SELECT ISNULL(gs.AWBNo,0)FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)

    SET @count = (SELECT gsv.CountValue FROM GV_STNCountValues gsv WHERE gsv.StoreCode = @FromStore)

    IF @AWBNo = 0

    BEGIN

    IF NOT EXISTS (SELECT ISNULL(gs.STNNO,0) FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)

    BEGIn

    SET @STN = CONVERT(varchar,REPLACE(STR(@FromStore,4),' ',0)) + CONVERT(varchar,REPLACE(STR(@count + 1,5),' ','0'))

    --SELECT @STN

    --RETURN (@STN)

    UPDATE GV_STNCountValues

    SET CountValue = @count + 1

    WHERE StoreCode = @FromStore

    END

    ELSE

    BEGIN

    SET @STN = (SELECT gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)

    --RETURN (@STN)

    END

    END

    ELSE

    BEGIN

    DECLARE @oldSTN varchar(10)

    SET @oldSTN = (SELECT gs.STNNO FROM GV_STNDetails gs WHERE gs.From_StoreCode = @FromStore AND gs.TO_StoreCode = @ToStore)

    SET @STN = CONVERT(varchar,REPLACE(STR(@FromStore,4),' ',0)) + CONVERT(varchar,RIGHT(@STN,6)+ 1)

    --RETURN (@STN)

    END

    RETURN (@STN)

    END

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • For anybody else wandering in here I formatted the sql so you can read it:

    CREATE FUNCTION dbo.GetSTNValue (

    @FromStore INT

    ,@ToStore INT

    )

    RETURNS VARCHAR(10)

    AS

    BEGIN

    DECLARE @STN VARCHAR(10) = 0

    ,@count INT = 0

    ,@AWBNo VARCHAR(10) = 0

    SET @AWBNo = (

    SELECT ISNULL(gs.AWBNo, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    SET @count = (

    SELECT gsv.CountValue

    FROM GV_STNCountValues gsv

    WHERE gsv.StoreCode = @FromStore

    )

    IF @AWBNo = 0

    BEGIN

    IF NOT EXISTS (

    SELECT ISNULL(gs.STNNO, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    BEGIN

    SET @STN = CONVERT(VARCHAR, REPLACE(STR(@FromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 5), ' ', '0'))

    --SELECT @STN

    --RETURN (@STN)

    UPDATE GV_STNCountValues

    SET CountValue = @count + 1

    WHERE StoreCode = @FromStore

    END

    ELSE

    BEGIN

    SET @STN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    --RETURN (@STN)

    END

    END

    ELSE

    BEGIN

    DECLARE @oldSTN VARCHAR(10)

    SET @oldSTN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    SET @STN = CONVERT(VARCHAR, REPLACE(STR(@FromStore, 4), ' ', 0)) + CONVERT(VARCHAR, RIGHT(@STN, 6) + 1)

    --RETURN (@STN)

    END

    RETURN (@STN)

    END

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    The shortest path is to turn this into a proc instead of a function. There seems to be a lot of improvements you could make so this code flows a bit smoother.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (4/19/2013)


    kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    The shortest path is to turn this into a proc instead of a function. There seems to be a lot of improvements you could make so this code flows a bit smoother.

    But if I create a stored procedure for this then while inserting into a table for a particular column I need to call a sp within a sp and I think this will effect the performance

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    The shortest path is to turn this into a proc instead of a function. There seems to be a lot of improvements you could make so this code flows a bit smoother.

    But if I create a stored procedure for this then while inserting into a table for a particular column I need to call a sp within a sp and I think this will effect the performance

    You are currently calling a scalar function from within a proc. The performance isn't going to be any worse.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    The shortest path is to turn this into a proc instead of a function. There seems to be a lot of improvements you could make so this code flows a bit smoother.

    But if I create a stored procedure for this then while inserting into a table for a particular column I need to call a sp within a sp and I think this will effect the performance

    What if I convert this whole script into a dynamic query and assign to a variable and then simple assign that variable to that column while inserting.... Is it right approach?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (4/19/2013)


    kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    kapil_kk (4/19/2013)


    Sean Lange (4/19/2013)


    You can't do updates/insert/deletes in a function. You would need to make this a stored proc instead. Have you considered MERGE? I think you could probably turn this whole thing into a MERGE with OUTPUT.

    Actually this script I write to create a STNNo which will I used to insert in a column value with another stored procedure...

    How can I achieve this thing?

    The shortest path is to turn this into a proc instead of a function. There seems to be a lot of improvements you could make so this code flows a bit smoother.

    But if I create a stored procedure for this then while inserting into a table for a particular column I need to call a sp within a sp and I think this will effect the performance

    What if I convert this whole script into a dynamic query and assign to a variable and then simple assign that variable to that column while inserting.... Is it right approach?

    Not exactly sure what you mean.

    I would assume that the reason you made this a function is because you need this functionality in other places?

    This should do the same thing but as a sproc instead of a function.

    CREATE PROCEDURE dbo.GetSTNValue

    (

    @FromStore INT

    ,@ToStore INT

    ) AS

    BEGIN

    DECLARE @STN VARCHAR(10) = 0

    ,@count INT = 0

    ,@AWBNo VARCHAR(10) = 0

    SET @AWBNo = (

    SELECT ISNULL(gs.AWBNo, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    SET @count = (

    SELECT gsv.CountValue

    FROM GV_STNCountValues gsv

    WHERE gsv.StoreCode = @FromStore

    )

    IF @AWBNo = 0

    BEGIN

    IF NOT EXISTS (

    SELECT ISNULL(gs.STNNO, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    BEGIN

    SET @STN = CONVERT(VARCHAR, REPLACE(STR(@FromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 5), ' ', '0'))

    UPDATE GV_STNCountValues

    SET CountValue = CountValue + 1

    WHERE StoreCode = @FromStore

    END

    ELSE

    BEGIN

    SET @STN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    END

    END

    ELSE

    BEGIN

    DECLARE @oldSTN VARCHAR(10)

    SET @oldSTN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @FromStore

    AND gs.TO_StoreCode = @ToStore

    )

    SET @STN = CONVERT(VARCHAR, REPLACE(STR(@FromStore, 4), ' ', 0)) + CONVERT(VARCHAR, RIGHT(@STN, 6) + 1)

    END

    select @STN

    END

    Calling a proc from within a proc is not some huge performance nightmare. It is not a great thing but it doesn't suddenly mean performance is awful. Honestly the performance of this is most likely going to be exactly the same as your scalar function.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • A slight mod to Sean's code to return the single value to a variable:

    CREATE PROCEDURE dbo.GetSTNValue

    (

    @pFromStore INT

    ,@pToStore INT

    ,@pSTN VARCHAR(10) OUTPUT

    ) AS

    BEGIN

    DECLARE @count INT = 0

    ,@AWBNo VARCHAR(10) = 0

    SET @AWBNo = (

    SELECT ISNULL(gs.AWBNo, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @pFromStore

    AND gs.TO_StoreCode = @pToStore

    )

    SET @count = (

    SELECT gsv.CountValue

    FROM GV_STNCountValues gsv

    WHERE gsv.StoreCode = @pFromStore

    )

    IF @AWBNo = 0

    BEGIN

    IF NOT EXISTS (

    SELECT ISNULL(gs.STNNO, 0)

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @pFromStore

    AND gs.TO_StoreCode = @pToStore

    )

    BEGIN

    SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 5), ' ', '0'))

    UPDATE GV_STNCountValues

    SET CountValue = CountValue + 1

    WHERE StoreCode = @pFromStore

    END

    ELSE

    BEGIN

    SET @pSTN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @pFromStore

    AND gs.TO_StoreCode = @pToStore

    )

    END

    END

    ELSE

    BEGIN

    DECLARE @oldSTN VARCHAR(10)

    SET @oldSTN = (

    SELECT gs.STNNO

    FROM GV_STNDetails gs

    WHERE gs.From_StoreCode = @pFromStore

    AND gs.TO_StoreCode = @pToStore

    )

    SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, RIGHT(@pSTN, 6) + 1)

    END

    END

    GO

    /*

    How called:

    declare @newSTN varchar(10), @FromStore int, @ToStore int;

    set @FromStore = 1;

    set @ToStore = 2;

    exec dbo.GetSTNValue @pFromStore = @FromStore, @pToStore = @ToStore, @pSTN = @newSTN output;

    select @newSTN -- verify that the value was returned

    */

  • Thanks Lynn and Sean I will implement in this manner and let you guys know after implementing in that way 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 11 posts - 1 through 10 (of 10 total)

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