May 6, 2013 at 1:34 am
I have a stored procedure :
In this prcedure I am returning @pSTN value and used in another sp as output. @pSTn value is generating on basis of From Store and suppose if my from store is 1001 thn @pstn value like '1001000111' is will retun no error but when my @fromstore variable is > 2 i.e. 3001 or 400 it will start giving me error 'The conversion of the varchar value '4001000012' overflowed an int column'.
To prevent that i used CAST in the RETURN statment of the stored procedure but then it giving me error:
'Arithmetic overflow error converting expression to data type int.'
How to solve this?
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 1001
,@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
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
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
END
RETURN @pSTN
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 6, 2013 at 2:54 am
Instead of RETURN I have added a SELECT @pSTN statement for the different different conditions and its working 🙂
Is it correct?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 6, 2013 at 6:25 am
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.
_____________
Code for TallyGenerator
May 6, 2013 at 7:11 am
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 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 6, 2013 at 10:28 am
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.
May 6, 2013 at 11:16 pm
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/
May 7, 2013 at 1:20 am
Four queries in your stored procedure use TOP (n) without ORDER BY. The results may be unpredictable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 7, 2013 at 1:23 am
ChrisM@Work (5/7/2013)
Four queries in your stored procedure use TOP (n) without ORDER BY. The results may be unpredictable.
Hi Chris, I didn't get your point
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 1:46 am
TOP (n) without ORDER BY can return different results between executions of the same query. TOP 1 effectively means "give me a row from the set matching the search criteria, any row will do". That's very different to TOP 1 ... ORDER BY, where the first row of the ordered list would be returned.
Is this really what you want?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 7, 2013 at 3:30 am
I'm sure there's an easier (and safer) way of doing this.
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 1001,
@pToStore int = 1004,
@pSTN VARCHAR(20) = 0 output
AS
--BEGIN
DECLARE
@count INT = 0,
@AWBNo VARCHAR(10) = 0,
@checkSTN varchar(10)
DECLARE @oldSTN VARCHAR(10)
------------------------------------------------------------------
-- If there isn't a row in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- with a blank AWBNo (NULL or 0) then
-- create one, inserting only @pFromStore and @pToStore
IF NOT EXISTS (
SELECT 1
FROM GV_STNDetails gs
WHERE gs.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
-------------------------------------------------------------------
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
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
-------------------------------------------------------------------
-- Get a random AWBNo from GV_STNDetails
-- matching on @pFromStore and @pToStore
SET @AWBNo = (
SELECT TOP 1 ISNULL(gs.AWBNo, 0)
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @AWBNo
-------------------------------------------------------------------
-- Get the CountValue from GV_STNCountValues for @pFromStore
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)
IF @AWBNo = 0
BEGIN
--IF CAST(@checkSTN as BIGINT) = 0
IF @checkSTN = 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
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
UPDATE GV_STNCountValues
SET CountValue = CountValue + 1
WHERE StoreCode = @pFromStore
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
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)
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'same ' + @pstn
END
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
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
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'awb ' + @pSTN
END
RETURN @pSTN
--END
If you can correct the TOP(n) queries with ORDER BY, or better still MIN or MAX, it will clarify your intent. That in turn would make it much easier to rewrite this lot. It's well worth it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 7, 2013 at 7:09 am
ChrisM@Work (5/7/2013)
I'm sure there's an easier (and safer) way of doing this.
ALTER PROCEDURE [dbo].[BS_GetSTNValue]--DECLARE
@pFromStore int = 1001,
@pToStore int = 1004,
@pSTN VARCHAR(20) = 0 output
AS
--BEGIN
DECLARE
@count INT = 0,
@AWBNo VARCHAR(10) = 0,
@checkSTN varchar(10)
DECLARE @oldSTN VARCHAR(10)
------------------------------------------------------------------
-- If there isn't a row in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- with a blank AWBNo (NULL or 0) then
-- create one, inserting only @pFromStore and @pToStore
IF NOT EXISTS (
SELECT 1
FROM GV_STNDetails gs
WHERE gs.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
-------------------------------------------------------------------
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
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
-------------------------------------------------------------------
-- Get a random AWBNo from GV_STNDetails
-- matching on @pFromStore and @pToStore
SET @AWBNo = (
SELECT TOP 1 ISNULL(gs.AWBNo, 0)
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @AWBNo
-------------------------------------------------------------------
-- Get the CountValue from GV_STNCountValues for @pFromStore
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)
IF @AWBNo = 0
BEGIN
--IF CAST(@checkSTN as BIGINT) = 0
IF @checkSTN = 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
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
UPDATE GV_STNCountValues
SET CountValue = CountValue + 1
WHERE StoreCode = @pFromStore
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
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)
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'same ' + @pstn
END
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
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
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'awb ' + @pSTN
END
RETURN @pSTN
--END
If you can correct the TOP(n) queries with ORDER BY, or better still MIN or MAX, it will clarify your intent. That in turn would make it much easier to rewrite this lot. It's well worth it.
Thanks Chris,
But the STN no is generation is based on FromStoreCode not random numbers....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2013 at 7:14 am
kapil_kk (5/7/2013)
ChrisM@Work (5/7/2013)
I'm sure there's an easier (and safer) way of doing this.
ALTER PROCEDURE [dbo].[BS_GetSTNValue]--DECLARE
@pFromStore int = 1001,
@pToStore int = 1004,
@pSTN VARCHAR(20) = 0 output
AS
--BEGIN
DECLARE
@count INT = 0,
@AWBNo VARCHAR(10) = 0,
@checkSTN varchar(10)
DECLARE @oldSTN VARCHAR(10)
------------------------------------------------------------------
-- If there isn't a row in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- with a blank AWBNo (NULL or 0) then
-- create one, inserting only @pFromStore and @pToStore
IF NOT EXISTS (
SELECT 1
FROM GV_STNDetails gs
WHERE gs.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
-------------------------------------------------------------------
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
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
-------------------------------------------------------------------
-- Get a random AWBNo from GV_STNDetails
-- matching on @pFromStore and @pToStore
SET @AWBNo = (
SELECT TOP 1 ISNULL(gs.AWBNo, 0)
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @AWBNo
-------------------------------------------------------------------
-- Get the CountValue from GV_STNCountValues for @pFromStore
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)
IF @AWBNo = 0
BEGIN
--IF CAST(@checkSTN as BIGINT) = 0
IF @checkSTN = 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
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
UPDATE GV_STNCountValues
SET CountValue = CountValue + 1
WHERE StoreCode = @pFromStore
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
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)
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'same ' + @pstn
END
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
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
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'awb ' + @pSTN
END
RETURN @pSTN
--END
If you can correct the TOP(n) queries with ORDER BY, or better still MIN or MAX, it will clarify your intent. That in turn would make it much easier to rewrite this lot. It's well worth it.
Thanks Chris,
But the STN no is generation is based on FromStoreCode not random numbers....
As long as you are using TOP without ORDER BY, you could get any FromStoreCode which is within scope of the WHERE clause. That's what I mean by random.
Use MAX() or MIN(), or use TOP with ORDER BY.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 7, 2013 at 11:53 pm
ChrisM@Work (5/7/2013)
kapil_kk (5/7/2013)
ChrisM@Work (5/7/2013)
I'm sure there's an easier (and safer) way of doing this.
ALTER PROCEDURE [dbo].[BS_GetSTNValue]--DECLARE
@pFromStore int = 1001,
@pToStore int = 1004,
@pSTN VARCHAR(20) = 0 output
AS
--BEGIN
DECLARE
@count INT = 0,
@AWBNo VARCHAR(10) = 0,
@checkSTN varchar(10)
DECLARE @oldSTN VARCHAR(10)
------------------------------------------------------------------
-- If there isn't a row in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- with a blank AWBNo (NULL or 0) then
-- create one, inserting only @pFromStore and @pToStore
IF NOT EXISTS (
SELECT 1
FROM GV_STNDetails gs
WHERE gs.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
-------------------------------------------------------------------
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
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
-------------------------------------------------------------------
-- Get a random AWBNo from GV_STNDetails
-- matching on @pFromStore and @pToStore
SET @AWBNo = (
SELECT TOP 1 ISNULL(gs.AWBNo, 0)
FROM GV_STNDetails gs
WHERE gs.From_StoreCode = @pFromStore
AND gs.TO_StoreCode = @pToStore
)
PRINT @AWBNo
-------------------------------------------------------------------
-- Get the CountValue from GV_STNCountValues for @pFromStore
SET @count = (
SELECT gsv.CountValue
FROM GV_STNCountValues gsv
WHERE gsv.StoreCode = @pFromStore
)
IF @AWBNo = 0
BEGIN
--IF CAST(@checkSTN as BIGINT) = 0
IF @checkSTN = 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
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
UPDATE GV_STNCountValues
SET CountValue = CountValue + 1
WHERE StoreCode = @pFromStore
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
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)
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo is blank (NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'same ' + @pstn
END
END
ELSE
BEGIN
-- Get a random STNNO from GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
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
-- assign the random STNNO to all rows in GV_STNDetails
-- matching on @pFromStore and @pToStore
-- where AWBNo has a value (not NULL or 0)
UPDATE GV_STNDetails
SET STNNO = @pSTN
WHERE From_StoreCode = @pFromStore
AND TO_StoreCode = @pToStore
AND ISNULL(AWBNo,0) = 0
PRINT 'awb ' + @pSTN
END
RETURN @pSTN
--END
If you can correct the TOP(n) queries with ORDER BY, or better still MIN or MAX, it will clarify your intent. That in turn would make it much easier to rewrite this lot. It's well worth it.
Thanks Chris,
But the STN no is generation is based on FromStoreCode not random numbers....
As long as you are using TOP without ORDER BY, you could get any FromStoreCode which is within scope of the WHERE clause. That's what I mean by random.
Use MAX() or MIN(), or use TOP with ORDER BY.
Ok thanks I will do modification to my script....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply