Using stored procedure result set in another stored procedure

[marc]

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

[David Portas]

Please post DDL (CREATE TABLE statements), sample data (INSERT
statements), show your required end result and explain a bit more for
us. In particular, is this a one-off process to eliminate duplicates or
do you intend to use this as a mechanism to generate keys?

The problem with generating keys like this is that it requires you to
serialize your INSERT statements. In a multi-user environment that may
prove unacceptable. If you really need to do it then I suggest you
retrieve and increment the next value as part of the proc that performs
the INSERT.

Unfortunately from the sample you've given I'm not clear exactly what
is supposed to drive the sequence. If it's from insertion order then
you may be better off just to preserve the insertion DATETIME, leave
out the Count column and then derive the "row number" in a query when
you retrieve the data. Anyway, I'm certain there is a better
alternative to using a cursor and a function in this way. See if this
helps:

UPDATE Transactions
SET duplicatecount =
(SELECT COUNT(*)
FROM Transactions AS T
WHERE T.accountnumber = Transactions.accountnumber
AND T.transactionid <= Transactions.transactionid);

P.S. Don't use the "sp_" prefix for procs unless you intend to create a
system proc in Master. "sp_" is reserved for system procs and comes
with some disadvantages when applied to user procs.

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> the end product needs to allow for dynamic table names <<

Stop what you are doing and get a book. You have no understanding of
on basic data modeling or SQL.

Q: What is a table?
A: A set of one kind of entities or relationships. They must map to
thigns in the reality of the data model.

Q: What do dynamic table names mean?
A: That you are modeling a world in which elephants can fall out of
the sky!

I see that you use an ORDER BY with an INSERT; did you know that tables
have no ordering? It is the first rule OF RM.

what is a "sub-identity"? Surely you know that an IDENTITY cannot ever
be a key.
You talk about "multiple duplicates", which leads us to ask what a
"single duplicate" means. I thinik you might have meant a two-column
key, but who knows?

Do you know that "sp_" has a special proprietary meaning in T-SQL? Why
did anyone design a CHAR(50) account number?? That has to be a bitch
to verify! But I bet that you have no validation or verification, do
you?

Usually we ask that someone post DDL,data and clear spec. but i do
not think this will really help. Procedures, cursors, sequential
numberings, no constraints and trying to put ordering on tables -- this
is not an RDBMS at all. You are writting a magnetic tape file system
in SQL. You need outside coinsulting and not teh kludges you can get
from a Newsgroup.

[Erland Sommarskog]

[posted and mailed, please reply in news]

(ma**@oneleaf.com) writes:

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.
Ehum, if you have a need for dynamic table names, you probably have a
problem with your design. The idea is that the schema in a relational
database is stable. It may change when there are upgrades to the product,
but when the system is in normal production mode, the set of tables
should be constant.
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.
No, but use a temp table instead. Then you can use TRUNCATE TABLE to
delete the old data, and have the counter reset to 1.

However, study the suggestion from David. Queries likes are not always
that effective, but it's probably more effective than iterating over
each account with duplicates.
Here is the snippet of code replacing the current UPDATE query.

DECLARE @dupTransactions sysname
EXECUTE @dupTransactions = sp_setDuplicateTransactions @accountNumber

The return value from a stored procedure is always an integer value.
Normally you use this only to indicate success/failure, with 0 meaning
success and everything else means failure. Since you don't have a RETURN
statement in your procedure, you get back 0, and the 0 then causes syntax
errors in your dynamic SQL.

I don't really understand what the above is supposed to mean. But judging
from what you say in the rest of the article, you may be interested in
an article on my web site about sharing data between stored procedures,
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[marc]

Sorry about that, I know not to use "sp_", I was testing some of this
to get the results I wanted.

The main reason for doing the duplicate count was to speed up importing
new data. We import new data ever week. Some of the accounts have
duplicate transactions, so we need to check for any new duplicates. So
if the import table has an account with 5 duplicates and the
transaction table has 4, then we know that only one new transaction
needs to be added. We currently have a process in place to do this, but
we are trying to speed up the process by using a different method. So
with numbering the duplicates, we can match up any existing duplicates
and delete them from the import table. Thus leaving only new
transactions to be imported.

On that note, I took you suggested query and it worked extremely well.
My original stored procedure took about 25 minutes, with this new query
only 1 minute.

Thank you very much David!

Marc