July 10, 2014 at 11:54 am
Hello Everyone,
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@ID INT,
@Name VARCHAR (200),
@SessionID INT
)
AS
BEGIN
-- Alright!...let me show the world what my parameter values are :)
SELECT 'SessionID - ' + CAST (@SessionID AS VARCHAR (20)) + ' called MyMainStoredProc with ID: ' + CAST (@ID AS VARCHAR (20)) + '; and Name: ' + @Name
END
GO
Outer Stored Procedure: This will initiate calls to the proc above, "MyMainStoredProc"
-- All this proc needs to do is, call MyMainStoredProc once per row of the table variable within this proc
CREATE PROCEDURE dbo.MyOuterStoredProc (
@SessionID INT
)
AS
BEGIN
SET NOCOUNT ON
-- Create a dummy table (variable) with 3 dummy rows in it
DECLARE @TestTable TABLE (ID INT, Name VARCHAR (200))
INSERT @TestTable VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
-- Form a string that forms a call to MyMainStoredProc for each row of the dummy table
DECLARE @myTestSQLString NVARCHAR (MAX) = N'', @myCounter INT = 0
SELECT @myTestSQLString = @myTestSQLString + '
EXEC dbo.MyMainStoredProc @ID = ' + CAST (ID AS VARCHAR) + ', @Name = ''' + Name + ''', ' + '@SessionID = ' + CAST (@SessionID AS VARCHAR) + ';
',
@myCounter = @myCounter + 1
FROM @TestTable
-- PRINT @myTestSQLString
-- Execute the string which have multiple sp_executesql calls, once per each row of the dummy table
EXEC sp_executesql @myTestSQLString
SET NOCOUNT OFF
END
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I would appreciate your suggestions to make this a more efficient solution without trivial limitations (like the string length I have in mine).
- SC
July 10, 2014 at 11:58 am
There's no need to avoid cursors for such a thing. They're not the issue in such instances. The issue would be, what does the stored procedure do and why it has been written to support only RBAR.
My recommendation would be to spend some time on a set-based rewrite of the stored proc.
--Jeff Moden
July 10, 2014 at 1:37 pm
I agree: if you HAVE to do this one row at a time then a properly defined cursor is the correct way to do it. FAST_FORWARD or FORWARD ONLY, READ ONLY, STATIC, LOCAL.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 10, 2014 at 2:04 pm
It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.
July 10, 2014 at 9:36 pm
autoexcrement (7/10/2014)
It may also be possible to re-write your SP as a Function and use CROSS APPLY. Just thinking out loud.
That's actually a good idea if it can be written as an iTVF. Otherwise, it'll be about the same as the cursor solution.
--Jeff Moden
July 10, 2014 at 10:38 pm
I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!
July 11, 2014 at 2:31 am
Let us know a bit more about your business problem....I bet you don't need to call a stored proc in a cursory fashion like this, whether using a cursor or not! There is bound to be another way of doing what you need to do!
July 11, 2014 at 2:43 am
autoexcrement (7/10/2014)
I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!
Inline Table-valued functions are. Specifically inline, not any table valued function
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2014 at 8:54 am
autoexcrement (7/10/2014)
I thought that went without saying, but yes I should have specified! 🙂 Table-Valued Functions will be many times faster than Scalar Functions!
Apologies. I used an abbreviation that I thought everyone knew.
As Gail stated, "INLINE Table Valued Functions" (or "iTVF" for short) will be faster than Scalar Functions. There are 3 basic types of UDFs. "Scalar" (SF), "Multi-statement Table Valued" (mTVF), and "Inline Table Valued" (iTVF). SFs can be dreadfully slow. mTVFs can be much worse. iTVFs are usually much faster than either of the other two. SFs and mTVFs require a BEGIN/END around the body of the function and iTVFs do not. iTVFs are written more like a parameterized view than anything else. This is also why a lot of people say that "If your function contains a "BEGIN", there's a good chance that it's slow".
There are, of course, exceptions to every rule.
--Jeff Moden
July 12, 2014 at 4:00 am
From what I can gather, there is no need for a cursor. It can change depending on the actual work required!
😎
USE tempdb;
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyMainiTVF'
AND ROUTINE_SCHEMA = 'dbo')
DROP FUNCTION dbo.MyMainiTVF;
GO
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE FUNCTION dbo.MyMainiTVF (
@ID INT,
@Name VARCHAR (200),
@SessionID INT
)
RETURNS TABLE
AS
RETURN
-- Alright!...let me show the world what my parameter values are :)
SELECT 'SessionID - '
+ CAST (@SessionID AS VARCHAR (20))
+ ' called MyMainiTVF with ID: '
+ CAST (@ID AS VARCHAR (20))
+ '; and Name: ' + @Name AS RET_VAL
GO
/* CREATE TEST SET */
DECLARE @TestTable TABLE (ID INT, Name VARCHAR (200))
INSERT @TestTable VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
/* THE SELECT */
SELECT
XTV.RET_VAL
FROM @TestTable TT
CROSS APPLY dbo.MyMainiTVF(TT.ID,TT.Name,@@SPID) AS XTV
Results
RET_VAL
-----------------------------------------------------------
SessionID - 54 called MyMainiTVF with ID: 1; and Name: AAA
SessionID - 54 called MyMainiTVF with ID: 2; and Name: BBB
SessionID - 54 called MyMainiTVF with ID: 3; and Name: CCC
SessionID - 54 called MyMainiTVF with ID: 4; and Name: DDD
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply