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/