Using stored procedure result set in another stored procedure

I've been developing a stored procedure that uses a user defined
function in the query portion of the procedure. However, since the end
product needs to allow for dynamic table names, the UDF will not work.
I've been trying to get this to work with converting the UDF to a
procedure, but I'm having no luck.

Here is the background on what I'm trying to accomplish. I need to
perform a sub-identity on a table, I have the normal identity set, but
there are multiple duplicates in the table and I need each set of
duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup
2).

Here is what I have using the UDF (the UDF returns a variable table
with indetity and ID for each record)

********** UDF *************

CREATE FUNCTION dbo.setDuplicateTransactions(@accountNumber as
varchar(50))
RETURNS @dupTransactions TABLE
(
ID int IDENTITY,
transactionID int
)
AS

BEGIN

INSERT @dupTransactions
SELECT t1.transactionID
FROM providerTransactions t1
WHERE t1.accountNumber = @accountNumber
ORDER BY t1.transactionID

RETURN

END

******** Stored Procedure ************

CREATE PROCEDURE dbo.sp_parseTransactions

AS

DECLARE @accountNumber varchar(50)

DECLARE temp_cursor CURSOR FORWARD_ONLY FOR
SELECT t1.accountNumber
FROM providerTransactions t1
GROUP BY t1.accountNumber
HAVING MAX(isNull(t1.duplicateCount,0)) != COUNT(t1.transactionID)
ORDER BY t1.accountNumber

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @accountNumber

WHILE @@FETCH_STATUS = 0

BEGIN
UPDATE providerTransactions
SET duplicateCount = t1.ID
FROM setDuplicateTransactions(@accountNumber) t1,
providerTransactions t2
WHERE t1.transactionID = t2.transactionID

FETCH NEXT FROM temp_cursor
INTO @accountNumber

END

CLOSE temp_cursor
DEALLOCATE temp_cursor


Now this does work and accomplishes what I want. But as mentioned, I
need to make the tables dynamic which will require the UDF to be
eliminated and either the UDF portion moved to the stored procedure or
create another stored procedure to be called by this one.

I've tried moving the UDF into the procedure, however, the identities
did not reset on the subsequent loops. If there is a way to reset the
table variable, that would be a big help.

I did move the UDF to a stored procedure, then tried to tie it into the
main procedure.

**** UDF as Stored Procedure ******

CREATE PROCEDURE dbo.sp_setDuplicateTransactions
@accountNumber as varchar(50)
AS


DECLARE @dupTransactions TABLE
(
ID int IDENTITY,
transactionID int
)

BEGIN


INSERT @dupTransactions
SELECT t1.transactionID
FROM providerTransactions t1
WHERE t1.accountNumber = @accountNumber
ORDER BY t1.transactionID

END

Here is the snippet of code replacing the current UPDATE query.

DECLARE @dupTransactions sysname
EXECUTE @dupTransactions = sp_setDuplicateTransactions @accountNumber

EXEC('
UPDATE providerTransactions
SET duplicateCount = t1.ID
FROM ' + @dupTransactions + ' t1, providerTransactions t2
WHERE t1.transactionID = t2.transactionID
')

When I run the main stored procedure I get the following error when I
stop running it.

(1 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '0'.

(1 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '0'.

(1 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '0'.

(1 row(s) affected)

So any help would be greatly appreciated.

Thanks,
Marc

.

Relevant Pages

  • Re: Interview Questions Feb 01 2006
    ... > What's the difference between Stored Procedure and User Defined ... in ORACLE the UDF can have OUT parameters. ... > SP's can change some of the server environment. ...
    (comp.programming)
  • Re: VIEW showing result of a STORED PROCEDURE ?
    ... There are similarities and differences between UDF and SP. ... from (select distinct EmployeeID from Orders)derived ... Can you create a user function and leave it on> the system like a stored procedure to be used within views or is it more> like a temporary object, like a temporary table, functions are always> temporary? ... >> create proc usp ...
    (microsoft.public.sqlserver.programming)
  • sp_ExecuteSQL in User Defined Function
    ... I need to create a UDF that would return a TRUE/FALSE value based ... 'DataTypeID', DataTypeID) ... and stored procedure: ... I get the same error message about only being able to execute functions ...
    (comp.databases.ms-sqlserver)
  • Re: Suggestions
    ... use a simple recursive scalar udf. ... > work because I can't use cursors in a UDF. ... > Select that includes a stored procedure with params like a sub-select... ... > James Hancock ...
    (microsoft.public.sqlserver.programming)
  • Re: function + text problem
    ... so as far as I know you cannot user TEXT datatype as a ... parameter neither stored procedure nor udf. ... I'd go with creating stored procedure but use the body of the email as local ... > sender, isSent, subject and body, where body needs to be ...
    (microsoft.public.sqlserver.programming)