multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.
In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>

multi-statement functions vs stored procedures

I would like to understand the reason in which to "use multi-statement
functions instead of stored procedures that return tables. Writing stored
procedures that return tables as multi-statement userdefined functions can
improve efficiency."
When to use Multi-statement table-value and when to use stored procedure?
Thanks.In general:
When you want to execute code, use stored procedures.
When you want to use the result table of your code in a SELECT statement (in the FROM clause), use a
MSTVUDF.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:3EA76526-4DA5-454B-973E-F457D7795E30@.microsoft.com...
> I would like to understand the reason in which to "use multi-statement
> functions instead of stored procedures that return tables. Writing stored
> procedures that return tables as multi-statement userdefined functions can
> improve efficiency."
> When to use Multi-statement table-value and when to use stored procedure?
> Thanks.
>sql

Multiple-resultset stored procedures

Hi,
Is it possible to use multiple-resultset stored procedures with Reporting
Services 2005?
I use SS2005, SP1.
Thanks in advance
Nikola MilicNo it is not. RS only can use one resultset. You could add a parameter and
based on the parameter return the appropriate resultset and then hardcode
that parameter in your report when call the SP. However, that does mean your
SP is called multiple times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:%23tPpPeB0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Is it possible to use multiple-resultset stored procedures with Reporting
> Services 2005?
> I use SS2005, SP1.
> Thanks in advance
> Nikola Milic
>

multiple transaction ?

I have a question regarding multiple transactions. I
would like to create a main stored procedure that calls
several other stored procedures. I would like to use
transactions when I write all of my SP's but am unsure
how it would works. I am running into problems when a
rollback occurs when i have multiple transactions. Is
there a standard way of setting up a commit/rollback
procedure in all SP's? I always use the following but it
does not seem to work correctly.
BEGIN TRANSACTION
IF @.ErrorCount = 0
COMMIT TRANSATION
ELSE
ROLLBACK TRANSACTION
Do i need something in here to look at the transaction
count?
Any help would be appreciated.
Thanks
B.A.I have to assume that @.ErrorCount is something of your doing so I will also
assume it is getting this correctly. When you issue a ROLLBACK, regardless
of where it's issued, all teh open transactions will be rolled back. So you
want to test to see if @.@.TRANCOUNT is greater than 0 before issuing the
rollbck otherwise you will get an error.
Andy
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:014c01c34f12$4d4b6c20$a501280a@.phx.gbl...
> I have a question regarding multiple transactions. I
> would like to create a main stored procedure that calls
> several other stored procedures. I would like to use
> transactions when I write all of my SP's but am unsure
> how it would works. I am running into problems when a
> rollback occurs when i have multiple transactions. Is
> there a standard way of setting up a commit/rollback
> procedure in all SP's? I always use the following but it
> does not seem to work correctly.
> BEGIN TRANSACTION
> IF @.ErrorCount = 0
> COMMIT TRANSATION
> ELSE
> ROLLBACK TRANSACTION
> Do i need something in here to look at the transaction
> count?
> Any help would be appreciated.
> Thanks
> B.A.
>

Multiple stored procedures,single report

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

This is somewhat interesting -- the usual complaint is "it ran okay in the query analyzer, why does it run so much slower in the report". Here you're saying "it pretty much runs exactly the same way in the report as the sum of the times in the query analyzer, and I don't like that" <g>.

So the first thing to do is to profile in the query analyzer (use the execution plans) and see what you need to optimize about that query. It sounds like whatever is in your multiple WHERE conditions needs indexes or something.

Another thing that comes to mind is checking the state of the database, especially if this db was up-converted from SQL 2000, using DBCC. I would especially look into indexes and if it is an up-convert use DBCC UPDATEUSAGE(0).

Be that as it may, this might be a situation in which we have a different way to handle it for reports. Ordinarily this is not recommended, but in your case it might work great. Here is what I have in mind:

* -- Instead of 8 procs, have one proc that returns all 8 columns and a where clause that (probably with ORs) returns the full set of data you need for your 8 tables. Now you're only going against the database once.

* -- now have your layout tables re-interpret your parameters or wherever you're getting your WHERE clauses, to filter each displayed set of data appropriately for the column they represent.

Note: proper indexes on the table, if they are not already present, will still help for that initial data pull and are still indicated. If you already have the proper indexes, try rebuilding them...

>L<

Multiple stored procedures,same set of tables

Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

first identified the common parameters from All Queries. and create a new Query and move all data in a Temp table.

and apply where clause on that temp table. this will reduce your query time.

Example.

select *

into #temp

from table1 inner join table2 on table1.t1 = table2.t1

where x= 'abc'

select * from #temp where a='aaa'

select * from #temp where a='aad'

select * from #temp where a='aaf'

select * from #temp where a='aag'

select * from #temp where a='aagg' and b ='aaa'

drop table #temp

from that way you hit database only one time and rest of the queries will be perform on small temparary data table.

|||

As long as concurrency isn't a problem, I would consider having one stored procedure build up a permanent table in tempdb, and then have the subsequent calls use that object. It will require timing to make sure that your child stored procedures don't start before the first one gets started, but it will give you the set of data (with indexes, if you need them) that can be accessed across any number of processes.

Of course, since the temp table has a given name, you can't have multiple processes running the report at the same time, You could use dynamic SQL if you had to and use a GUID for the reporting table's name in that case, though it would significantly ugly up your code.

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number used. there are around 50 users simultaneously trying to retrieve the invoice number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from the workstation, then send it back to the server via an UPDATE, there is the remote possibility of some users retrieving the same number before it being updated by the first user. (the UPDATE issued by the first user can sometimes be delayed due to slow network)
can i use stored procedures to solve this problem? i figured that if i make the SELECT and UPDATE into a single stored procedure, even if i have 50 users calling it all at the same time, each call (SELECT and UPDATE) must execute completely before the next user's call can be executed. there by making sure that the invoice number is incremented before the next user can retrieve it from the table. is this a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...
> hi,
> i have an application that uses a table to track the last invoice number
used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.
> the problem is when i issue a SELECT to get the number, increment it from
the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)
> can i use stored procedures to solve this problem? i figured that if i
make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?
> thanks in advance.
> bob
>

multiple stored procedures executed simultaneously

hi,
i have an application that uses a table to track the last invoice number use
d. there are around 50 users simultaneously trying to retrieve the invoice
number and after doing so, increment the number for the next user.
the problem is when i issue a SELECT to get the number, increment it from th
e workstation, then send it back to the server via an UPDATE, there is the r
emote possibility of some users retrieving the same number before it being u
pdated by the first user. (
the UPDATE issued by the first user can sometimes be delayed due to slow net
work)
can i use stored procedures to solve this problem? i figured that if i make
the SELECT and UPDATE into a single stored procedure, even if i have 50 use
rs calling it all at the same time, each call (SELECT and UPDATE) must execu
te completely before the ne
xt user's call can be executed. there by making sure that the invoice numbe
r is incremented before the next user can retrieve it from the table. is th
is a correct assumption?
thanks in advance.
bobbob,
If you don't want to change your architecture, you could simply SELECT it
with UPDLOCK hint before you do your update.
Or, better:
BEGIN TRANS
UPDATE
Invoice
SET
NextInvoice = NextInvoice + 1
SELECT
@.iNewInvoice = NextInvoice
FROM
Invoice
COMMIT TRANS
James Hokes
"bob" <anonymous@.discussions.microsoft.com> wrote in message
news:3BD8E37A-BF4D-4D20-B18B-9121C8607697@.microsoft.com...

quote:

---

> hi,
> i have an application that uses a table to track the last invoice number

---

used. there are around 50 users simultaneously trying to retrieve the
invoice number and after doing so, increment the number for the next user.

