Calling Stored Procedures

Hi All

I was wondering if there is a way to call a stored procedure from inside
another stored procedure. So for example my first procedure will call a
second stored procedure which when executed will return one record and i
want to use this data in the calling stored procedure. Is this possible ?

Thanks in advance"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message news:<bvl7me$4tf$1@.lust.ihug.co.nz>...
> Hi All
> I was wondering if there is a way to call a stored procedure from inside
> another stored procedure. So for example my first procedure will call a
> second stored procedure which when executed will return one record and i
> want to use this data in the calling stored procedure. Is this possible ?
> Thanks in advance

There are several options - see here:

http://www.sommarskog.se/share_data.html

If by "one record" you mean a scalar value, then an OUTPUT parameter
would work; if you mean a result set of one row, then you would need
one of the other approaches.

Simon|||Jarrod,
There are 2 ways to do this. (probably more, but these are the 2 most
common ways). Both of these use the Northwind database, so you can test
yourself, if needed:

WAY 1 (this is my favorite because it lets you return multiple values):

create procedure sp_test1
as begin
select top 1 orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2
as begin
declare @.my_value varchar(20)
exec @.my_value = sp_test1
print @.my_value
end

exec sp_test2

WAY 2 (this is probably more common, but the syntax is a little strange.
Note BOTH places where the keyword OUTPUT is used. Both are necessary):

create procedure sp_test1a @.@.outparam varchar(20) OUTPUT
as begin
select top 1 @.@.outparam = orderID from orders
where customerID = 'tomsp'
end

create procedure sp_test2a
as begin
declare @.my_value varchar(20)
exec sp_test1a @.my_value OUTPUT
print @.my_value
end

exec sp_test2a

You can find these and many more questions answered at
www.TechnicalVideos.net
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvl7me$4tf$1@.lust.ihug.co.nz...
> Hi All
> I was wondering if there is a way to call a stored procedure from inside
> another stored procedure. So for example my first procedure will call a
> second stored procedure which when executed will return one record and i
> want to use this data in the calling stored procedure. Is this possible ?
> Thanks in advance|||Chuck Conover (cconover@.commspeed.net) writes:
> create procedure sp_test1

Don't your technical videos tell people to stay away from the sp_
prefix? This prefix is reserved from system procedures, and SQL Server
first looks for these in master. There is a slight performance penalty,
and if MS ships a new system procedure, you might be in for a surprise.

> as begin
> select top 1 orderID from orders
> where customerID = 'tomsp'
> end
> create procedure sp_test2
> as begin
> declare @.my_value varchar(20)
> exec @.my_value = sp_test1
> print @.my_value
> end

I don't know what is supposed to look like, but it won't fly. sp_test1
does not have a RETURN statement, so it will always return 0. sp_test1
will also produce a result set, which will go to the client. In sp_test2
you are receiving the return value in a varchar(20), but the return
value from a stored procedure is an integer value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 2 Feb 2004 23:13:55 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Chuck Conover (cconover@.commspeed.net) writes:
>> create procedure sp_test1
>Don't your technical videos tell people to stay away from the sp_
>prefix? This prefix is reserved from system procedures, and SQL Server
>first looks for these in master. There is a slight performance penalty,
>and if MS ships a new system procedure, you might be in for a surprise.

On that note, is it good/bad practice (or even possible, I haven't
tried) to write some sp_whatever procedures and dump them into master?
Or should one create a common database for that stuff and call it like
exec common..sp_myproc, I get visions of invalid table name messages
if putting sps into a common database that would probably have no
tables.

--
A)bort, R)etry, I)nfluence with large hammer.|||Trevor Best (bouncer@.localhost) writes:
> On that note, is it good/bad practice (or even possible, I haven't
> tried) to write some sp_whatever procedures and dump them into master?
> Or should one create a common database for that stuff and call it like
> exec common..sp_myproc, I get visions of invalid table name messages
> if putting sps into a common database that would probably have no
> tables.

And there those days when the manuals, at least those from Sybase,
almost encouraged people to write their own system procedures.

But those says are long gone by. Today, writing and installing your
own system procedures is not supported.

There are sometimes questions in the newsgroups on how to have stored
procedures in a common database, but these questions typically relate
to applications where you have multiple copies of the schema, and the
answer to these questions is that they need to learn release management.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Simon

Thanks for the link, it did explain what i was trying to do but im not sure
if im going about it the right way, ive posted below the procedure im using
and it works correctly under sql query analyzer but not in VB, im assuming
that this is because im using a temp table and then deleting the temp table
afterwards. The reason im using a temp table is because there isnt always
going to be just one record returned from some of the searches so im
inserting the records into a temp table then one by one adding them to the
search string and deleting each record and finally deleting the table. Is
there a better way that i should be doing this ? Thanks for all your help

-- Stored Procedure Code ---

/*
** Determine Entity Launcher Items
*/

CREATE PROCEDURE [dbo].[EntityLauncherItems]

@.UserName VarChar(50),
@.MachineName VarChar (50),
@.EntityLocationID VarChar(3)

AS

DECLARE @.SqlStr VarChar(500) /* SQL Search String */
DECLARE @.SrchInt VarChar(3) /* Search Integer */
DECLARE @.IdCount Int /* ID Count */

SET @.SrchInt = '1'

/* SELECT Public Items */

SET @.SqlStr = 'SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub =
''' + '1' + ''''

/* Create Temporary Application ID Table */

CREATE TABLE #Id (AppID VarChar(4))

/* SELECT Single Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_MachineAssoc WHERE
MachineName = @.MachineName

/* SELECT Group Machine Items */

INSERT INTO #Id (AppId) SELECT AppID FROM Launcher_LocationAssoc WHERE
LocationID = @.EntityLocationId

/* SELECT UserName Items */

INSERT INTO #Id (AppId) SELECT AppId FROM Launcher_UserAssoc WHERE
UserName = @.UserName

/* Combine Non Public Applications Into Sql Search String */

SET @.IdCount = (SELECT COUNT(AppId) FROM #Id)

WHILE @.SrchInt <= @.IdCount

BEGIN

IF @.SrchInt = 1

BEGIN
SET @.SqlStr = @.SqlStr + ' UNION SELECT AppId, Path, Name FROM
Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

IF @.SrchInt > 1

BEGIN
SET @.SqlStr = @.SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
#Id) + ''''
DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
t1.AppID
END

SET @.SrchInt = @.SrchInt + 1

END

DROP TABLE #Id

EXEC (@.SqlStr)
GO

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402020643.520ad289@.posting.google.c om...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:<bvl7me$4tf$1@.lust.ihug.co.nz>...
> > Hi All
> > I was wondering if there is a way to call a stored procedure from inside
> > another stored procedure. So for example my first procedure will call a
> > second stored procedure which when executed will return one record and i
> > want to use this data in the calling stored procedure. Is this possible
?
> > Thanks in advance
> There are several options - see here:
> http://www.sommarskog.se/share_data.html
> If by "one record" you mean a scalar value, then an OUTPUT parameter
> would work; if you mean a result set of one row, then you would need
> one of the other approaches.
> Simon|||"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> Hi Simon
> Thanks for the link, it did explain what i was trying to do but im not
sure
> if im going about it the right way, ive posted below the procedure im
using
> and it works correctly under sql query analyzer but not in VB, im assuming
> that this is because im using a temp table and then deleting the temp
table
> afterwards. The reason im using a temp table is because there isnt always
> going to be just one record returned from some of the searches so im
> inserting the records into a temp table then one by one adding them to the
> search string and deleting each record and finally deleting the table. Is
> there a better way that i should be doing this ? Thanks for all your help

<snip
If you're getting the right results in QA, then you should be able to
retrieve them in VB - you'd need to explain what you mean by "not working"
when you run it from VB, and which client library you use. If it's ADO, then
one common piece of advice is to put SET NOCOUNT ON at the start of your
procedure:

http://www.aspfaq.com/show.asp?id=2246

Simon|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> WHILE @.SrchInt <= @.IdCount
> BEGIN
> IF @.SrchInt = 1
> BEGIN
> SET @.SqlStr = @.SqlStr + ' UNION SELECT AppId, Path, Name FROM
> Launcher_Items WHERE AppId = ''' + (SELECT TOP 1 AppId FROM #Id) + ''''
> DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
> t1.AppID
> END
> IF @.SrchInt > 1
> BEGIN
> SET @.SqlStr = @.SqlStr + ' OR AppID = ''' + (SELECT TOP 1 AppId FROM
> #Id) + ''''
> DELETE #Id FROM (SELECT TOP 1 * FROM #Id) AS t1 WHERE #Id.AppId =
> t1.AppID
> END
> SET @.SrchInt = @.SrchInt + 1
> END

I might be missing something here, but why the dynamic SQL?

Why can't you just say:

SELECT AppID, Path, Name FROM Launcher_Items WHERE IsPub = '1'
UNION
SELECT AppID, Path, Name
FROM Launcher_Items l
WHERE EXISTS (SELECT *
FROM #Id i
WHERE l.AppId = i.AppId)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hey Simon

Your a champ, thanyou, it fixed it straight away. To answer your question
about VB when i tried to look at the data in the recordset i get an EOF
message. But after putting the SET NOCOUNT ON it fixed that straight away.
Thanks again for you help

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40213aa5$1_1@.news.bluewin.ch...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > Hi Simon
> > Thanks for the link, it did explain what i was trying to do but im not
> sure
> > if im going about it the right way, ive posted below the procedure im
> using
> > and it works correctly under sql query analyzer but not in VB, im
assuming
> > that this is because im using a temp table and then deleting the temp
> table
> > afterwards. The reason im using a temp table is because there isnt
always
> > going to be just one record returned from some of the searches so im
> > inserting the records into a temp table then one by one adding them to
the
> > search string and deleting each record and finally deleting the table.
Is
> > there a better way that i should be doing this ? Thanks for all your
help
> <snip>
> If you're getting the right results in QA, then you should be able to
> retrieve them in VB - you'd need to explain what you mean by "not working"
> when you run it from VB, and which client library you use. If it's ADO,
then
> one common piece of advice is to put SET NOCOUNT ON at the start of your
> procedure:
> http://www.aspfaq.com/show.asp?id=2246
> Simon|||Hi simon

Just one other quick question, this isnt really important but is there a way
to find out how many records have been returned in the stored procedure from
vb ? If i use the .recordcount function with the object it returns -1
regardless of how many records there may be.

Thanks

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40213aa5$1_1@.news.bluewin.ch...
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > Hi Simon
> > Thanks for the link, it did explain what i was trying to do but im not
> sure
> > if im going about it the right way, ive posted below the procedure im
> using
> > and it works correctly under sql query analyzer but not in VB, im
assuming
> > that this is because im using a temp table and then deleting the temp
> table
> > afterwards. The reason im using a temp table is because there isnt
always
> > going to be just one record returned from some of the searches so im
> > inserting the records into a temp table then one by one adding them to
the
> > search string and deleting each record and finally deleting the table.
Is
> > there a better way that i should be doing this ? Thanks for all your
help
> <snip>
> If you're getting the right results in QA, then you should be able to
> retrieve them in VB - you'd need to explain what you mean by "not working"
> when you run it from VB, and which client library you use. If it's ADO,
then
> one common piece of advice is to put SET NOCOUNT ON at the start of your
> procedure:
> http://www.aspfaq.com/show.asp?id=2246
> Simon|||Jarrod,
Look at CursorType and CursorLocation in ADO. You are probably using a
combination which does not give you the recordcount (and then ADO indicates
this by returning -1). ForwardOnly is the default CursorType and it does not
give you the recordcount...
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!

"Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
news:bvt14m$cct$1@.lust.ihug.co.nz...
> Hi simon
> Just one other quick question, this isnt really important but is there a
way
> to find out how many records have been returned in the stored procedure
from
> vb ? If i use the .recordcount function with the object it returns -1
> regardless of how many records there may be.
> Thanks
>
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:40213aa5$1_1@.news.bluewin.ch...
> > "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> > news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > > Hi Simon
> > > > Thanks for the link, it did explain what i was trying to do but im not
> > sure
> > > if im going about it the right way, ive posted below the procedure im
> > using
> > > and it works correctly under sql query analyzer but not in VB, im
> assuming
> > > that this is because im using a temp table and then deleting the temp
> > table
> > > afterwards. The reason im using a temp table is because there isnt
> always
> > > going to be just one record returned from some of the searches so im
> > > inserting the records into a temp table then one by one adding them to
> the
> > > search string and deleting each record and finally deleting the table.
> Is
> > > there a better way that i should be doing this ? Thanks for all your
> help
> > > <snip>
> > If you're getting the right results in QA, then you should be able to
> > retrieve them in VB - you'd need to explain what you mean by "not
working"
> > when you run it from VB, and which client library you use. If it's ADO,
> then
> > one common piece of advice is to put SET NOCOUNT ON at the start of your
> > procedure:
> > http://www.aspfaq.com/show.asp?id=2246
> > Simon|||Hi Lars

Yes i am using the default cursor type in my vb code, which type of cursor
should i be using to return the record count ? Should i also be changing the
lock type as well ?

Thanks

"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:NyrUb.81644$dP1.211699@.newsc.telia.net...
> Jarrod,
> Look at CursorType and CursorLocation in ADO. You are probably using a
> combination which does not give you the recordcount (and then ADO
indicates
> this by returning -1). ForwardOnly is the default CursorType and it does
not
> give you the recordcount...
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> news:bvt14m$cct$1@.lust.ihug.co.nz...
> > Hi simon
> > Just one other quick question, this isnt really important but is there a
> way
> > to find out how many records have been returned in the stored procedure
> from
> > vb ? If i use the .recordcount function with the object it returns -1
> > regardless of how many records there may be.
> > Thanks
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:40213aa5$1_1@.news.bluewin.ch...
> > > > "Jarrod Morrison" <jarrodm@.ihug.com.au> wrote in message
> > > news:bvqfpj$c3p$1@.lust.ihug.co.nz...
> > > > Hi Simon
> > > > > > Thanks for the link, it did explain what i was trying to do but im
not
> > > sure
> > > > if im going about it the right way, ive posted below the procedure
im
> > > using
> > > > and it works correctly under sql query analyzer but not in VB, im
> > assuming
> > > > that this is because im using a temp table and then deleting the
temp
> > > table
> > > > afterwards. The reason im using a temp table is because there isnt
> > always
> > > > going to be just one record returned from some of the searches so im
> > > > inserting the records into a temp table then one by one adding them
to
> > the
> > > > search string and deleting each record and finally deleting the
table.
> > Is
> > > > there a better way that i should be doing this ? Thanks for all your
> > help
> > > > > > <snip>
> > > > If you're getting the right results in QA, then you should be able to
> > > retrieve them in VB - you'd need to explain what you mean by "not
> working"
> > > when you run it from VB, and which client library you use. If it's
ADO,
> > then
> > > one common piece of advice is to put SET NOCOUNT ON at the start of
your
> > > procedure:
> > > > http://www.aspfaq.com/show.asp?id=2246
> > > > Simon
> >|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> Yes i am using the default cursor type in my vb code, which type of
> cursor should i be using to return the record count ? Should i also be
> changing the lock type as well ?

In most cases you probably want a client-side cursor, but server-side
is the default. Set .CursorLocation to adUseClient. Then you only have
one cursor type to choose from, Static.

The reason you cannot get a record count with forward only, is that
you get the rows as soon as SQL Server finds them, so you have no idea
how many there will be until you're through.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jarrod,
As Erland said, but beware that you will get a "disconnected" recordset that
not (automatically) will reflect any changes done on the server. If you
shall change the lock type depends on your own application logic. How do you
update? If you do it by stored procedures your recordset can use
adLockReadOnly, but if you update via the recordset you need
adLockPessimistic, adLockOptimistic or adLockBatchOptimistic.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!d

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948881D4E980EYazorman@.127.0.0.1...
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> > Yes i am using the default cursor type in my vb code, which type of
> > cursor should i be using to return the record count ? Should i also be
> > changing the lock type as well ?
> In most cases you probably want a client-side cursor, but server-side
> is the default. Set .CursorLocation to adUseClient. Then you only have
> one cursor type to choose from, Static.
> The reason you cannot get a record count with forward only, is that
> you get the rows as soon as SQL Server finds them, so you have no idea
> how many there will be until you're through.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

Thanks for the reply, the way you explained it sounds pretty straight
forward so i change the cursor type and location in my code but i still
recieve -1, could this be because the stored procedure has set nocount on ?
This is the code im using in the vb app

Dim PtrlCmd As New ADODB.Command

PtrlCmd.ActiveConnection = CPDBase
PtrlCmd.CommandText = "sp_EntityMemberShips"
PtrlCmd.CommandType = adCmdStoredProc
PtrlRst.CursorType = adOpenStatic
PtrlRst.CursorLocation = adUseClient

PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("MachineName", adVarChar,
adParamInput, 50, frmLoading.lblMachine)
PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("UserName", adVarChar,
adParamInput, 50, frmLoading.lblUserName)

Set PtrlRst = PtrlCmd.Execute

after this line i break and try to get the recordcount and still get a -1 ?

Thanks for your help

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948881D4E980EYazorman@.127.0.0.1...
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> > Yes i am using the default cursor type in my vb code, which type of
> > cursor should i be using to return the record count ? Should i also be
> > changing the lock type as well ?
> In most cases you probably want a client-side cursor, but server-side
> is the default. Set .CursorLocation to adUseClient. Then you only have
> one cursor type to choose from, Static.
> The reason you cannot get a record count with forward only, is that
> you get the rows as soon as SQL Server finds them, so you have no idea
> how many there will be until you're through.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> Thanks for the reply, the way you explained it sounds pretty straight
> forward so i change the cursor type and location in my code but i still
> recieve -1, could this be because the stored procedure has set nocount on
?
> This is the code im using in the vb app
> Dim PtrlCmd As New ADODB.Command
> PtrlCmd.ActiveConnection = CPDBase
> PtrlCmd.CommandText = "sp_EntityMemberShips"
> PtrlCmd.CommandType = adCmdStoredProc
> PtrlRst.CursorType = adOpenStatic
> PtrlRst.CursorLocation = adUseClient

But since you are using cmd.Execute, you should set the cursor location
and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
thing to do if you open the record set with rs.Open.

ADO is indeed very confusing...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (sommar@.algonet.se) writes:
> Jarrod Morrison (jarrodm@.ihug.com.au) writes:
>> Thanks for the reply, the way you explained it sounds pretty straight
>> forward so i change the cursor type and location in my code but i still
>> recieve -1, could this be because the stored procedure has set nocount on
> ?
>> This is the code im using in the vb app
>>
>> Dim PtrlCmd As New ADODB.Command
>>
>> PtrlCmd.ActiveConnection = CPDBase
>> PtrlCmd.CommandText = "sp_EntityMemberShips"
>> PtrlCmd.CommandType = adCmdStoredProc
>> PtrlRst.CursorType = adOpenStatic
>> PtrlRst.CursorLocation = adUseClient
> But since you are using cmd.Execute, you should set the cursor location
> and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
> thing to do if you open the record set with rs.Open.
> ADO is indeed very confusing...

Indeed it is, and on top of that I am only an occasional ADO programmer,
which may explain my incorrect suggestions above.

You don't set the CursorLocation on the Command object; the place for
this is the Connection object. The CursorType property is only available
on the Recordset object, but the good news is that once you have gone
for client-side, there is only one cursor type available and that is
static.

Sorry for any confusion.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

Works a treat once i changed the cursor location for the connection. Thanks
once again for all your help and for explaining the reason why it wasnt
working

Thanks again

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9489CB4E35C6Yazorman@.127.0.0.1...
> Erland Sommarskog (sommar@.algonet.se) writes:
> > Jarrod Morrison (jarrodm@.ihug.com.au) writes:
> >> Thanks for the reply, the way you explained it sounds pretty straight
> >> forward so i change the cursor type and location in my code but i still
> >> recieve -1, could this be because the stored procedure has set nocount
on
> > ?
> >> This is the code im using in the vb app
> >>
> >> Dim PtrlCmd As New ADODB.Command
> >>
> >> PtrlCmd.ActiveConnection = CPDBase
> >> PtrlCmd.CommandText = "sp_EntityMemberShips"
> >> PtrlCmd.CommandType = adCmdStoredProc
> >> PtrlRst.CursorType = adOpenStatic
> >> PtrlRst.CursorLocation = adUseClient
> > But since you are using cmd.Execute, you should set the cursor location
> > and cursor type on PtrlCmd. Setting the properties in PtrlRst is the
> > thing to do if you open the record set with rs.Open.
> > ADO is indeed very confusing...
> Indeed it is, and on top of that I am only an occasional ADO programmer,
> which may explain my incorrect suggestions above.
> You don't set the CursorLocation on the Command object; the place for
> this is the Connection object. The CursorType property is only available
> on the Recordset object, but the good news is that once you have gone
> for client-side, there is only one cursor type available and that is
> static.
> Sorry for any confusion.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Calling Stored Procedure fromanother Stored Procedure

Hi,

I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.

My first Stored Procedure has the following input output parameters:

ALTER PROCEDURE dbo.FixedCharges

@.InvoiceNo int,

@.InvoiceDate smalldatetime,

@.TotalOut decimal(8,2) output

AS ...

I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.

CALL FixedCharges (

@.InvoiceNo,

@.InvoiceDate,

@.TotalOut )

Many thanks in advance

James

I believe you want to use 'EXEC'|||

abadincrotch:

I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

|||

Caddre:

abadincrotch:

I believe you want to use 'EXEC'

You should use System stored procedure sp_executesql, it will take care of the dependcy chain for. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188001.aspx

you'll still need to EXECUTE (EXEC) sp_executesql to begin with.

|||Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.|||

Caddre:

Exec is not the reason it works sp_executesql takes care of the dependecy chain because all stored procedures are recorded in the sysdepends table, if you run both directly in SQL Server it will give the error the second stored proc is not in sysdepends. So the job of sp_executesql is to register the second stored proc with sysdepends in the Master database.

I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.

|||

(I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.)

That error is because of the broken dependency chain and one of sp_executesql job is to provide the dependency chain.

|||never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.|||

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

|||

Caddre:

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

|||

abadincrotch:

Caddre:

abadincrotch:

never encountered that problem when using EXEC, and from the look of his syntax, it doesn't seem as though CALL is required.

Exec works some times but not always but sp_executesql is the main way to do it, now read what Microsoft says about Exec.

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

rather than waste my time re-reading msdn/sqlbol, if you have a point to make there, please make it -- that entry is lengthy.

and like I said, you still need to CALL or EXEC sp_executesql to begin with, so your point is ... ?

|||

(I'm not sure you're understanding his question or my responses to begin with -- he needs to know how to execute a stored procedure, from within another stored procedure. this is done using the EXEC TSQL statement.)

You said I don't understand what the user asked for when I gave the correct solution, when you gave something that works some times and not always so you have a point I don't.

The Exec works some times and not always sp_executesql works always.

|||

Thanks for your help People,

It worked!!

James

|||

JamesNZ:

Thanks for your help People,

It worked!!

James

out of curiosity, which?

|||

Caddre:

The Exec works some times and not always sp_executesql works always.

somehow I fail to see where it says EXEC doesn't always "work."

yes, the secureables need to have permissions granted to the role/login executing the statement ... that's just plain common sense ... where does it say exec doesn't always work?

Calling stored procedure from another stored procedure

Is it possible to call one sp from another sp?
I've been hunting around for an example to do this and just can't seem to find one.
Anyone have a link for this or a sample?
Thanks all,
Zath

Yes, you can. Just use EXEC usp_secondStoredProc @.params inside your first SP.

Nick

Calling RS through DTS

Does anyone have an example? Can this be done through VBS?
I need to do some preprocessing and then render a report.
ThanksIs the environment all SQL Server? Your report could actually call a
stored procedure which kicks off DTS and then returns data when the DTS
is done.
Andy Potter|||We use shared schedules on subscriptions. This gives us the ability to look
up the shared schedule's Name in the Schedule table to find the ScheduleID.
One you have the ScheduleID you can run the subscription via the AddEvent SP.
You can lookup how to do this in the SQL Server Agent, but it is simple:
exec ReportServer.dbo.AddEvent @.EventType='SharedSchedule',
@.EventData='57a5c647-81bb-4715-be5c-7297dd7c306f'
you just need to put the ScheduleID in the @.EventData section. We actually
run this from Services for UNIX using isql, but I know this can be done
similarly from DTS.
The other nice thing about shared schedules is that you can put multiple
subscriptions on a single shared schedule and kick them all off at once.
We set all of our shared schedules that we use this way to run once in the
past so they never run other than when we trigger them.
Hope this helps!
"CatsCradle" wrote:
> Does anyone have an example? Can this be done through VBS?
> I need to do some preprocessing and then render a report.
> Thanks
>

Calling other databases from stored procedure

Hi,

I am working with multiple databases on the same server and in a stored procedure I need to be able to call on one of them.

Here is an example of what I am trying to do in this stored procedure:
create procedure sp_procedure
(
@.variable int
)
select anitem from atable where selection = @.variable

declare @.anothervariable Char(3)

select @.anothervariable = item_that_determines_database from atable where selection = @.variable

usedbo.@.anothervariable
select count(id) from Some_table where selection = @.variable

The database is not found using this method and I do need to use it in a stored procedure. All of the databases being used are (3) letter names in lower case (aaa, bbb, ccc, etc...), the info that @.anothervariable pulls from the table is the name of that database but in all caps. Does this part make a difference?

Also, what method could I use to get the database variable to read from that selected database?

Thank-you for your help.

Eric

You can build a dynamic SQL.

Declare @.sql varchar(500)

SET @.sql = 'SELECT @.anothervariable = ' + @.otherdb + '.dbo.itemfromothertable where selection = @.variable'

use sp_ExecuteSQL the execute the SQL and add the parameters appropriately.

|||

I don't get it.

Where did the @.otherdb come from?

I am selecting an item from a table and using that item to get a count from a table in another database. The database is determined from the result of the first query.

|||Yes, since the DB name is dynamic, you need to get the name from your first query, assign it to the variable @.otherdb, build your T-SQl and execute it.

Calling a Stored Procedure on Multiple Records

I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @.UserID BIGINT AS
DECLARE @.TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)

INSERT INTO @.TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @.UserID)
FROM Users u
WHERE u.RecID = @.UserID

