September 20, 2011 at 1:23 pm
Hi all, I am having some trouble parsing multiple values within two parameters. The goal is to loop through each value and execute a stored procedure for each value.
In the below sample, the current result return Actual and Open. I tried a nested loop but was not able to get the desired results. I am wondering if the best approach would be a cursor?
Any ideas?
The desired result is the following:
Open
200902
Open
200903
Actual
200902
Actual
200903
Example SQL
CREATE PROCEDURE TEST
@scenario NVARCHAR(100),
@period VARCHAR(100)
AS
BEGIN
DECLARE @spot SMALLINT, @strScenario NVARCHAR(1000), @strPeriod NVARCHAR(1000)
SET NOCOUNT ON;
--Loop Scenario
WHILE @scenario <> ''
BEGIN
SET @spot = CHARINDEX(',', @scenario)
IF @spot>0
BEGIN
SET @strScenario = CAST(LEFT(@scenario, @spot-1) AS NVARCHAR(100))
SET @scenario = RIGHT(@scenario, LEN(@scenario)-@spot)
END
ELSE
BEGIN
SET @strScenario = CAST(@scenario AS NVARCHAR(100))
SET @scenario = ''
END
--Execute proc pass @strScenario and @strPeriod
Print @strScenario
END
END
-- exec TEST @scenario=N'''OPEN'',''ACTUAL''',@Period=N'''200902'',''200903'''
Any feedback is greatly appreciated.
Mike
September 20, 2011 at 1:52 pm
Certainly a cursor is not going to help much in the performance world at all. You need to come up with a set based solution for this instead of RBAR. Take a look at Jeff Moden's awesome split[/url] function. This is the first step in making this process better. If you want to get some detailed help take a look at the link in my signature for best practices on posting questions. Then we can take your row by row by row logic and turn into a split second process set based approach.
_______________________________________________________________
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/
September 20, 2011 at 5:22 pm
Wow, thanks fellas, nice replies! I appreciate the time to review and answer the question, err- waste your time with your answers about best practices and odd references to lady gaga. But seriously this is just seems to be a basic simple exercise and in my role, I don’t usually worry about writing SQL, so thought I would reach out to a community who enjoys it on a daily basis. If you don’t know the answer, no worries, no need to reply.
Allow me to reiterate the question and perhaps there is a newbie that may want to answer or benefit from the answer:
1. An application we have will pass values to any stored procedure.
2. The goal is to split the values passed from each parameter and execute a different stored procedure which cannot consume an array.
The result I want is the following:
1. Parameters captured from application would look like: @scenario=N'''OPEN'',''ACTUAL''',@Period=N'''200902'',''200903''' (I cannot change this, it is what i have to work with).
2. The goal is to parse those values into the following executions of another procedure which also cannot be changed. The end result would be executing the procedure four times in this specific example. So I am looking for a way to effectively parse these arrays and execute a procedure passed on the combination of values contained in the arrays. Is this confusing?
- exec blahblah @scenario=N''OPEN'',@Period=N''200902''
- exec blahblah @scenario=N''OPEN'',@Period=N''200903''
- exec blahblah @scenario=N''ACTUAL'',@Period=N''200902''
- exec blahblah @scenario=N''ACTUAL'',@Period=N''200903'
September 21, 2011 at 8:08 am
Michael A. (9/20/2011)
Wow, thanks fellas, nice replies! I appreciate the time to review and answer the question, err- waste your time with your answers about best practices and odd references to lady gaga. But seriously this is just seems to be a basic simple exercise and in my role, I don’t usually worry about writing SQL, so thought I would reach out to a community who enjoys it on a daily basis. If you don’t know the answer, no worries, no need to reply.Allow me to reiterate the question and perhaps there is a newbie that may want to answer or benefit from the answer:
1. An application we have will pass values to any stored procedure.
2. The goal is to split the values passed from each parameter and execute a different stored procedure which cannot consume an array.
The result I want is the following:
1. Parameters captured from application would look like: @scenario=N'''OPEN'',''ACTUAL''',@Period=N'''200902'',''200903''' (I cannot change this, it is what i have to work with).
2. The goal is to parse those values into the following executions of another procedure which also cannot be changed. The end result would be executing the procedure four times in this specific example. So I am looking for a way to effectively parse these arrays and execute a procedure passed on the combination of values contained in the arrays. Is this confusing?
- exec blahblah @scenario=N''OPEN'',@Period=N''200902''
- exec blahblah @scenario=N''OPEN'',@Period=N''200903''
- exec blahblah @scenario=N''ACTUAL'',@Period=N''200902''
- exec blahblah @scenario=N''ACTUAL'',@Period=N''200903'
Don't mind Joe. He tends to be extremely rude online (and reportedly proud of it).
I however gave you nothing remotely snarky in my response. I suggested that a cursor is a really bad approach to your solution. A cursor is nothing more than a different way of writing a loop. The reason I suggested you look at the link in my signature is because you gave us nothing to work with to help you build a tested solution. Keep in mind that we are all volunteers on here. The best way for you get some FREE help is to help us help you. That means posting some ddl, sample data, things that make it easy for us to give you the assistance you are looking for. The last thing anybody wants to do is spend an hour setting up the scenario and tables before we begin to work on your solution.
Did you read either of the articles I suggested? I can cut to the chase with Jeff's splitter function.
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
If you read the article it explains splitting strings similar to the fashion you were using and demonstrates how and why this set based approach is WAY FASTER!!!!
So you could use this splitter as the basis for your cursor or I suspect we could dissect the proc logic and either modify that or find another way to use the existing proc in a way that makes this fast. It may be that you have to use the existing proc inside a cursor. We get that and nobody is saying it won't work but we don't know until we have more details.
Now if you would like some FREE assistance from people (including myself) who does enjoy working with SQL on a daily basis please make it easy for me (or anybody else who happens on this thread) to work on the solution to your problem and post something that might help.
_______________________________________________________________
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/
September 21, 2011 at 12:44 pm
Thanks Crazy
I do appreciate the time taken to review and reply to questions. I understand that perhaps I have not articulated the question well enough. I know what the desired end result should be and I fundamentally understand what is wrong with the sample SQL I provided but I am unable to come up with a solution that works. Thus the posting here.
I thought my original post included the SQL to understand the basic problem and hopefully my subsequent posts have made it more clear. There are no sample tables or data to provide in this exercise as I am only trying to split the arrays from two parameters passed into a stored procedure (provided). This is a snap with a single parameter but when you have two there is more of a challenge (at least for me). I have posted an updated SQL sample below. The issue is that the current WHILE logic will stop once the values within one array are exhausted. However, I need a method to essentially pivot all values within the two arrays to give the results provided in the below example.
So when I pass the arrays “OPEN, ACTUAL” and “200902,200903” I am looking to produce a matrix that would look like:
OPEN | 200902
OPEN | 200902
ACTUAL | 200902
ACTUAL | 200903
This way, I can execute another procedure for these values which does hit tables with data. If you run the SQL below, you will see the results are below. The print statement is to debug in the location in which the execute statement will eventually be placed. The first issue I can identify is that the print statement is within the period loop. If I move it outside, the results will change but of course there is additional syntax required to get this thing working the way I need it.
'OPEN' | '200902'
'OPEN' | '200903'
I hope my winded reply helps clarify the goal here.
Updated SQL example:
CREATE PROCEDURE ATEST
@scenario NVARCHAR(100),
@period VARCHAR(100)
AS
BEGIN
DECLARE @spot SMALLINT, @strScenario NVARCHAR(1000), @strPeriod NVARCHAR(1000), @spot2 SMALLINT
SET NOCOUNT ON;
WHILE @scenario <> ''
BEGIN
SET @spot = CHARINDEX(',', @scenario)
IF @spot>0
BEGIN
SET @strScenario = LEFT(@scenario, @spot-1)
SET @scenario = RIGHT(@scenario, LEN(@scenario)-@spot)
END
ELSE
BEGIN
SET @strScenario = @scenario
SET @scenario = ''
END
WHILE @period <> ''
BEGIN
SET @spot2 = CHARINDEX(',', @period)
IF @spot2>0
BEGIN
SET @strPeriod =LEFT(@period, @spot2-1)
SET @period = RIGHT(@period, LEN(@period)-@spot2)
END
ELSE
BEGIN
SET @strPeriod = @period
SET @period = ''
END
-- Execute stored procedure based on split parameters
-- EXEC rule @strScenario,@strPeriod
-- Test debug print results
Print @strScenario + ' | ' + @strPeriod
END
END
END
GO
--Test -Execute procedure to produce split params used to execute a seperate procedure
-- exec ATEST @scenario=N'''OPEN'',''ACTUAL''',@period=N'''200902'',''200903'''
So basically, the question is not how to split the arrays but how to loop through two split arrays so that I can capture every combination and execute the "real" stored procedure based on these combinations.
Thanks again,
Mike
September 21, 2011 at 1:16 pm
So basically, the question is not how to split the arrays but how to loop through two split arrays so that I can capture every combination and execute the "real" stored procedure based on these combinations.
I pretty much figured that was what you meant but using a while loop to split strings is horribly inefficient and makes things like what you are trying to do really difficult. And in your case the two "arrays" are really associated with each other. Remember sql doesn't have arrays. We have a way more powerful tool (tables) at our disposal. If you are unable or unwilling to change your stored proc to work with a table valued parameter then you will have to loop and execute.
Here is the query using the split function.
declare @Scenario varchar(50) = 'Open, Actual'
declare @Period varchar(50) = '200902,200903'
select LTRIM(a.Item) as scenario, LTRIM(b.item) as period
from dbo.DelimitedSplit8K(@Scenario, ',') a
cross join dbo.DelimitedSplit8K(@Period, ',') b
So here you can see that getting your cross join list is REALLY easy. 😉
OK so what? We have the list you were looking for but how do I use that in relation to my issue?
Well first we have to setup the existing procedure. This is the proc you want to call for each iteration.
--create dummy procedure since no example was given
create procedure CursorExample
(
@Item varchar(25),
@Period varchar(25)
) as begin
select @Item, @Period
end
OK but where are we going with this....you want your new code inside a procedure so let us create that procedure. This is the real meat and potatoes of what you are after.
--now create the procedure that you will call
create Procedure ProofExample
(
@Scenario varchar(25),
@Period varchar(25)
) as begin
declare @MyScenario varchar(50)
declare @MyPeriod varchar(50)
declare myList cursor for
select LTRIM(a.Item) as scenario, LTRIM(b.item) as period
from dbo.DelimitedSplit8K(@Scenario, ',') a
cross join dbo.DelimitedSplit8K(@Period, ',') b
open myList
fetch next from myList into @MyScenario, @MyPeriod
while @@FETCH_STATUS = 0 begin
exec CursorExample @MyScenario, @MyPeriod
fetch next from myList into @MyScenario, @MyPeriod
end
close myList
deallocate myList
end
Oh cool. This looks pretty simple so far but, all we have now is a bunch of disconnected stuff. How do I actually this to accomplish my original task which is to execute a stored proc for the cross join results of two strings each of which contain multiple values?
declare @Scenario varchar(50) = 'Open, Actual'
declare @Period varchar(50) = '200902,200903'
exec ProofExample @Scenario, @Period
This is really just an example of how to do this in a **cough**cursor**cough**. If at ALL possible change the main proc (the placeholder one I created) with some logic that can do this set based instead of this approach. If however you are unable to change that proc (because other processes use it for example) you are pretty much force to do it like this. Let me know if this makes sense. I will warn you that this approach is very likely going to be hideously slow on large tables. Cursors are just not a fast approach but sometimes decisions outside of our control forces us to use a hammer. Hope this helps.
_______________________________________________________________
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/
September 21, 2011 at 1:18 pm
Michael,
Here's some food for thought for you. Use the split function that Sean mentioned to split the strings into tables. Then join them (full outer join) and cursor through that.
something like this:
DECLARE [SetCursor]
CURSOR FORWARD_ONLY READ_ONLY
FOR
SELECT
[Period] = p.[Item],
[Scenario], s.[Item]
FROM
dbo.[DelimitedSplit8K](@Period, '|') AS p CROSS JOIN
dbo.[DelimitedSplit8K](@Scenario, '|') AS s
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM [QueueCursor]
INTO @tempPeriod, @tempScenario
IF @@FETCH_STATUS != 0 BEGIN
BREAK;
END
--exec someproc @tempPeriod, @tempScenario
END
edit: Sean beat me to the response...
September 21, 2011 at 1:22 pm
venoym (9/21/2011)
Michael,Here's some food for thought for you. Use the split function that Sean mentioned to split the strings into tables. Then join them (full outer join) and cursor through that.
something like this:
DECLARE [SetCursor]
CURSOR FORWARD_ONLY READ_ONLY
FOR
SELECT
[Period] = p.[Item],
[Scenario], s.[Item]
FROM
dbo.[DelimitedSplit8K](@Period, '|') AS p CROSS JOIN
dbo.[DelimitedSplit8K](@Scenario, '|') AS s
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM [QueueCursor]
INTO @tempPeriod, @tempScenario
IF @@FETCH_STATUS != 0 BEGIN
BREAK;
END
--exec someproc @tempPeriod, @tempScenario
END
edit: Sean beat me to the response...
I think Jeff might roll over and die if he saw either of us using his setbased splitter as the source of a cursor. :w00t:
_______________________________________________________________
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/
September 21, 2011 at 3:10 pm
Thanks again for the time guys; Sean your answer did the trick. I understand this is completely inefficient and we will be looking at changing the stored procedure which forced us to go this route as well as the application code to better handle this newer requirement. We needed some sort of stop-gap solution to get through this immediate need.
Thanks for input Venoym and of course Celko, thanks for the links to very interesting reads. I have posted one other topic and had a fantastic response from Jeff Moden and figured this is the best place to find the absolute final word on best practice approaches. I will certainly be more diligent about forming my question the next time around. 😀
Mike
September 21, 2011 at 3:15 pm
Glad you were able to find at least a temporary solution. I think you could take what you learned about how to use the Delimited split function and just add that your main proc. I don't know what the logic is inside there but I would bet it can be made set based. Then you can drop the intermediate step and just pass your delimited string to your main proc, parse it and do whatever it is you need to do in there. No worries about the format of your question. It can be a bit daunting especially if you are kind of new to providing that level of detail.
_______________________________________________________________
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/
September 21, 2011 at 3:43 pm
Sean Lange (9/21/2011)
venoym (9/21/2011)
I think Jeff might roll over and die if he saw either of us using his setbased splitter as the source of a cursor. :w00t:
Heh... nope... loading Pork Chops into my brand new 3 band Pork Chop launcher, though. 😉
I have a couple of after work appointments... If they don't take all night, I'll take a crack at this... without a cursor, of course. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2011 at 7:21 am
Michael A. (9/21/2011)
The issue is that the current WHILE logic will stop once the values within one array are exhausted.
Hmmm... been thinking about this... Sean's code does a great job on splitting and creating the Cartesian product you want for the "matrix". Are you suggesting that the two CSV parameters you're passing could have a different number of elements like this?
'Open,Closed'
'200902,200903,200904'
If that's the case, what would you like to see for the output there?
Also, what does the stored procedure you want to use these parameters on actually do? That would be the key to figuring out how to keep this all from being a RBAR exercise.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2011 at 8:57 am
Hey Jeff, that is a valid question. I realize I did not provide much context to the root of why I posted this question in the first place. I figured that was a more complex question and I did not want to introduce that element into the thread.
With that said, since you asked ;-), the "main" proc basically aggregates financial transactions to a monthly level, otherwise considered as monthly "trial Balance.” It than does complex currency translation based on different rate types and rates based on an entity and natural account. So for instance, Income statement accounts are calculated at an Avg. rate for each related month, Balance Sheet accounts are calculated at a closing rate for a specific month and historic (equity) accounts are calculated at whatever rates have been associated with those specific accounts. Currently we do this for a single month and type of data. e.g. 201001 (Jan-2010) and Actual data which is passed to the proc. However, the new business requirement is that we may want to run this procedure for multiple months at the same time or multiple scenarios, e.g. Actual data, budget, forecast, etc. The current procedure references the parameters in the where clause and changing this would require to rethink the procedure, which does need to happen but not something I wanted to deal with now. It actually goes back to a question I posted that you answered regarding rolling aggregations, which is required to calculate YTD values on for the Balance Sheet accounts.
So basically, the feedback everyone provided does do the trick. In fact, the more I thought about it, using the cursor or even while syntax is not really a big deal because usually we will only need to parse a few values to pass to this aggregation/translation proc. In other words, most times this procedure is executed, we will only pass a single scenario and period but there may be times when more values will be passed, e.g. if we need to re-run the currency translation for all periods in a year, which would mean we would pass 12 values 201001 - 201012. So not really a big deal on the performance side to split out the values and pass them to the proc. Also, if I am aggregating a million transactions each month, I think it may be more efficient to do this one month at a time as opposed to try to lump it into one big process. Of course we can change the application that passes the values to handle the loop but it will affect other business processes, so the shortest distance to a solution was to find a way to handle the "loop", in other words I am creating a middle layer to handle the looping logic between the app and proc so that nothing else needs to be changed (for now).
Anyway, probably way more information than required here, unless you would like to help redesign the existing translation script 😀
Mike
September 24, 2011 at 6:28 pm
Thanks for the great explanation and, I agree... the control loop for this would not be a performance problem. As a sidebar, man, I'd love to take a crack at the larger problem but I just don't have the time.
One more thing, though... you didn't answer my question. Is it possible for the 2 CSV parameters you're passing to have a different number of elements and, if so, what do you want to do about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply