SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help Creating a Loop for Dynamic Stored Procedure


Need Help Creating a Loop for Dynamic Stored Procedure

Author
Message
David92595
David92595
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 145
Hello,

I have a tracking table (Bankruptcy_Tracking) that tracks when steps are completed in a process. The completion of one step will then trigger the creation of a follow up date for the next step(s). The template used for setting the follow up dates in a another table (Bankruptcy_Tracking_Config). My code which is normally in a stored procedure works great when there is only one follow up date to set. However, when there are multiple I receive the follow error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
I know the problem is when I am setting the value of @Step_ID, and subsequently @Days. I'm looking for the best way to set this up with a loop. My knowledge and experience with loops is rather lacking.
** I set the @var variables as an easy way to convert the INT parameters to varchar. I like to only accept INT parameters as a way to limit exposure to SQL injections.

Thank you!!
David92595

CREATE TABLE Bankruptcy_Tracking_Config
([Step_ID] int, [Step] varchar(26), [Process_ID] int, [Process] varchar(11), [Process_Order] int, [Trigger_ID] int, [Days] int)
;

INSERT INTO Bankruptcy_Tracking_Config
([Step_ID], [Step], [Process_ID], [Process], [Process_Order], [Trigger_ID], [Days])
VALUES
(134, 'Objection Deadline Date', 16, 'BK Advesary', 1, 0, 0),
(135, 'Obtain BK Docs', 16, 'BK Advesary', 2, 0, 0),
(136, 'Obtain Loan Docs', 16, 'BK Advesary', 3, 0, 0),
(137, 'Response Drafted', 16, 'BK Advesary', 4, 2, 0),
(138, 'Response Approved by Attorney', 16, 'BK Advesary', 5, 4, 0),
(139, 'Response Filed', 16, 'BK Advesary', 6, 5, 0),
(140, 'Response Served', 16, 'BK Advesary', 7, 6, 0),
(141, 'Hearing Prep', 16, 'BK Advesary', 8, 9, -7),
(142, 'Hearing Date', 16, 'BK Advesary', 9, 7, 5),
(143, 'Hearing Results to Client', 16, 'BK Advesary', 10, 9, 1),
(144, 'Prepare and File Order', 16, 'BK Advesary', 11, 10, 1),
(145, 'Order Entered', 16, 'BK Advesary', 12, 11, 1),
(146, 'Order Provided to Client', 16, 'BK Advesary', 13, 12, 0)
;


CREATE TABLE Bankruptcy_Tracking_Config
([ID] int, [TS] int, [Step] varchar(26), [Completed] datetime, [Follow_Up] datetime, [Process] varchar(11), [Process_ID] int, [Process_Order] int, [Instance] int)
;

INSERT INTO Bankruptcy_Tracking_Config
([ID], [TS], [Step], [Completed], [Follow_Up], [Process], [Process_ID], [Process_Order], [Instance], [Timestamp])
VALUES
(28, 111111, 'Objection Deadline Date', NULL, NULL, 'BK Advesary', 16, 1, 1),
(29, 111111, 'Obtain BK Docs', NULL, NULL, 'BK Advesary', 16, 2, 1),
(30, 111111, 'Obtain Loan Docs', NULL, NULL, 'BK Advesary', 16, 3, 1),
(31, 111111, 'Response Drafted', NULL, NULL, 'BK Advesary', 16, 4, 1),
(32, 111111, 'Response Approved Attorney', NULL, NULL, 'BK Advesary', 16, 5, 1),
(33, 111111, 'Response Filed', NULL, NULL, 'BK Advesary', 16, 6, 1),
(34, 111111, 'Response Served', NULL, NULL, 'BK Advesary', 16, 7, 1),
(35, 111111, 'Hearing Prep', NULL, '2018-02-15 00:00:00.000', 'BK Advesary', 16, 8, 1),
(36, 111111, 'Hearing Date', '2018-02-14 00:00:00.000', NULL, 'BK Advesary', 16, 9, 1),
(37, 111111, 'Hearing Results to Client', NULL, '2018-02-15 00:00:00.000', 'BK Advesary', 16, 10, 1),
(38, 111111, 'Prepare and File Order', NULL, NULL, 'BK Advesary', 16, 11, 1),
(39, 111111, 'Order Entered', NULL, NULL, 'BK Advesary', 16, 12, 1),
(40, 111111, 'Order Provided to Client', NULL, NULL, 'BK Advesary', 16, 13, 1)
;


Declare @TS NUMERIC = 111111
,@Process_ID INT = 16
,@Process_Order INT = 9
,@Completed DATE = Convert(Date,Getdate())