SELECT * FROM @.TempTable
GO

So if I execute this amount entering a single ID, it returns a single row for that user:

UserID UserName TotalAmt
--------
1 Doe, John 100.00

What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:

UserID UserName TotalAmt
--------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55

Is there a way to re-use a stored procedure within another, based on the results of a query?Some thoughts:

1. This sounds more like a function than a stored procedure. Would make the execution concept much easier (ie, SELECT dbo.MyFunction(MyUserID) FROM MyTable).

2. You could wrap this with another stored procedure; you could pass in the user list as a parameter (there was an excellent article recently over at SQLServerCentral.com about using an xml string as a parameter).

But I really think that what you want is a function.

Regards,

hmscott|||Thanks for your feedback.

I'm not familiar with functions. Would I want to convert GetUserStats to a function?|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?|||I'm not familiar with functions. Would I want to convert GetUserStats to a function?

I wouldn't "convert" per se; at least not the way that you have written it.
Try this:

Open Query Analyzer
Click on File | New
Double click on Create Function
Double click on Create Scalar Function

Read in SQL BOL about Scalar functions.

I think you will get an idea of what a function is and how to go about writing your own. From there I think you will find it easier to understand how to proceed.

Regards,

hmscott|||may, maybe not. you do know you can do that whole thing without the temp table.

