Lynn Pettis (5/6/2013)
kapil_kk (5/6/2013)
Sergiy (5/6/2013)
kapil_kk (5/6/2013)
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working 🙂Is it correct?
Yes, it is correct.
In fact, "RETURN @pSTN " was incorrect.
In procedures RETURN_VALUE by default contains error code generated by procedure execution (0 if there is no error).
You may overturn the default value with some other code, but it's vedry not-recommended practice.
RETURN as you used it must be used in scalar functions only.
Thanks for the suggestion 🙂
Actually, looking at the declaration of your procedure the following is how you should be invoking your procedure.
Procedure declaration (partial):
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 1001
,@pToStore int = 1004
,@pSTN VARCHAR(20) = 0 output
...
Invoked:
declare @pSTNval varchar(20);
exec dbo.BS_GetSTNValue @pFromStore = ??, @pToStore = ??, @pSTN = @pSTNVal OUTPUT -- The ?? are what ever values for FromStore and ToStore you are passing to the procedure
select @pSTNVal -- display value returned from procedure
This will eliminate the SELECT @pSTN you added to your procedure.
Hi Lynn,
I have made changes in this manner to the sp. Please let me know if it is right or not?
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 4001
,@pToStore int = 1004
,@pSTN VARCHAR(20) =0 output
AS
BEGIN
IF NOT EXISTS (
SELECT1
FROMGV_STNDetails gs
WHEREgs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) = 0
)
BEGIN
INSERT INTO GV_STNDetails (From_StoreCode, TO_StoreCode)
VALUES (@pFromStore,@pToStore)
END
DECLARE @count INT = 0
,@AWBNo VARCHAR(10) = 0,
@checkSTN varchar(10)
SET @checkSTN = (
SELECT TOP 1 ISNULL(gs.STNNO, 0)
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @checkSTN
SET @AWBNo = (
SELECTTOP 1 ISNULL(gs.AWBNo, 0)
FROMGV_STNDetails gs
WHEREgs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @AWBNo
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)
IF @AWBNo = 0
BEGIN
IF CAST(@checkSTN as BIGINT) = 0
BEGIN
SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(@count + 1, 6), ' ', '0'))
--PRINT 'WHEn STN & AWB is 0 ' + @pSTN
SELECT @pSTN
UPDATEGV_STNDetails
SETSTNNO = @pSTN
WHEREFrom_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
UPDATEGV_STNCountValues
SETCountValue = CountValue + 1
WHEREStoreCode = @pFromStore
END
ELSE
BEGIN
SET @pSTN = (SELECT TOP 1 gs.STNNO
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0)
UPDATE GV_STNDetails
SETSTNNO = @pSTN
WHERE From_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0
--PRINT 'same ' + @pstn
SELECT @pSTN
END
END
ELSE
BEGIN
DECLARE @oldSTN VARCHAR(10)
SET @oldSTN = (
SELECT TOP 1 gs.STNNO
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore AND ISNULL(gs.AWBNo,0) <> 0
)
SET @pSTN = CONVERT(VARCHAR, REPLACE(STR(@pFromStore, 4), ' ', 0)) + CONVERT(VARCHAR, REPLACE(STR(RIGHT(@oldSTN, 6) + 1,6),' ',0))
--PRINT @pSTN
UPDATEGV_STNDetails
SETSTNNO = @pSTN
WHEREFrom_StoreCode = @pFromStore AND TO_StoreCode = @pToStore AND ISNULL(AWBNo,0) = 0
--PRINT 'awb ' + @pSTN
SELECT @pSTN
END
--RETURN @pSTN
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/