quote:

---

> the problem is when i issue a SELECT to get the number, increment it from

---

the workstation, then send it back to the server via an UPDATE, there is the
remote possibility of some users retrieving the same number before it being
updated by the first user. (the UPDATE issued by the first user can
sometimes be delayed due to slow network)

quote:

---

> can i use stored procedures to solve this problem? i figured that if i

---

make the SELECT and UPDATE into a single stored procedure, even if i have 50
users calling it all at the same time, each call (SELECT and UPDATE) must
execute completely before the next user's call can be executed. there by
making sure that the invoice number is incremented before the next user can
retrieve it from the table. is this a correct assumption?

quote:

---

> thanks in advance.
> bob
>

---

Multiple Stored Procedures and Transactions

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.

To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.

To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.

I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.

I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.

Thank

Why don't you add a column to the legacy output to indicate errors. That way nothing is stopped, but you can determine at what point errors start and output to the user, or react accordingly.

|||

Thank you for your response but the problem with that is I want things to stop, I dont want the data in both tables to be out of sync. The only reason we are supporting the older table is the web application is quite huge, very very large and at this point, rewriting legacy code (using classic ASP) to use our new tables would be time consuming. Additionaly, we have several apps wrote in Visual Basic 6 that would need to be rewrote as well to support our newer system. I am developing a new Payment Processing System in C# that writes to the database, and caputres additional data that is useful instead of waiting for end of month. The old system is very very inefficient but until we have the resources to go back and recode it, End of Month Reports still run off of it. These jobs take 2 hours to run (Yeah lots of data) However, the purpose of the new system is to capture some of that data that wasn't avaialble until the end of month. It does this by writing the data that one department needs concering payments and adjustments at the time that action is performed.

However, we cannot have any one table having a different value than the other. If I do it the way you stated then if there is an error, our historical end of month data will be different than data in our new table.

From my understanding, if I wrap everything in a TRY CATCH block in sql server and if any one statment or procedure fails and I am raising the error, the Outermost TRY CATCH BLOCK will also fail, thus braching off to its CATCH BLOCK and inside that CATCH Block I have a rollback. From what I have read, the outermost COMMIT and ROLLBACK are what matters and if it ROLLSBACK then everything rolls back.. I just want to make sure I am understanding this correctly.

Thanks!

John

|||

jgilmore:

Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

Yes. In fact, that's the default behavior of "nested transactions"

Seehttp://www.codeproject.com/KB/database/sqlservertransactions.aspx and scroll down to "nested transactions" but here's the nutshell answer. Assuming proc1 executes proc2, which contains a begin tran/commit, as long as proc1 wraps the call to proc2 in another begin tran/commit/rollback, a rollback done as part of the outer transaction will in fact roll back the transaction done by proc2, even if proc2's transaction has commited.

For many more articles, just google "sql server nested transaction"

|||

Thank you dbland07666!

I wanted to ensure that this worked as I expected it to.

John

Multiple stored procedures ,same tables

Hi,

I am using multiple stored procedures which are using same set of tables at a time .

As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.

My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins

where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you

Raj Deep.A

Create a parent stored procedure then create your temp tables there. Then you just call your other stored procedures. They should then be able to use the temp tables you've created in the parent stored procedure.

e.g.

Code Snippet

create procedure usp_parent

as

select *

into #tmp

from sysobjects

exec usp_child1

exec usp_child2

go

create procedure usp_child1

as

select 'usp_child1',*

from #tmp

go

create procedure usp_child2

as

select 'usp_child2',*

from #tmp

go

exec usp_parent

go

drop procedure usp_child1,usp_child2,usp_parent

go

|||

If you have SQL 2005 and your application is critical I should mirror the database (with database mirroring feature); i'll try creating a database snapshot too.

Then i should divide the execution some of sp's on a database some of other.

|||

Hi Oj,