BTW, bigint for userid? do you know there are not that many people in the world?
The example I posted is very simplified. The actual query I'm trying to pull off is pretty complicated and pulls data from several tables, that's why I included the temp table. Can I use a temp table in a function?

Yeah, I guess a BIGINT is more than I need for a user ID. I had the misconception of thinking INTs only go to 32,000 when I set up this database (as they do in many programming languages).|||I have tried building a few functions. I can get them to run using a direct statemtent such as:

SELECT * FROM MyFunction(1)

But If I try to use the line above in a stored procedure or another function, I get an error:

Invalid object name 'MyFunction'.|||They're fiesty. You need the user name as a prefix, as in:SELECT *
FROM dbo.MyFunction(1)-PatP

Calling a job on another server...How to

Hello, all. Was wondering if it is possible to execute a scheduled job
located on another server? For example, on Server A, I have a few scheduled
DTS jobs I want run by being executed from a DTS job on Server B.
As always,
Thnx!
Roz
Possibly through a linked server from a TSQL task/jobstep:
EXEC srvname.msdb.dbo.sp_start_job
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
> Hello, all. Was wondering if it is possible to execute a scheduled job
> located on another server? For example, on Server A, I have a few scheduled
> DTS jobs I want run by being executed from a DTS job on Server B.
> As always,
> Thnx!
> Roz
>
|||Sounds like it'll work. I'll give it a shot, and report back.
Thanks!
"Tibor Karaszi" wrote:

> Possibly through a linked server from a TSQL task/jobstep:
> EXEC srvname.msdb.dbo.sp_start_job
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Roz" <Roz@.discussions.microsoft.com> wrote in message
> news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
>

Calling a job on another server...How to

Hello, all. Was wondering if it is possible to execute a scheduled job
located on another server? For example, on Server A, I have a few scheduled
DTS jobs I want run by being executed from a DTS job on Server B.
As always,
Thnx!
RozPossibly through a linked server from a TSQL task/jobstep:
EXEC srvname.msdb.dbo.sp_start_job
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
> Hello, all. Was wondering if it is possible to execute a scheduled job
> located on another server? For example, on Server A, I have a few scheduled
> DTS jobs I want run by being executed from a DTS job on Server B.
> As always,
> Thnx!
> Roz
>|||Sounds like it'll work. I'll give it a shot, and report back.
Thanks!
"Tibor Karaszi" wrote:
> Possibly through a linked server from a TSQL task/jobstep:
> EXEC srvname.msdb.dbo.sp_start_job
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Roz" <Roz@.discussions.microsoft.com> wrote in message
> news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
> > Hello, all. Was wondering if it is possible to execute a scheduled job
> > located on another server? For example, on Server A, I have a few scheduled
> > DTS jobs I want run by being executed from a DTS job on Server B.
> >
> > As always,
> > Thnx!
> > Roz
> >
>

