Stored Procedure vs. Function
Is there any cut-and dry rule regarding performance between using a stored procedure vs. a function. I need to pass a parameter and return a single scalar value.
I can code it as a stored procedure that has one input and one output parameter, or as a function that accepts a value and returns value.
I can code it as a stored procedure that has one input and one output parameter, or as a function that accepts a value and returns value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, you're right, it will. But to call a sp for every row, *you* have to page thru the rows yourself. In other words, you *can't* do this:
SELECT col1, col2, (EXEC myProc col1)
FROM myTable
If you want to exec myProc for every column col1, you have to use a cursor or the equivalent, like so:
CREATE TABLE #results (
col1 INT,
col2 INT,
procResult VARCHAR(30)
)
DECLARE csrMyTable CURSOR FAST_FORWARD FOR
SELECT col1, col2
FROM myTable
DECLARE @col1 INT
DECLARE @col2 INT
DECLARE @procResult VARCHAR(30)
OPEN csrMyTable
FETCH NEXT FROM csrMyTable INTO @col1, @col2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC myProc @col1, @procResult OUTPUT
INSERT INTO #results
SELECT @col1, @col2, @procResult
FETCH NEXT FROM csrMyTable INTO @col1, @col2
END --WHILE
CLOSE csrMyTable
SELECT *
FROM #results
YIKES!
SELECT col1, col2, (EXEC myProc col1)
FROM myTable
If you want to exec myProc for every column col1, you have to use a cursor or the equivalent, like so:
CREATE TABLE #results (
col1 INT,
col2 INT,
procResult VARCHAR(30)
)
DECLARE csrMyTable CURSOR FAST_FORWARD FOR
SELECT col1, col2
FROM myTable
DECLARE @col1 INT
DECLARE @col2 INT
DECLARE @procResult VARCHAR(30)
OPEN csrMyTable
FETCH NEXT FROM csrMyTable INTO @col1, @col2
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC myProc @col1, @procResult OUTPUT
INSERT INTO #results
SELECT @col1, @col2, @procResult
FETCH NEXT FROM csrMyTable INTO @col1, @col2
END --WHILE
CLOSE csrMyTable
SELECT *
FROM #results
YIKES!
wow Thanks for the points.....
ASKER
Okay. Yes, I knew you can't execute a procedure within a select. Yes, yikes is correct.
ASKER
I know I closed this, but something in your answer 'puzzles' me. If I have a udf, and the following query:
SELECT col1, col2, dbo.myFunction(col1)
FROM myTable
Is this not going to call the function for every row?