If you know about the reports,for each select statement i want to show i need a seperate stored procedures or same stored procedure should be called multiple times with different parameters which returns different select statement based on the parameters i passed.

i cant avoid using multiple stored procedures. i think i should have an option where can i use some feature of sql server for not creating locks.

Thnk you

Raj Deep.A

|||

You can take a look at NOLOCK hint in book online.

e.g.

Code Snippet

select *

from tb with(nolock)

where col1=123

Multiple stored procedures ,same tables

Hi,

I am using multiple stored procedures which are using same set of tables at a time .

As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.

My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins

where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you

Raj Deep.A

Create a parent stored procedure then create your temp tables there. Then you just call your other stored procedures. They should then be able to use the temp tables you've created in the parent stored procedure.

e.g.

Code Snippet

create procedure usp_parent

as

select *

into #tmp

from sysobjects

exec usp_child1

exec usp_child2

go

create procedure usp_child1

as

select 'usp_child1',*

from #tmp

go

create procedure usp_child2

as

select 'usp_child2',*

from #tmp

go

exec usp_parent

go

drop procedure usp_child1,usp_child2,usp_parent

go

|||

If you have SQL 2005 and your application is critical I should mirror the database (with database mirroring feature); i'll try creating a database snapshot too.

Then i should divide the execution some of sp's on a database some of other.

|||

Hi Oj,

If you know about the reports,for each select statement i want to show i need a seperate stored procedures or same stored procedure should be called multiple times with different parameters which returns different select statement based on the parameters i passed.

i cant avoid using multiple stored procedures. i think i should have an option where can i use some feature of sql server for not creating locks.

Thnk you

Raj Deep.A

|||

You can take a look at NOLOCK hint in book online.

e.g.

Code Snippet

select *

from tb with(nolock)

where col1=123

Multiple Stored Procedure Execute Together - How?

Hi,
I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns an
d
column types.
I want to run all these different SP's as one SP and combine the output as
one result.
I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.
Create SPAll @.Parameter int
AS
Exec SP1 @.Parameter
Exec SP2 @.Parameter
GO
But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.
Thanks in advance.Hello GJ.
execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set
IE:
use Northwind
GO
set nocount on
if object_id('tempdb..#results') is not null drop table #results
create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Categories'
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Customers'
select * from #results
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"GJ" wrote:

> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results fro
m
> both SP1 and SP2.
> Thanks in advance.|||Best way, based on what you are telling us:
Create SPAll @.Parameter int
AS
create table #spAllReturn
(
<columns that match procs>
)
insert into #spAllReturn
Exec SP1 @.Parameter
insert into #spAllReturn
Exec SP2 @.Parameter
select * from #spAllReturn
GO
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
> and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
> from
> both SP1 and SP2.
> Thanks in advance.|||GJ
create table #test
(
col ...
....
....
)
insert into #test exec sp1
insert into #test exec sp2
select * from #test
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
from
> both SP1 and SP2.
> Thanks in advance.

Multiple SP Versions

Hi,
I have a database, the interface to which is completely implemented as a set
of stored procedures. I want the application which uses the database to use
side-by-side versioning, i.e. there may be more than one version of the
application current and needing to access the database.
As you can imagine, over time and versions the interface to the database may
change, so I will need to have a set of stored procedures for each version
of the application that is current. What I am thinking about is, for each
version, to have a "shell" database on the same server which only has the
stored procedures which acess the tables on the "real" database... hence
keeping separate the interface for each version of the application.
Does anyone have any comments on the performance of this arrangement? Or a
better way to go about it?
Regards
Greg BacchusHi
Stored procedures can contain a verson number see "; number" in http://msdn.micros
oft.com/l...e=tr
ue. I would not call the stored procedure through the application but not by
using dynamic
SQL everywhere.
You may well have serious maintenance issue with running more than a few ver
sions of a stored procedure, therefore you may want to think about a more fo
rmal upgrade process.
John