Calling a job on another server...How to

Hello, all. Was wondering if it is possible to execute a scheduled job
located on another server? For example, on Server A, I have a few scheduled
DTS jobs I want run by being executed from a DTS job on Server B.
As always,
Thnx!
RozPossibly through a linked server from a TSQL task/jobstep:
EXEC srvname.msdb.dbo.sp_start_job
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
> Hello, all. Was wondering if it is possible to execute a scheduled job
> located on another server? For example, on Server A, I have a few schedule
d
> DTS jobs I want run by being executed from a DTS job on Server B.
> As always,
> Thnx!
> Roz
>|||Sounds like it'll work. I'll give it a shot, and report back.
Thanks!
"Tibor Karaszi" wrote:

> Possibly through a linked server from a TSQL task/jobstep:
> EXEC srvname.msdb.dbo.sp_start_job
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Roz" <Roz@.discussions.microsoft.com> wrote in message
> news:041038EE-0A29-48B7-97A5-6DD31B5A9DB7@.microsoft.com...
>

calling a stored procedure from another

Hi All,

could anyone help me with an example as to how to call a stored procedure from another procedure.

I need to call sprocA, which inserts values in DB and also retuns an integer value, pass this integer value to sprocB and perform some inserts.

Thanks in advance,

vnswathi.

This calls one procedure and users an output value for input to the other, if the first succeded

declare @.retvalue int;
declare @.outputvalue int;

execute @.retvalue = sprocA @.outputvalue output;

if @.retvalue = 0
begin
execute @.retvalue = sprocB @.outputvalue;
end

EDIT: I just saw now that this was in reporting services so not sure if this will help as it is just how to do it in TSQL

Call Stored Procedure within a SELECT statement

Hello,
Is it possible to call a SP from within the SELECT part of a SQL query?
For example, it is possible to put a sub-query within the SELECT:
SELECT
a,
b,
id,
(SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
FROM
Tbl1
So therefore, shouldn't I be able to do something like:
SELECT
a,
b,
id,
(EXEC SP2 id) as secondaryID
FROM
Tbl1
Is this possible? Have I got the syntax wrong.
Andrewmilney_boy wrote:
> Hello,
> Is it possible to call a SP from within the SELECT part of a SQL query?
> For example, it is possible to put a sub-query within the SELECT:
> SELECT
> a,
> b,
> id,
> (SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
> FROM
> Tbl1
> So therefore, shouldn't I be able to do something like:
> SELECT
> a,
> b,
> id,
> (EXEC SP2 id) as secondaryID
> FROM
> Tbl1
>
> Is this possible? Have I got the syntax wrong.
> Andrew
No that's not possible. Either use a user-defined function instead of a
proc or rewrite the expression from your proc as part of this query.
David Portas
SQL Server MVP
--|||> Is it possible to call a SP from within the SELECT part of a SQL query?
Nope.
You can create a table-valued function, or have a look at other ways to
share data between stored procedures:
http://www.sommarskog.se/share_data.html|||milney_boy wrote:
> Hello,
> Is it possible to call a SP from within the SELECT part of a SQL
> query?
> For example, it is possible to put a sub-query within the SELECT:
> SELECT
> a,
> b,
> id,
> (SELECT Tbl2.id from Tbl2 where Tbl2.fk = id) as secondaryID
> FROM
> Tbl1
> So therefore, shouldn't I be able to do something like:
> SELECT
> a,
> b,
> id,
> (EXEC SP2 id) as secondaryID
> FROM
> Tbl1
>
> Is this possible? Have I got the syntax wrong.
No. You can call a UDF like this, but not a SP. See
http://www.sommarskog.se/share_data.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Look for OPENQUERY in the syntax:
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
HTH, Jens Suessmeyer.|||The problem I have is that the stored procedure I have written returns
a row of an unknown number of columns - hence the fact that i wanted to
just 'plonk' the stored procedure into the SELECT clause.
To make matters worse, the stored procedure mentioned uses dynamic sql
- as in EXEC(@.query) - due to the fact that I am returning a variable
number of columns.
Any ideas?|||milney_boy wrote:
> The problem I have is that the stored procedure I have written returns
> a row of an unknown number of columns - hence the fact that i wanted to
> just 'plonk' the stored procedure into the SELECT clause.
> To make matters worse, the stored procedure mentioned uses dynamic sql
> - as in EXEC(@.query) - due to the fact that I am returning a variable
> number of columns.
> Any ideas?
A query always returns a fixed number of columns. You can't have a
variable number of columns without using dynamic SQL so you'll have to
put the entire query in a proc or construct it client-side. Why don't
you know the columns at design time?
David Portas
SQL Server MVP
--|||The reasons is that I am converting from a table of none or more rows
per user to a column layout:
user | letter
1 A
1 F
1 X
2 G
2 D
to:
user | letter1 | letter2 | letter3 etc...
1 A F X
2 G D
Hence, I have written dynamic SQL to return the row structure.
Unfortunately, I also need other information about the user from
another 7 tables, so I need to join the SProc output to other queries.|||then why not join the SProc *input* and then do the conversion (it's
called crosstab and as far as I'm concerned the reference on this is:
description is on
http://weblogs.sqlteam.com/jeffs/ar...05/02/4842.aspx
updated version is on http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
)
milney_boy wrote:
> The reasons is that I am converting from a table of none or more rows
> per user to a column layout:
> user | letter
> 1 A
> 1 F
> 1 X
> 2 G
> 2 D
> to:
> user | letter1 | letter2 | letter3 etc...
> 1 A F X
> 2 G D
> Hence, I have written dynamic SQL to return the row structure.
> Unfortunately, I also need other information about the user from
> another 7 tables, so I need to join the SProc output to other queries.
>

Call a Stored Procedure in another server

is it possible to call a stored proc if for example im in server1 then the proc i need is in server2... can i access that proc?

any help is much appreciated! tnx!As you have posted a question in the articles section it is being moved to SQL Server Forum.

MODERATOR

Calendar Reporting Control

I have a request to have a report created in a calendar layout. Does anyone
know of a charting component that can do this or an example that would help
me get started.
Thanks,
DavidHi David,
Welcome to MSDN Managed Newsgroup!
Unfortunately, I am afraid we do not have direct solution for this calendar
layout report in Microsoft. You may have to handle the layout yourself and
write custom code if required.
Let's wait to see whether other community members have such experience. You
may also search the google.com for related information.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Calculations across semi-related fact tables

Using the AdventureWorks database as an example:

If I create a calculated measure that is the sum of total sales across InternetSales and ResellerSales, how do I ensure that when I am browsing the cube, that my total sales value is always accurate even when I filter the cube on a dimension that applies to one fact table, but not the other.

For example, if I am browsing the cube by Sales Territory and I apply a filter on a specific Reseller Type (for instance Warehouse), then I would expect that for Sales Territories that did not have ResellerSales with a Reseller Type of Warehouse, the value displayed for Total Sales would be blank.

However, because the non-empty behavior for the calculation is the combination of InternetSales and Reseller Sales, the InternetSales value will always be displayed (even though it does not make sense based on the filter).

Is there a way to change this behavior?

I ran the following query against an unmodified version of the Adventure Works DW database:

Code Snippet

with member [Measures].[x] as

[Measures].[Reseller Sales Amount] +

[Measures].[Internet Sales Amount]

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Internet Sales Amount],

[Measures].[x]

} on 0,

Geography.Country.Members on 1

from [Adventure Works];

At the All Geographies member of the Country attribute hierarchy, Internet Sales Amount had a value of $29M. Against individual countries, this same $29M figure was repeated as the Internet Sales measure group has no relationship with this dimension. If you think about how SSAS handles cube space definition, this makes sense even though it is counter-intuitive to most users.

Then, the question is how is the calculated member [x] handled? The $29M is added to whatever value for Reseller Sales Amount is returned.

I then went into the cube designer in BIDS for the Adventure Works cube, selected the Internet Sales measure group, and then pulled up its properties. I set IgnoreUnrelatedDimensions to False and reprocessed the cube. When I executed the query again, I got the same $29M for the All Geographies member, but NULL for its children. The calculated member [x] reflected $29M + Reseller Sales Amount for the All Geographies member and just Reseller Sales Amount for the other members.

Bryan

Calculations

I have to perform a number of calculations on a table but these
calculations need intermediate values ( like for example I have to
first calculate one value before I can use it in the next step of the
calcualtion). There are two ways of doing this:
1. Have columns for intermediate values ( it is possible as my
calculations are on a temp tbl) and use update statements. One update
statement for every step in the calculation.
2. Have a cursor, loop through the resultset and do the calculations
like we would in a programming language like C#.
Which one is better? Are updates ( a number of them) faster than a
cursor?
Thanks.Cursors are usually slower than an SQL operation that updates all the rows i
n
one go.
I would only use a cursor for this sort of operation if there were any
locking issues.
Are Riksaasen
"John Smith" wrote:

> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
> Thanks.
>

Calculating Trends

Hello,
I am looking for the most efficient way to calculate a trend based upon
individual figures in a table. Here is an example (e.g. sold items):
Data:
date number
-- --
2001-01-01 3
2001-01-02 5
2001-01-03 10
expected result:
date totsl number of sold items
-- --
2001-01-01 3
2001-01-02 8
2001-01-03 18
Any idea hoe I can solve that with the best performance possible?
ThanksOn Thu, 2 Jun 2005 22:36:17 +0200, CrazyHorse wrote:

> Hello,
> I am looking for the most efficient way to calculate a trend based upon
> individual figures in a table. Here is an example (e.g. sold items):
> Data:
> date number
> -- --
> 2001-01-01 3
> 2001-01-02 5
> 2001-01-03 10
> expected result:
> date totsl number of sold items
> -- --
> 2001-01-01 3
> 2001-01-02 8
> 2001-01-03 18
> Any idea hoe I can solve that with the best performance possible?
> Thanks
"Calculating Running Totals"
http://www.sqlteam.com/item.asp?ItemID=3856
Summary: Depending on the size of your data, this is one instance where a
cursor may actually be worth using. This is because the cursor will only
make one pass through the table, while any set-based solution has to join
the table with itself - which can produce millions of temporary rows, at
least momentarily.
Most of the time, an even better idea is to let the SQL side just retrieve
the un-summarized data, and let the consuming application produce the
running total. In effect, this is moving the cursor from the database side
to the client side.|||Do:
SELECT dt, ( SELECT SUM( number )
FROM tbl t2
WHERE t2.dt <= t1.dt )
FROM tbl t1 ;
Anith|||Hello,
thanks for your help.
<CrazyHorse> wrote in message news:uvjo$K7ZFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am looking for the most efficient way to calculate a trend based upon
> individual figures in a table. Here is an example (e.g. sold items):
> Data:
> date number
> -- --
> 2001-01-01 3
> 2001-01-02 5
> 2001-01-03 10
> expected result:
> date totsl number of sold items
> -- --
> 2001-01-01 3
> 2001-01-02 8
> 2001-01-03 18
> Any idea hoe I can solve that with the best performance possible?
> Thanks
>

Calculating QTD - Help

Hi,

Can anyone help me in calculating QTD for a particular measure? Some example on QTD calculation would be helpful.

In SSAS2005 you can try this:

SUM(QTD(MyTimeDim.MyTimeHierarchy.Month.CurrentMember), Measure.MyMeasure)

In AS2000 you can try this:

SUM(QTD(MyTimeDim.Month.CurrentMember), Measure.MyMeasure)

These are very simple examples to get you going.

Here is a link to a good article about MDX-time functions:

http://www.databasejournal.com/features/mssql/article.php/2232111

HTH

Thomas Ivarsson

Calculating previous thursday date

