August 4, 2015 at 4:06 am
Hi All,
I have a stored procedure that outputs a variable in result.
In stored procedure code i have 2 loops, i need to exit the whole stored procedure to avoid unneeded looping in case a condition is fulfilled inside the inner loop.
If use the break clause it just exits the inner loop while i need it to exit the whole stored procedure while if i use the return clause, the output parameter is not returned correctly.
I hope you guys help with this as i searched a lot and couldn't find solution.
Below is code for stored procedure.
Thanks in advance
Nader
ALTER PROC [dbo].[CheckNameAvailability]
@RequestID int,
@NewTradeName varchar(200),
@SameDifferent int,
@DuplicateName varchar(200) = NULL OUTPUT
AS
set nocount on
Declare @GenIDStr As varchar(500)
Declare @CurrentTrade As varchar(200)
Declare @TotLen As int
Declare @CurrentIndex As int
Declare @PartOfName As varchar(10)
Declare @SQL as varchar(200)
Declare @WordLen as int
select @GenIDStr = dbo.GetRequestGenericId (@RequestID)
if @SameDifferent =1
begin
SET @SQL= 'DECLARE Names CURSOR FOR select distinct trade_name from TradeDrug group by trade_name,tradecode having dbo.GetDrugGenericId(tradecode) =''' + @GenIDStr + ''''
SET @WordLen = 3
end
else
begin
SET @SQL= 'DECLARE Names CURSOR FOR select distinct trade_name from TradeDrug group by trade_name,tradecode having dbo.GetDrugGenericId(tradecode) <>''' + @GenIDStr + ''''
SET @WordLen = 4
end
EXEC (@SQL)
OPEN Names
FETCH Names INTO @CurrentTrade
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotLen = Len(@NewTradeName)
set @CurrentIndex=1
while @CurrentIndex < @TotLen - 1
BEGIN
Set @PartOfName = SUBSTRING(@NewTradeName, @CurrentIndex, @WordLen)
If Len(@PartOfName) = @WordLen
If CHARINDEX(@PartOfName, @CurrentTrade,1) > 0
BEGIN
SET @DuplicateName = @CurrentTrade
CLOSE Names
DEALLOCATE Names
return
END
SET @CurrentIndex =@CurrentIndex + 1
End
FETCH NEXT FROM Names
INTO @CurrentTrade
END
CLOSE Names
DEALLOCATE Names
August 5, 2015 at 3:06 am
Hi Guys,
The post got many view and no replies, is the issue description not clear or what?.
If not clear please let me know to rephrase it.
Thanks Nader
August 5, 2015 at 6:21 am
You can use Label: and goto statement to redirect it to the end of the stored procedure
August 5, 2015 at 6:44 am
Thanks for your reply, i will try that.
August 5, 2015 at 7:25 am
I was going to suggest something like using FETCH LAST and BREAK to control your WHILE loops.
However, I wanted to get a little bit further and suggest a set-based option.
This uses an inline tally or numbers table in the form of a CTE. It's untested as I don't have anything to test against, but it might work just fine.
I'm still worried about the performance of using scalar UDFs on the WHERE clause, but there's nothing I can do unless you share some code, with sample data.
ALTER PROC [dbo].[CheckNameAvailability]
@RequestID int,
@NewTradeName varchar(200),
@SameDifferent int,
@DuplicateName varchar(200) = NULL OUTPUT
AS
set nocount on
Declare @GenIDStr As varchar(500)
select @GenIDStr = dbo.GetRequestGenericId (@RequestID)
if @SameDifferent =1
begin
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (Len(@NewTradeName) - 3) ROW_NUMBER() OVER(ORDER BY NULL)
FROM E a, E b, E c --6*6*6=216
)
select TOP 1
@DuplicateName = trade_name
from TradeDrug td
CROSS
JOIN cteTally ct
WHERE dbo.GetDrugGenericId(tradecode) = @GenIDStr
AND CHARINDEX(SUBSTRING(@NewTradeName, n, 3), @CurrentTrade) > 0
--ORDER BY Something?
;
end
else
begin
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (Len(@NewTradeName) - 4) ROW_NUMBER() OVER(ORDER BY NULL)
FROM E a, E b, E c --6*6*6=216
)
select TOP 1
@DuplicateName = trade_name
from TradeDrug td
CROSS
JOIN cteTally ct
WHERE dbo.GetDrugGenericId(tradecode) <> @GenIDStr
AND CHARINDEX(SUBSTRING(@NewTradeName, n, 4), @CurrentTrade) > 0
--ORDER BY Something?
;
end
August 5, 2015 at 9:04 am
I agree with Luis, the issue is that you are using loops in the first place. I didn't have time to try to rewrite it without using loops, so I didn't respond.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 6, 2015 at 6:00 am
Hi guys,
Thanks for your replies, i have attached the script to create related tables and sample data.
Luis
Not sure exactly what your code is doing, if appreciate if you can give more illustration please .
Best Regards
Nader
August 6, 2015 at 7:57 am
As I was reviewing my code, I found a mistake. I'm including a corrected version filled with comments on what the code is doing.
Basically, I'm removing the cursor because it's not needed to traverse a table, but it's commonly used when we think of what we want to do with each row. The first step to set-based programming (the principle for SQL) is to stop thinking what do you want to do to a row and start thinking what do you want to do with a column.
Then, I'm removing the inner while loop that simply counts from one to the length of the parameter. To do this, I replace it with a Tally (or numbers) table. To know what it is and how it replaces a loop, read the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/ or this one: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/ or you can search on the internet for more information.
I still can't test the code because you gave lots of data but no code for the functions and no information on what the values of the parameters look like.
ALTER PROC [dbo].[CheckNameAvailability]
@RequestID int,
@NewTradeName varchar(200),
@SameDifferent int,
@DuplicateName varchar(200) = NULL OUTPUT
AS
set nocount on
Declare @GenIDStr As varchar(500)
select @GenIDStr = dbo.GetRequestGenericId (@RequestID) --This was on your code.
if @SameDifferent =1
begin
--Starting here
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (CASE WHEN Len(@NewTradeName) >= 3 THEN Len(@NewTradeName) - 2
ELSE 0 END) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) --Validate for strings at least 3 char long
FROM E a, E b, E c --6*6*6=216
) --Ending here will create a CTE (Common table expression or inline view or subquery factoring in Oracle)
--This will return the number of rows needed to traverse the whole contents of @NewTradeName
--The limit is 216 because the max length of @NewTradeName is 200.
select TOP 1
@DuplicateName = trade_name --Return the first trade name (add an order by to ensure that you'll always return the same value)
from TradeDrug td
CROSS
JOIN cteTally ct --Uses the CROSS JOIN to create a row with each 3 character part of the @NewTradeName value. (Same thing as your inner loop)
WHERE dbo.GetDrugGenericId(tradecode) = @GenIDStr --Condition on your CURSOR definition (this can be changed to a faster option by removing the UDF)
AND trade_name LIKE '%' + SUBSTRING(@NewTradeName, n, 3) + '%' --Validate that the 3 character part is in the trade_name column
--ORDER BY Something?
;
end
else
begin
--Same as previous but with different conditions.
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (CASE WHEN Len(@NewTradeName) >= 4 THEN Len(@NewTradeName) - 3
ELSE 0 END) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E a, E b, E c --6*6*6=216
)
select TOP 1
@DuplicateName = trade_name
from TradeDrug td
CROSS
JOIN cteTally ct
WHERE dbo.GetDrugGenericId(tradecode) <> @GenIDStr
AND trade_name LIKE '%' + SUBSTRING(@NewTradeName, n, 4) + '%'
--ORDER BY Something?
;
end
August 6, 2015 at 6:23 pm
Hi Luis,
Thanks again for your replies.
Please find attached file containing the needed scripts.
Best Regards
Nader Galal
August 6, 2015 at 6:29 pm
nadersam (8/6/2015)
Hi Luis,Thanks again for your replies.
Please find attached file containing the needed scripts.
Best Regards
Nader Galal
No file attached.
August 7, 2015 at 7:08 pm
sorry for that
August 9, 2015 at 3:40 am
Hi Luis,
Please bare with me, i am new with tally tables programming, i was reading about it during last few days.
In our example you specified the number of rows to be 6
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
Can you please tell me why did u specify as such.
Thank you very much
Nader
August 9, 2015 at 5:37 pm
nadersam (8/9/2015)
Hi Luis,Please bare with me, i am new with tally tables programming, i was reading about it during last few days.
In our example you specified the number of rows to be 6
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
Can you please tell me why did u specify as such.
Thank you very much
Nader
The number of rows can be any number. This time I chose to use six rows because I only needed 200 hundred rows and 6x6x6=216. It was the easiest way to get enough rows.
August 9, 2015 at 6:37 pm
Your data doesn't help that much. It allows basic tests and I can't be sure that all conditions are met.
You'll have to test this code to be sure that it works fine. I hope that someone else can take a look as it has been a while since I last coded for full equality on sets and wanted to try something that might be SARGable instead of concatenating values.
As I'm sure that you'll need to use concatenated values as you generate them in your functions, take a look at this article to get a better way of doing it. http://www.sqlservercentral.com/articles/comma+separated+list/71700/
ALTER PROC [dbo].[CheckNameAvailability2]
@RequestID int,
@NewTradeName varchar(200),
@SameDifferent int,
@DuplicateName varchar(200) = NULL OUTPUT
AS
set nocount on
Declare @GenIDStr As varchar(500)
select @GenIDStr = dbo.GetRequestGenericId (@RequestID) --This was on your code.
if @SameDifferent =1
begin
--Starting here
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (CASE WHEN Len(@NewTradeName) >= 3 THEN Len(@NewTradeName) - 2
ELSE 0 END) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) --Validate for strings at least 3 char long
FROM E a, E b, E c --6*6*6=216
) --Ending here will create a CTE (Common table expression or inline view or subquery factoring in Oracle)
--This will return the number of rows needed to traverse the whole contents of @NewTradeName
--The limit is 216 because the max length of @NewTradeName is 200.
select TOP 1
@DuplicateName = trade_name --Return the first trade name (add an order by to ensure that you'll always return the same value)
from TradeDrug td
CROSS
JOIN cteTally ct --Uses the CROSS JOIN to create a row with each 3 character part of the @NewTradeName value. (Same thing as your inner loop)
WHERE NOT EXISTS(
SELECT *
FROM
(SELECT gn.GenericName_ID
FROM TradeName_GenericName tn
INNER JOIN GenericName gn ON tn.GenericName_ID = gn.GenericName_ID
where tn.Tradename_id = td.TradeCode) a
FULL JOIN
(SELECT b.GenericName_id
FROM BoxRequestDet b
WHERE ParentKey = @RequestID ) b ON a.GenericName_ID = b.GenericName_id
WHERE a.GenericName_ID IS NULL
OR b.GenericName_id IS NULL
) --Condition on your CURSOR definition (this can be changed to a faster option by removing the UDF)
AND trade_name LIKE '%' + SUBSTRING(@NewTradeName, n, 3) + '%' --Validate that the 3 character part is in the trade_name column
--ORDER BY Something?
;
end
else
begin
--Same as previous but with different conditions.
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (CASE WHEN Len(@NewTradeName) >= 4 THEN Len(@NewTradeName) - 3
ELSE 0 END) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM E a, E b, E c --6*6*6=216
)
select TOP 1
@DuplicateName = trade_name
from TradeDrug td
CROSS
JOIN cteTally ct
WHERE EXISTS(
SELECT *
FROM
(SELECT gn.GenericName_ID
FROM TradeName_GenericName tn
INNER JOIN GenericName gn ON tn.GenericName_ID = gn.GenericName_ID
where tn.Tradename_id = td.TradeCode) a
FULL JOIN
(SELECT b.GenericName_id
FROM BoxRequestDet b
WHERE ParentKey = @RequestID ) b ON a.GenericName_ID = b.GenericName_id
WHERE a.GenericName_ID IS NULL
OR b.GenericName_id IS NULL
)
AND trade_name LIKE '%' + SUBSTRING(@NewTradeName, n, 4) + '%'
--ORDER BY Something?
;
end
August 10, 2015 at 12:51 am
Thank you very much Luis.
I appreciate your help.
I will try to read more about it to better understand this technique but it looks amazing.
Regards
Nader
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply