Multi-Value Parameter

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:

Code Snippet

SE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

what am i doing wrong?

You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.

Expression used to populate the parameter

=Join(Parameters! <ParameterName> .Value,", ")

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

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 Values Returned from Stored Proc

I need to return 2 values from a stroed proc. Can you have more than one
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:

> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com

Multiple Values for single row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int --> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

if it helps..iam using sql server express edition..and iam accessing using asp.net/C#Hi,

Create a contact list table which includes columns,

UserId,
ContactId

Eralper

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 tables in the dataset

Hi,
I have a stored proc which returns multiple result sets. I think SSRS picks
up the first one by design. What's the best way forward, a data extension, or
is there a simpler way?
Thanks in advance,
Regards,
DattaYou need to split each result set into a uniqe dataset in RS. It can't
handle multiple result sets. Can you create new stored procedures from the
queries in the original one?
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> Hi,
> I have a stored proc which returns multiple result sets. I think SSRS
> picks
> up the first one by design. What's the best way forward, a data extension,
> or
> is there a simpler way?
> Thanks in advance,
> Regards,
> Datta|||Thanks, I suspected so.
I can split the procedures, but I think a custom data extension is the right
way to go, if there is no in-built support.
"Kaisa M. Lindahl Lervik" wrote:
> You need to split each result set into a uniqe dataset in RS. It can't
> handle multiple result sets. Can you create new stored procedures from the
> queries in the original one?
> Kaisa M. Lindahl Lervik
> "Datta" <Datta@.discussions.microsoft.com> wrote in message
> news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> > Hi,
> >
> > I have a stored proc which returns multiple result sets. I think SSRS
> > picks
> > up the first one by design. What's the best way forward, a data extension,
> > or
> > is there a simpler way?
> >
> > Thanks in advance,
> > Regards,
> > Datta
>
>

Multiple tables from within a stored procedure - how?

Hi,
I'm currently analyzing SSRS as a potential replacement for Crystal Reports
(and yes, it's definitely going to replace it). I do have one issue,
however... I'm attempting to point SSRS to a stored procedure that returns
four separate tables, but only the first one seems to be returned.
Is there a way to have access to each of the tables within the sproc?
Regards,
ScottNo there is not and this has not changed in RS 2008.
The most you can do is to have a parameter that selects which one you want
and then when calling the stored procedure set that parameter to a constant
value. If used in a report and all four are desired you have to call it four
times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott McNair" <smcnair@.beachexpress.takethispartout.com> wrote in message
news:Xns9A218ECAC3E7Adonquixote235gmailco@.207.46.248.16...
> Hi,
> I'm currently analyzing SSRS as a potential replacement for Crystal
> Reports
> (and yes, it's definitely going to replace it). I do have one issue,
> however... I'm attempting to point SSRS to a stored procedure that returns
> four separate tables, but only the first one seems to be returned.
> Is there a way to have access to each of the tables within the sproc?
> Regards,
> Scott

Multiple tables from a dataset

Hi all,
Is it possible to use multiple tables being returned from the Stored
Procedure in a dataset on my report. it seems the dataset in the report
layout uses just the first data table.
thanx
regards,
KalpnaI remember reading somewhere in the book "Micrsoft Sql Server 2000 Reporting
Services" that reporting services do NOT support multiple datasets being
returned by stored procs.
-stan
"kalpna" <kalpna.prakash@.grapecity.com> wrote in message
news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the report
> layout uses just the first data table.
> thanx
> regards,
> Kalpna
>|||Hi
AnyBody can locate that MSDN link.
Thanks
Amit
"Stan Huff" <no!_spam!_stanhuff@.yhaoo.com> wrote in message
news:#op8SZ6gEHA.1392@.TK2MSFTNGP11.phx.gbl...
> I remember reading somewhere in the book "Micrsoft Sql Server 2000
Reporting
> Services" that reporting services do NOT support multiple datasets being
> returned by stored procs.
> -stan
> "kalpna" <kalpna.prakash@.grapecity.com> wrote in message
> news:u4Im5Q1gEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > Is it possible to use multiple tables being returned from the Stored
> > Procedure in a dataset on my report. it seems the dataset in the report
> > layout uses just the first data table.
> >
> > thanx
> > regards,
> > Kalpna
> >
> >
>|||kalpna wrote:
> Hi all,
> Is it possible to use multiple tables being returned from the Stored
> Procedure in a dataset on my report. it seems the dataset in the
> report layout uses just the first data table.
Hello Kalpna,
What I experienced by now, is that you get only the data from the first
select statement. So I think you have to create a second datasource, as
I usually do, if i have two tables in my report.
Manfred

Multiple Table Queries

I am having propblems creating a multiple table query for a stored procedure.

Here is what I have..

db1 = transaction database with multipke fields keyed on transid, and membid

db2 = transaction legal details keyed on transid & legalid

db3 = member details keyed on membid & officeid

db4 = office details keyed on officeid & membid

What I am after is a query which will result in ALL details being returned for a particular transaction

ie. transaction detail, legal detail, member detail & office detail...

please helpsomething like...

select db1.*, db2.*, db3.*, db4.*
from db1
join db2 on db1.transid = db2.transid
join db3 on db1.membid = db3.membid
join db4 on db3.officeid = db4.officeid|||Thanks, worked like a charm.

Much Obliged.. have more queries to do for this project, I am sure I will have more posts for you.

multiple table entry in a stored procedure

is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?

Yes it is possible.

After the first insert call "SCOPE_IDENTITY() " .

This will retrieve the ID number from the newly inserted row. This can be stored in a variable and entered into a second insert :)

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...or 1 dynamic procedure?

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
JustinHi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH|||Hi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH

Hi,

Yeah I did have a quick read of the dynamic sql like Jesse provided...and was able to solve the previous problem with it...as i was a bit under the pump when i read it, i did not read the entire thing, and missed anything relating different amounts of dynamic columns...

You have pretty much answered what i was asking anyway, so it looks like im in for a long session of writing stored procs...

Thanks Again,
Justin