Declare @Step_ID INT = (Select Step_ID FROM Bankruptcy_Tracking_Config WHERE Trigger_ID = @Process_Order AND Process_ID = @Process_ID)
Declare @Days as varchar(3) = (Select [Days] from Bankruptcy_Tracking_Config where Step_ID = @Step_ID),
@varProcess_Order as varchar(2) = CONVERT(varchar(2),@Process_Order),
@varProcess_ID as varchar(2) = CONVERT(varchar(2),@Process_ID),
@varTS as varchar(10) = Convert(varchar(10), @TS)
Declare @Function as varchar(30) = Case WHEN @Days < 0 THEN 'dbo.fn_SubtractBusinessDays' Else 'dbo.fn_AddBusinessDays' END

DECLARE @Run nvarchar(1000),
@ParamDefCompleted nvarchar(25)


Set @Run = 'update Bankruptcy_Tracking
SET Follow_Up = CONVERT(DATE,' + @Function + '(CONVERT(nvarchar(10),@Completed),' + @Days + ')) FROM Bankruptcy_Tracking as BT LEFT OUTER JOIN Bankruptcy_Tracking_Config as BTC on BT.Process_ID = BTC.Process_ID and BTC.Process_Order = BT.Process_Order
WHERE BTC.Process_ID = ' + @varProcess_ID + ' AND BTC.Trigger_ID = ' + @varProcess_Order + ' AND TS = ' + @varTS

--For Testing Purposes. Print the change before you make it.
--Print @Run

SET @ParamDefCompleted = N'@Completed nvarchar(10)'

Execute sp_executesql @Run, @ParamDefCompleted, @Completed

drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39653 Visits: 14389
You say you want to create records, but your code is updating records.

Also, I see absolutely no reason to use dynamic SQL here. Because you are using dynamic SQL, you are forcing an iterative approach, which causes problems. If you just start out with standard SQL, you can use a set-based approach and won't have an issue when there are multiple records to update.

Finally, why do you have two separate functions that presumably do the same thing? Subtracting a number is exactly the same as adding the negative of that same number.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
David92595
David92595
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 145
Apologies, the template is created via another action. I need to update the data.

The two separate function are used in conjunction with our holidays table. They are also used in determining the roll of the date set when a follow up date lands on a weekend or holiday. should the date be set for the Friday before the weekend or the Monday after...

I've taken your advise and removed the Dynamic SQL, however, I'm still having a problem using the AddBusinessDays and SubtractBusinessDays functions. Any idea's?


   
--@Function wont run as @days is not longer declared
Declare @Function as varchar(30) = Case WHEN @Days < 0 THEN 'dbo.fn_SubtractBusinessDays' Else 'dbo.fn_AddBusinessDays' END

update Bankruptcy_Tracking
SET Follow_Up = CONVERT(DATE,@Function(@Completed,BTC.[Days])) FROM Bankruptcy_Tracking as BT LEFT OUTER JOIN Bankruptcy_Tracking_Config as BTC on BT.Process_ID = BTC.Process_ID and BTC.Process_Order = BT.Process_Order
WHERE BTC.Process_ID = @Process_ID AND BTC.Trigger_ID = @Process_Order AND TS = @TS

drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39653 Visits: 14389
David92595 - Wednesday, February 14, 2018 12:24 PM
Apologies, the template is created via another action. I need to update the data.

The two separate function are used in conjunction with our holidays table. They are also used in determining the roll of the date set when a follow up date lands on a weekend or holiday. should the date be set for the Friday before the weekend or the Monday after...

I didn't ask what they did, I asked why you had two that presumably did exactly the same thing.

You also didn't mention anything about the dynamic SQL. I've rewritten your query using standard SQL.

UPDATE BT
SET Follow_Up = CASE WHEN [Days] < 0 THEN dbo.fn_SubtractBusinessDays(@Completed, [Days]) ELSE dbo.fn_AddBusinessDays(@Completed, [Days]) END
FROM #Bankruptcy_Tracking BT
LEFT OUTER JOIN #Bankruptcy_Tracking_Config AS BTC
ON BT.Process_ID = BTC.Process_ID
AND bt.Process_Order = BTC.Process_Order
WHERE BTC.Process_ID = @Process_ID
AND BTC.Trigger_ID = @Process_Order
AND BT.TS = @TS


Drew

PS: There were several issues with your sample data.
1) You had two different tables with exactly the same name.
2) Your insert clause named a column that wasn't in your table.
3) The number of columns in your insert clause didn't match the number of columns in your values clause

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
David92595
David92595
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 145
Thank you for your assistance.

Sorry about the bad data. I took out the timestamp thinking it would simplify my example, obviously forgot to take it out of the table.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search