August 18, 2010 at 12:28 pm
I have a stored procedure that is locked, it needs two variables to run. one is numeric, the other I can pass a null. How can I get it to loop through a list of numbers
SQL2008 server
August 18, 2010 at 12:40 pm
There is simply not enough information to give any advice.
What do you mean by "is locked"? What is the purpose of the procedure? What is the structure of that "list of numbers"? Is it a table, a file to be loaded?
Usually there is no need to "loop through a list". In almost any cases it can be done in one path, avoiding a row-based solution.
So, please help us help you by providing more detailed information.
August 18, 2010 at 12:47 pm
locked - meaning it's locked with the little lock icon, I can not modify it. only run it
(i guess the correct term would be the procedure was created with Encryption On)
it's purpose is to get client payment history, but 1 at a time.
i do not have any format for the list yet, since i don't know how to do it. i can make a file, cut and paste, etc.. whatever it needs to work
August 18, 2010 at 2:13 pm
The lock icon basically means you don't have the permission to view or modify it.
I'd recommend you get someone with the proper permission asking him to give you a function (inline-table valued function preferred) with the same input and returning the same result (basically rewriting the stored procedure as a function).
This way you wouldn't need to write a loop.
But it all depends on how many clients you have on your list (which actually should be a table...). If there are just a few and it's a one-time requirement then the effort of rewriting the proc might not be worth it. But if it's an online app where the user can select numerous clients (e.g. by region) and the app is going to be used frequently, then a rewrite is highly recommended.
I'd recommend you talk to one of your DBA's describing your business requirement and ask for assistance/support. Your DBA on site should be a much better resource since he (should) know what the best solution for your given situation will be.
August 18, 2010 at 2:20 pm
well basically I am the DBA (although uneducated in the ways of the SQL). the database is part of custom software we purchased. and yes the will give us the code for the procedure, but as with every little thing they want to charge us a gazillion dollars. so I was just trying to work around their constraints. thanks anyway
August 18, 2010 at 5:16 pm
I didn't know you're trying to add some value to a 3rd party app ... 😉
Do you know the table structure and how you need to query it to get the data you need or do you have to use the sproc? What are the answers to the rest of my questions (how many clients, are those clients already stored in a table, purpose of the sproc you want to write etc.)?
August 18, 2010 at 8:38 pm
taz, I guess the Stored Procedure with a "lock" icon is a CLR procedure.. Just check the definition of the SP....
August 20, 2010 at 9:45 am
the data is spread across a bunch of tables and calculations. quite a bit over my head in sql. a couple of tables, and multiple joins is as far as I have learned so far.
August 20, 2010 at 2:07 pm
Ok, here's an example on how to do it. Howver, I strongly recommend you get the vendor involved providing you a function with the same functionality (iTVF preferred...).
This would not only make coding a lot easier, it would also help performance. I strongly recommend against running that code using thousand or even a few hundred of ContactId's in the #numbers table (or anyy equivalent you're going to use).
This will most probably impact performance nad as soon as your vendure gets aware of that code you might not get any help from them improving performance.
Therefore voted againsta loop and a stored procedure to begin with.
And here's a test scenario based on AdventureWorks database:
-- create a dummy procedure for demonstration purpose using AdventureWorks database
CREATE PROC #dummyLutzM (@id INT)
AS
SELECT
ContactId,
FirstName,
LastName,
Title,
EmailAddress,
Phone
FROM AdventureWorks.person.Contact
WHERE contactID=@id
GO
-- create the structure of the target table
SELECT CAST(ContactId AS INT) ContactId,
CAST(FirstName AS NVARCHAR(50)) AS FirstName,
CAST(LastName AS NVARCHAR(50)) AS LastName,
CAST(Title AS NVARCHAR(8)) AS Title,
CAST(EmailAddress AS NVARCHAR(50)) AS EmailAddress,
CAST(Phone AS NVARCHAR(25)) AS Phone
INTO #test
FROM AdventureWorks.person.Contact
WHERE 1=0
-- create and populate a table holding the numbers that will be used in the loop
SELECT *
INTO #numbers
FROM
(
SELECT 1 AS ContactId UNION ALL
SELECT 2
) x
-- define the c.u.r.s.o.r.
DECLARE cur CURSOR LOCAL fast_forward FOR
SELECT ContactId FROM #numbers
ORDER BY ContactId
-- .. and local variable to hold the ContactId values
DECLARE @id INT
-- the actual loop
OPEN cur
FETCH NEXT FROM cur INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #test
EXEC #dummyLutzM @id
FETCH NEXT FROM cur INTO @id
END
-- loop cleanup
CLOSE cur
DEALLOCATE cur
-- show results
SELECT *
FROM #test
-- table and sproc cleanup
DROP TABLE #test
DROP TABLE #numbers
DROP PROC #dummyLutzM
August 20, 2010 at 2:38 pm
Simple solution would be cursors that iterates through client ids...
If there are a lot of clients to retrieve payment histories for, and cursors are too slow, you can try a while loop by giving each row a row number.
eg.
Table: Client
RowNum ClientID
1 2354
2 92783
3 1234
SELECT @currRowNum = MAX(RowNum) FROM Client
WHILE(@currRowNum > 0)
BEGIN
SELECT @currClientID = ClientID WHERE RowNum = @currRowNum
<your stored procedure> @currClientID
-- You might want to store the result in a temp table
SET @currClientID = @currClientID - 1
END
Either way, I would suggest writing a wrapper stored procedure that takes a table valued parameter (SQL 2008)
August 20, 2010 at 3:09 pm
@supachoi:
Replacing a c.u.r.s.o.r with a while loop due to performance reasons is like suggesting to cure cancer with AIDS.
Both solutions (loop and cursor) are row based (aka RBAR) won't give a significant performance difference when compared to a set based solution (where a set based solution is possible).
Side note: your code won't run as expected since you're decrementing your target variable @currClientID instead of your loop control variable @currRowNum resulting in an infinite loop...
August 20, 2010 at 3:22 pm
Oops...copy and pasted too fast 😛
I, of course, meant to decrement @currRowNum.
Regarding the while loop suggestion, I was merely suggesting an alternate, potentially more memory efficient solution...not the best solution, which of course is a set based solution, as you mentioned.
From reading _taz_'s post, it just seemed like a set based solution was an unlikely possibility.
August 20, 2010 at 3:38 pm
So let's asume the given scenario (no set based solution possible and a unchangeable sproc).
Do you have any proof of concept available that a while loop would perform better than a c.u.r.s.o.r.?
Your while loop would have to perform one select statement against your client table per loop. A c.u.r.s.o.r. would select the data just once and store it in memory.
You're probably right that storing 1000 client id's in memory (consuming 4kB + some overhead) vs. selecting 1 clientID per loop will be less memory consuming. But did you also consider I/O, reads and execution time? If you can't afford 4k of memory for a rather short period of time I guess you'll have a lot more to be concerned about than this one query...
To summarize it: it would be great if you could provide a test scenario to support your argument... 😉
August 20, 2010 at 4:19 pm
Sorry, I can't provide a test scenario...
But from personal experience, I have seen performance improvements when rewriting slow cursor queries with while loops. And of course, vice versa. I'm afraid this is the best I can do for the moment...
supachoi (8/20/2010)
Simple solution would be cursors that iterates through client ids...If there are a lot of clients to retrieve payment histories for, and cursors are too slow, you can try a while loop by giving each row a row number.
Having read your byte calc and realizing memory is not an issue in this case, I would like quietly take back
If there are a lot of clients to retrieve payment histories for
😀
Back to _taz_'s issue...
If set base solution is not an option, give cursors a try.
If that's too slow, try while loops...as this is the only other row based solution. It won't hurt a bit, I promise 🙂
Good luck!
August 23, 2010 at 2:58 pm
brucla (8/20/2010)
Simple solution would be cursors that iterates through client ids...If there are a lot of clients to retrieve payment histories for, and cursors are too slow, you can try a while loop by giving each row a row number.
eg.
Table: Client
RowNum ClientID
1 2354
2 92783
3 1234
SELECT @currRowNum = MAX(RowNum) FROM Client
WHILE(@currRowNum > 0)
BEGIN
SELECT @currClientID = ClientID WHERE RowNum = @currRowNum
<your stored procedure> @currClientID
-- You might want to store the result in a temp table
SET @currClientID = @currClientID - 1
END
Either way, I would suggest writing a wrapper stored procedure that takes a table valued parameter (SQL 2008)
hey I tried this one i called my table tempbc1
Declare @currRowNum int
Declare @currClientID int
SELECT @currRowNum = MAX(RowNum) FROM tempbc1
WHILE(@currRowNum > 0)
BEGIN
SELECT @currClientID = ClientID WHERE RowNum = @currRowNum
exec Get_ClientDisbursals @currClientID
-- You might want to store the result in a temp table
SET @currClientID = @currClientID - 1
END
I get error on line 8 invalid column name for both columns. that is what I named the columns. if I query the table directly by those names it works. what am i missing?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply