February 25, 2003 at 9:34 am
quote:
...occurance of the semicolon in your naming convention. Does that have signifigance, or is it just convenient?...
Semicolons are a way of implementing stored procedure groups. The procedure group will only appear once in Enterprise Manager, yet there will be multiple execution plans available for it, since each sub-procedure will be cached separately. If you double-click on MyProc in Enterprise Manager, it will open all sub-procedures for editing... Also, each sub-procedure can be called explicitly through ADO...
' Pseudo-code
Recordset = Commmand.Execute("MyProc;2")
February 25, 2003 at 10:28 am
Ok, so this is only an easier means of viewing things inside the EM; it doesn't have any real bearing on the code. It's not like Oracle where you can package things together, it's just for display purposes.
I'm building things in Access, and the semi is causing issues. Also, in Access, all the SPs are visible, the semi doesn't hide them.
February 25, 2003 at 11:00 am
Definitely, this is not Access JET SQL. That is a significantly different variation from T-SQL. Unfortunately, I don't have the expertise to comment on how stored procedure groups in SQL Server (which I believe I explained as more than just a way of displaying in EM) are different from Oracle. Maybe someone from the Oracle camp can comment...?
February 25, 2003 at 11:22 am
It looks to me that the issue you are having is running a query against more than one table. If so, here's an undocumented procedure that might help (from Guru's Guide to Transact-SQL by Ken Henderson):
syntax:
sp_msforeachtable
@command1
@replacechar = '?'
[,@command2]
[,@command3]
[,@whereand]
[,@precommand]
[,@postcommand]
Purpose:
Executes up to three commands for every table in a database (optionally matching the @whereand clause). @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.
Example:
exec sp_msforeachtable @command1='Print "Listing ?=', @command2='select * from ?', @whereand=' AND name like "title%"'
I have never used this procedure, but it might be what you are looking for. You would use the @whereand to keep the search to your temp tables by using @whereand=' AND name like "##mytemptable%"'
(BTW- That is a space between the single quote and the AND in the @whereand clause).
-SQLBill
February 25, 2003 at 12:13 pm
Well, it's not pretty, but it is fast. So, 2^5 stored procs later +1 master, it's much smoother. Thanks very much for your assistance, and sticking with me through it.
February 25, 2003 at 12:19 pm
You're welcome. Sometimes you just have to fall back on an efficient, but ugly, solution... 🙂 Hope everything works out...
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply