|
|
Posted by Erland Sommarskog on 07/19/05 01:22
[posted and mailed, please reply in news]
(marc@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|