please can someone help me.
I am trying to work out the previous thursday date, for example, todays
date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
for 05/04/2006, previous date is 30/03/2006 but if the date is
06/04/2006 then the previous date is 06/04/2006.
This must work in a View.
I am using SQL Server 2000.
Cheers.
paulLook up DATEADD() in Books On Line and subtract 7 days.|||--CELKO-- wrote:
> Look up DATEADD() in Books On Line and subtract 7 days.
But there's more to it than that. I think I've had a brain-fart. The
best statement I can come up with is:
DATEADD(day,(0 - (((DATEPART(wday,CURRENT_TIMESTAMP) + 1) % 7) + 1))
% -7,CURRENT_TIMESTAMP)
but surely there's something shorter?
Damien|||First you check wday using DATENAME function.
And can use CASE WHEN statement.
if the date is 'thursday' , you can make previous thursday using DATEADD
function .
"PP"?? ??? ??:

> please can someone help me.
> I am trying to work out the previous thursday date, for example, todays
> date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
> for 05/04/2006, previous date is 30/03/2006 but if the date is
> 06/04/2006 then the previous date is 06/04/2006.
> This must work in a View.
> I am using SQL Server 2000.
> Cheers.
> paul
>|||On 30 Mar 2006 18:31:39 -0800, PP wrote:

>please can someone help me.
>I am trying to work out the previous thursday date, for example, todays
>date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
>for 05/04/2006, previous date is 30/03/2006 but if the date is
>06/04/2006 then the previous date is 06/04/2006.
>This must work in a View.
Hi Paul,
Here's a trick that doesn't depend on the SET DATEFIRST setting:
CREATE VIEW LastThursday
AS
SELECT DATEADD(day,
DATEDIFF(day, '19000104', CURRENT_TIMESTAMP) / 7 * 7,
'19000104') AS LastThusrday
go
SELECT * FROM LastThursday
go
DROP VIEW LastThursday
go
Hugo Kornelis, SQL Server MVP

Calculating leaf pages

I am trying to understand an example of indexing I read about.
Given the following SQL statement:
select title
from titles
where price between $20.00 and $30.00
The size of the table in rows and pages, the number of rows per page, and the
number of rows that the query returns:
1,000,000 rows (books)
190,000 are priced between $20 and $30
10 rows per page; pages 75 percent full; approximately 140,000 pages
There is a statement that I read which said:
Based upon a nonclustered index on price, title (and the table does not have
a clustered index) the query can perform a matching index scan, finding the
first page with a price of $20 via index pointers, and then scanning forward
on the leaf level until it finds a price more than $30. This index requires
about 35,700 leaf pages, so to scan the matching leaf pages requires about 6,
800 reads.
The part I do not understand is "This index requires about 35,700 leaf pages.
" I have a basic understanding of the leaf structure of a nonclustered index,
but I do not understand the value 35,700. How was 35,700 determined or
calculated based off the given data?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
The number of leaf nodes in the non-clustered index cannot be calculated
from the data given. Storage requirements depend on the data types of the
key columns and, for variable length data, the average value length. It
seems the average title is about 50 characters, assuming a varchar data type
and smallmoney for price.
It appears to me the point of this indexing example is to demonstrate the
relative efficiency of a covering non-clustered index rather than how to
calculate space index requirements.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards" <u3288@.uwe> wrote in message news:612e0f3c23791@.uwe...
>I am trying to understand an example of indexing I read about.
> Given the following SQL statement:
> select title
> from titles
> where price between $20.00 and $30.00
> The size of the table in rows and pages, the number of rows per page, and
> the
> number of rows that the query returns:
> 1,000,000 rows (books)
> 190,000 are priced between $20 and $30
> 10 rows per page; pages 75 percent full; approximately 140,000 pages
> There is a statement that I read which said:
> Based upon a nonclustered index on price, title (and the table does not
> have
> a clustered index) the query can perform a matching index scan, finding
> the
> first page with a price of $20 via index pointers, and then scanning
> forward
> on the leaf level until it finds a price more than $30. This index
> requires
> about 35,700 leaf pages, so to scan the matching leaf pages requires about
> 6,
> 800 reads.
> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1

Calculating leaf pages

I am trying to understand an example of indexing I read about.
Given the following SQL statement:
select title
from titles
where price between $20.00 and $30.00
The size of the table in rows and pages, the number of rows per page, and th
e
number of rows that the query returns:
1,000,000 rows (books)
190,000 are priced between $20 and $30
10 rows per page; pages 75 percent full; approximately 140,000 pages
There is a statement that I read which said:
Based upon a nonclustered index on price, title (and the table does not have
a clustered index) the query can perform a matching index scan, finding the
first page with a price of $20 via index pointers, and then scanning forward
on the leaf level until it finds a price more than $30. This index requires
about 35,700 leaf pages, so to scan the matching leaf pages requires about 6
,
800 reads.
The part I do not understand is "This index requires about 35,700 leaf pages
.
" I have a basic understanding of the leaf structure of a nonclustered index
,
but I do not understand the value 35,700. How was 35,700 determined or
calculated based off the given data?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
The number of leaf nodes in the non-clustered index cannot be calculated
from the data given. Storage requirements depend on the data types of the
key columns and, for variable length data, the average value length. It
seems the average title is about 50 characters, assuming a varchar data type
and smallmoney for price.
It appears to me the point of this indexing example is to demonstrate the
relative efficiency of a covering non-clustered index rather than how to
calculate space index requirements.
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards" <u3288@.uwe> wrote in message news:612e0f3c23791@.uwe...
>I am trying to understand an example of indexing I read about.
> Given the following SQL statement:
> select title
> from titles
> where price between $20.00 and $30.00
> The size of the table in rows and pages, the number of rows per page, and
> the
> number of rows that the query returns:
> 1,000,000 rows (books)
> 190,000 are priced between $20 and $30
> 10 rows per page; pages 75 percent full; approximately 140,000 pages
> There is a statement that I read which said:
> Based upon a nonclustered index on price, title (and the table does not
> have
> a clustered index) the query can perform a matching index scan, finding
> the
> first page with a price of $20 via index pointers, and then scanning
> forward
> on the leaf level until it finds a price more than $30. This index
> requires
> about 35,700 leaf pages, so to scan the matching leaf pages requires about
> 6,
> 800 reads.
> The part I do not understand is "This index requires about 35,700 leaf
> pages.
> " I have a basic understanding of the leaf structure of a nonclustered
> index,
> but I do not understand the value 35,700. How was 35,700 determined or
> calculated based off the given data?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1