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/