April 19, 2013 at 7:43 am
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/
April 19, 2013 at 7:48 am
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/
April 19, 2013 at 7:49 am
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/
April 19, 2013 at 7:52 am
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/
April 19, 2013 at 8:04 am
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/
April 19, 2013 at 8:27 am
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/
April 19, 2013 at 8:32 am
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/
April 19, 2013 at 8:51 am
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/
April 19, 2013 at 9:29 am
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/
April 19, 2013 at 10:11 am
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
*/
April 19, 2013 at 10:10 pm
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