473,857 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.setDuplicat eTransactions(@ accountNumber as
varchar(50))
RETURNS @dupTransaction s TABLE
(
ID int IDENTITY,
transactionID int
)
AS

BEGIN

INSERT @dupTransaction s
SELECT t1.transactionI D
FROM providerTransac tions t1
WHERE t1.accountNumbe r = @accountNumber
ORDER BY t1.transactionI D

RETURN

END

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

CREATE PROCEDURE dbo.sp_parseTra nsactions

AS

DECLARE @accountNumber varchar(50)

DECLARE temp_cursor CURSOR FORWARD_ONLY FOR
SELECT t1.accountNumbe r
FROM providerTransac tions t1
GROUP BY t1.accountNumbe r
HAVING MAX(isNull(t1.d uplicateCount,0 )) != COUNT(t1.transa ctionID)
ORDER BY t1.accountNumbe r

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @accountNumber

WHILE @@FETCH_STATUS = 0

BEGIN
UPDATE providerTransac tions
SET duplicateCount = t1.ID
FROM setDuplicateTra nsactions(@acco untNumber) t1,
providerTransac tions t2
WHERE t1.transactionI D = t2.transactionI D

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_setDupli cateTransaction s
@accountNumber as varchar(50)
AS
DECLARE @dupTransaction s TABLE
(
ID int IDENTITY,
transactionID int
)

BEGIN
INSERT @dupTransaction s
SELECT t1.transactionI D
FROM providerTransac tions t1
WHERE t1.accountNumbe r = @accountNumber
ORDER BY t1.transactionI D

END

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

DECLARE @dupTransaction s sysname
EXECUTE @dupTransaction s = sp_setDuplicate Transactions @accountNumber

EXEC('
UPDATE providerTransac tions
SET duplicateCount = t1.ID
FROM ' + @dupTransaction s + ' t1, providerTransac tions t2
WHERE t1.transactionI D = t2.transactionI D
')

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

Jul 23 '05 #1
4 13477
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.ac countnumber
AND T.transactionid <= Transactions.tr ansactionid);

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
--

Jul 23 '05 #2
>> 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.

Jul 23 '05 #3
[posted and mailed, please reply in news]

(ma**@oneleaf.c om) 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 @dupTransaction s sysname
EXECUTE @dupTransaction s = sp_setDuplicate Transactions @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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5551
by: Robin Tucker | last post by:
Hi, I have one stored procedure that calls another ( EXEC proc_abcd ). I would like to return a result set (a temporary table I have created in the procedure proc_abcd) to the calling procedure for further manipulation. How can I do this given that TABLE variables cannot be passed into, or returned from, a stored procedure? Thanks,
0
2123
by: jamiemcc | last post by:
Hi, I would like to have 1 stored procedure call another stored procedure (which contains multiple select statements) and then be able to access the 3 result sets. Example Create Procedure . exec Multiple --manipulate result set A
2
6839
by: Dan Flynn | last post by:
We have a stored procedure that returns a result set. We call this stored procedure from an RPG program using SQL CLI. The first call is successful and we can fetch rows, etc. The RPG program ends with *INLR set on. The second time the program is called and the stored procedure is executed, the stored procedure bombs before it can return the cursor to the result set. In the job log we see: Space offset X'00000000' or teraspace...
2
10640
by: Twan Kennis | last post by:
Question: How do I pass a returning resultset from a nested Stored Procedure (which opens a cursor including option "WITH RETURN TO CALLER") as a returning resultset from it's own? When I execute the top-level Stored Procedure, it executes succesfully, but without any resultset. ========================================================== CREATE PROCEDURE sp_executesql(sqltxt CLOB(2M))
2
2379
by: Patrick Olurotimi Ige | last post by:
When i run the code below with stored proc :- I get only the first table results :-"templates" even if i fill the dataset with another table for example category,pages etc.. Any ideas? Store procedure below:- ------------------------
4
4874
by: Jaraba | last post by:
In Microsoft SQL I can run the following command: insert into table execute proc1 to insert data into a table. Is there a comparable way to do this in DB2?
1
13675
by: deepdata | last post by:
Hi, I am trying to fetch data from db2 (express version) database by calling stored procedure. I have tried to use both cursor and for loop but still i am getting error. --======Start procedure============= Create PROCEDURE get_timedout_scripts (
7
9728
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason for this being to stop the user thinking the application has frozen when in fact it is just waiting for a long SP to complete. Another reason for doing it like this is that I also have had a problem in the past where the SP takes longer than the...
3
15820
by: mandible | last post by:
I'm trying to call one stored procedure inside another. I was wondering if this is possible Some ideas I was toying with is putting the first stored procedure inside of a temp table but haven't been able to get this idea to work.
0
9923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10808
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7940
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7104
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5971
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4592
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.