Can I group stored procedures some way

Hi folks!
I'm working on a report that requires about 20 SPs to retrieve data.
My code would be better if I could somehow create some object
(package?) that would have all my 20 SPs in it.
This way if I have 100 reports it will be easy to manage the SPs in the
database.
I thought I read several years ago there was a way to do this. Is there
still a way?
Thanks in advance.Bob wrote:
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in
> the database.
> I thought I read several years ago there was a way to do this. Is
> there still a way?
> Thanks in advance.
I'm about you're actually trying to do here. Are you talking
about packaging your procedures in order to create them on another
database? Can you give an example of what you need?
David Gugick
Imceda Software
www.imceda.com|||"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1109359684.432319.268490@.l41g2000cwc.googlegroups.com...
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in the
> database.
> I thought I read several years ago there was a way to do this. Is there
> still a way?
>
Are you refering to the stored procedure number?
CREATE PROC [ EDURE ] procedure_name [ ; number ]
. . .
;number
Is an optional integer used to group procedures of the same name so they can
be dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter
around procedure_name only.
This can be used to group procedures, but it's an old and rarely used
feature, and you run the risk of confusing people. I would probably just
use a common name prefix to sort and identify the related procedures.
David

Can I achieve WITH(NOLOCK) on all joins in a stored procedure with a single command?

I have a number of reporting stored procedures that purely list
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam

callinga web service?

Hi All
can i in call a web service from SP/function
(without using COM SP's) in any way ?
and represent the returned xml as a resultset ?
TIA
Danny
I don't think this is possible in SQL Server 2000 without using the sp_OA
procs or writing an extended stored proc.
In Yukon, you can write a CLR UDF.
Best regards
Michael
"Danny" <danny.ravid@.ness.com> wrote in message
news:eb2d01c43cee$7257dba0$a301280a@.phx.gbl...
> Hi All
> can i in call a web service from SP/function
> (without using COM SP's) in any way ?
> and represent the returned xml as a resultset ?
> TIA
> Danny