InterBase's FOR SELECT..SUSPEND equivalent in MS SQL Server 2005

Hello everyone. I've been wrestling with this one for a while now
and am hoping you can help.
My company is moving from InterBase to MS SQL Server 2005 in the very
near future and all has gone well so far, but there is a stored
procedure on the InterBase database that I have had trouble finding an
equivalent method for on SQL Server.
I hope some of you are familiar with Interbase's FOR SELECT...
SUSPEND method used in sored procedures, but I will try to explain it
anyways. The stored procedure I'm working does something like the
following.
BEGIN
FOR
SELECT DISTINCT PERSONID,
FROM TRANSACTION
WHERE GENERALLEDGERCODE = :IN_GLCODE
INTO :V_PERSONID
DO
BEGIN
SELECT CLOSEDTRANSACTIONBALANCE
FROM SP_CLOSEDTRANSACTIONBALANCE(:V_PERSONID,
:IN_FIRSTDATE)
INTO :BALANCE1;
SELECT CLOSEDTRANSACTIONBALANCE
FROM SP_CLOSEDTRANSACTIONBALANCE( :V_PERSONID, :IN_ENDDATE)
INTO :BALANCE2;
IF( :BALANCE1 <> 0 OR :BALANCE2 <> 0 ) THEN
BEGIN
SUSPEND;
END
END
END
When and if SUSPEND is reached a row is returned from the stored
procedure of your declared return variables. Which in this case would
be PERSONID, BALANCE1, and BALANCE2.
When first attempting this, I just broke the procedure into a couple
different queries on the client side, but that caused something that
took 15 minutes to run on InterBase to take 10+ hours to run.
All of the examples of T-SQL and CLR stored procedures I have looked at
don't do anything quite like this.
Any advice you might have would be appreciated.> Hello everyone. I've been wrestling with this one for a while now
> and am hoping you can help.
> My company is moving from InterBase to MS SQL Server 2005 in the very
> near future and all has gone well so far, but there is a stored
> procedure on the InterBase database that I have had trouble finding an
> equivalent method for on SQL Server.
> I hope some of you are familiar with Interbase's FOR SELECT...
> SUSPEND method used in sored procedures, but I will try to explain it
> anyways. The stored procedure I'm working does something like the
> following.
>
> BEGIN
> FOR
> SELECT DISTINCT PERSONID,
> FROM TRANSACTION
> WHERE GENERALLEDGERCODE = :IN_GLCODE
> INTO :V_PERSONID
> DO
> BEGIN
> SELECT CLOSEDTRANSACTIONBALANCE
> FROM SP_CLOSEDTRANSACTIONBALANCE(:V_PERSONID,
:IN_FIRSTDATE)
> INTO :BALANCE1;
> SELECT CLOSEDTRANSACTIONBALANCE
> FROM SP_CLOSEDTRANSACTIONBALANCE( :V_PERSONID, :IN_ENDDATE)
> INTO :BALANCE2;
> IF( :BALANCE1 <> 0 OR :BALANCE2 <> 0 ) THEN
> BEGIN
> SUSPEND;
> END
> END
> END
> When and if SUSPEND is reached a row is returned from the stored
> procedure of your declared return variables. Which in this case would
> be PERSONID, BALANCE1, and BALANCE2.
> When first attempting this, I just broke the procedure into a couple
> different queries on the client side, but that caused something that
> took 15 minutes to run on InterBase to take 10+ hours to run.
> All of the examples of T-SQL and CLR stored procedures I have looked at
> don't do anything quite like this.
> Any advice you might have would be appreciated.
You might want to take a look at Table Functions in the BOL.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||The closest you'll get to the FOR SELECT.. SUSPEND in SQL Server is using
Cursors.
Something else to be cautious of is the lack of Row-Level triggers in SQL
Server, so you'll need to consider that if you have any logic in Triggers.
And no Generators either, you'll need to use the Identity property on your
tables if you're using Trigger & Generators for primary keys.
There was a page on www.ibphoenix.com that did a pretty decent job of
comparing functionality, but I'm at a loss to find it..
<Contraptor@.gmail.com> wrote in message
news:1132327819.104004.209220@.g47g2000cwa.googlegroups.com...
> Hello everyone. I've been wrestling with this one for a while now
> and am hoping you can help.
> My company is moving from InterBase to MS SQL Server 2005 in the very
> near future and all has gone well so far, but there is a stored
> procedure on the InterBase database that I have had trouble finding an
> equivalent method for on SQL Server.
> I hope some of you are familiar with Interbase's FOR SELECT...
> SUSPEND method used in sored procedures, but I will try to explain it
> anyways. The stored procedure I'm working does something like the
> following.
>
> BEGIN
> FOR
> SELECT DISTINCT PERSONID,
> FROM TRANSACTION
> WHERE GENERALLEDGERCODE = :IN_GLCODE
> INTO :V_PERSONID
> DO
> BEGIN
> SELECT CLOSEDTRANSACTIONBALANCE
> FROM SP_CLOSEDTRANSACTIONBALANCE(:V_PERSONID,
:IN_FIRSTDATE)
> INTO :BALANCE1;
> SELECT CLOSEDTRANSACTIONBALANCE
> FROM SP_CLOSEDTRANSACTIONBALANCE( :V_PERSONID, :IN_ENDDATE)
> INTO :BALANCE2;
> IF( :BALANCE1 <> 0 OR :BALANCE2 <> 0 ) THEN
> BEGIN
> SUSPEND;
> END
> END
> END
> When and if SUSPEND is reached a row is returned from the stored
> procedure of your declared return variables. Which in this case would
> be PERSONID, BALANCE1, and BALANCE2.
> When first attempting this, I just broke the procedure into a couple
> different queries on the client side, but that caused something that
> took 15 minutes to run on InterBase to take 10+ hours to run.
> All of the examples of T-SQL and CLR stored procedures I have looked at
> don't do anything quite like this.
> Any advice you might have would be appreciated.
>|||Ok, I think I have a workable solution. I've created a UDF that
cursors through the initial query inserting rows into a table variable
if the right conditions are met. I have a couple questions though as I
have been unable to test it.
1. When trying to execute the function within the IDE it times out
after about a minute, when I know for a fact that it will take longer
than a minute to run. Finding a CommandTimeOut setting to change from
say 1 minute to 5 minutes has so far been elusive.
2. Since I was unable to test in Studio 2005, I went back to my main
work environment (Borland C++ Builder 6 (don't laugh) ) where this UDF
is ultimately to be used, I dropped a TADOStoredProc, setup my
parameters, set the CommandTimeOut to 15000 seconds instead of 30,
setup a couple other necessary things and when I run it I got a
""TRXACCOUNTLIST" Failed because "TRXACCOUNTLIST" is a table valued
function object" error. This wasn't totaly surprising to me, so I
wrapped the UDF in a stored procedure, but when I run that stored proc
from within Studio 2005 I receive an error saying "The request for
procedure 'TRXACCOUNTLIST' failed because 'TRXACCOUNTLIST' is a table
valued function object.".
Any Suggestions? I can provide code, but I wasn't sure if that would
be helpful here as I may be trying to do something fundamentally wrong.
I'm running Studio 2005 against SQL Server 2005, latest releases.|||1. There is a CommandTimeOut property on the SQLCommand object, you might
want to toggle that.
2. Do you have the latest service packs for BCB? That last Borland
environment I used was D6 and it had issues with ADO & SQL Server 2000
objects. There was an ADOExpress(is that the name?) update that corrected
some issues with SQL Server 2000, if I remember correctly. Can you run the
UDF in query analyzer or SQL Server Management studio without error? If not,
post your code and we'll take a look at it. If the query runs, you might
want to check out the borland newsgroups.
<Contraptor@.gmail.com> wrote in message
news:1132610782.388887.231870@.z14g2000cwz.googlegroups.com...
> Ok, I think I have a workable solution. I've created a UDF that
> cursors through the initial query inserting rows into a table variable
> if the right conditions are met. I have a couple questions though as I
> have been unable to test it.
> 1. When trying to execute the function within the IDE it times out
> after about a minute, when I know for a fact that it will take longer
> than a minute to run. Finding a CommandTimeOut setting to change from
> say 1 minute to 5 minutes has so far been elusive.
> 2. Since I was unable to test in Studio 2005, I went back to my main
> work environment (Borland C++ Builder 6 (don't laugh) ) where this UDF
> is ultimately to be used, I dropped a TADOStoredProc, setup my
> parameters, set the CommandTimeOut to 15000 seconds instead of 30,
> setup a couple other necessary things and when I run it I got a
> ""TRXACCOUNTLIST" Failed because "TRXACCOUNTLIST" is a table valued
> function object" error. This wasn't totaly surprising to me, so I
> wrapped the UDF in a stored procedure, but when I run that stored proc
> from within Studio 2005 I receive an error saying "The request for
> procedure 'TRXACCOUNTLIST' failed because 'TRXACCOUNTLIST' is a table
> valued function object.".
> Any Suggestions? I can provide code, but I wasn't sure if that would
> be helpful here as I may be trying to do something fundamentally wrong.
>
> I'm running Studio 2005 against SQL Server 2005, latest releases.
>|||The query runs alright. Last night it pegged our test box at 100% cpu
utilization for several hours before I came in a closed things down.
I forgot that a feature of Table-Value UDFunctions is that I could just
run a select from it and didn't need to wrap it in a stored procedure
at all.
Now I just need to figure out why something that takes 10-15 minutes to
run in InterBase takes hours within a more powerful SQL Server 2005
box. Hopfully it's just a matter of setting up some indexes.|||Inserting the indexes did the trick, now the whole process runs in
under 5 minutes in MS SQL Server.