SQL Server 2014 :: Stored Procedure That Inserts And Updates A Table With Excel Data?

May 27, 2014

I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?

View 6 Replies


ADVERTISEMENT

Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data

Dec 19, 2007

I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:




Code Block

create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.

For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.

If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.

How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?

View 4 Replies View Related

Counting The Inserts And Updates On A Table In A Sql Server Database

Jul 20, 2005

Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish

View 3 Replies View Related

SQL Server 2014 :: Import Data From Excel To Table - Null Instead Of 0 In Columns

Oct 29, 2013

I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.

View 8 Replies View Related

Creating Stored Procedure That Inserts Value In Table

Feb 13, 2008

I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this.
this is my table info
table = BusinessInfo
Columns = BusinessID, BusinessName, BusinessAddress
how can i create a stored procedure ?

View 1 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

Inserts And Updates To A Table That Contains A Unique Key Constraint

Nov 5, 2007

I am looking for pros and cons for the following scenarios:

When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?

Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.

View 4 Replies View Related

SQL Server 2014 :: Automating Random Inserts Into A Memory Optimized Table

Jan 28, 2015

I have this table

CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),

[Code] ....

I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.

View 3 Replies View Related

Integration Services :: Loading Data From Multiple Excel Sheets To Server 2014 Table

Aug 5, 2015

I have one excel sheet contains 50 sub sheets with different names on it. Is it possible can i load all sheets into SQL using SSIS?

View 2 Replies View Related

Help: How To Detect Inserts, Updates, Deleted On A Table From Within C++ Application?

Jul 20, 2005

Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.

View 2 Replies View Related

SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp

Jun 22, 2015

I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.

insert into Table_Test execute sp_who

View 2 Replies View Related

SQL Server 2014 :: Execute Stored Procedure To Update A Table / Invalid Object Name

Jan 21, 2015

I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.

BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]

[code]....

View 2 Replies View Related

Does The Configure Data Source Wizard Work For Updates,Inserts, And Deletes ?

Jun 8, 2007

Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks! 

View 5 Replies View Related

Exporting Data From SQL Server To Excel From A Stored Procedure

Oct 1, 2004

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

Thanks

View 1 Replies View Related

SQL Server - Checking Inserts / Updates After DTS Package

Oct 30, 2007

Hi All,

I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.

I currently use a DTS package to perform inserts / updates to a "production" table.

The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.

The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.

When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.

I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).

I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.

Any help will be greatly appreciated.

Many thanks.

Cheers,

David

View 8 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Access To SQL Server 2000 Pauses With Lots Of Inserts, Updates, ...

Nov 21, 2007



Hi,
I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.

If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?

Thanks
Hans

View 1 Replies View Related

SQL Server 2008 :: Add A Trigger That Inserts Original Data From 1 Table To Another

Apr 10, 2015

I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,

[Code] ....

When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,

[Code] .....

The trigger I've currently written looks like this:

/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]

[Code] ...

This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?

I can't sort by uniqueidentifier in descending as those can be random.

View 9 Replies View Related

2 Inserts In 1 Stored Procedure?

Sep 9, 2006

How do I include 2 insets in 1 stored procedure? code i have is:


ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE signUp
-- Add the parameters for the stored procedure here
@username varchar(24),
@password varchar(32),
@firstname varchar(30),
@lastname varchar(30),
@address varchar(50),
@town varchar(20),
@city varchar(20),
@postCode varchar(8),
@email varchar(60),
@dob datetime




AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert into MembersDetails(
"Username",
"Firstname",
"Lastname",
"address",
"Town",
"City",
"PostCode",
"Email",
"Dob"
)
values (
@username,
@firstname,
@lastname,
@address,
@town,
@city,
@postCode,
@email,
@dob
)

-- how do i add the second??? using an ";" or "," or what?
insert into Members(
"Username",
"Password"
)
values (
@username,
@password
)
END
GO

View 5 Replies View Related

Updates, Inserts

Jan 22, 2001

I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent

View 2 Replies View Related

Copy Data From 1 Table To Other In Stored Procedure In Sql Server

Apr 15, 2004

Hi there,

Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so.
Please help.
Thanx

View 2 Replies View Related

Stored Procedure - Import CSV File Into Table In Excel

Mar 3, 2014

I have the following code to import .csv file into my table in excel. It's being inserted into a table. dbo.ImportedPromoPricing. Table and the .csv file have 3 fields price, code and selling price.

Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] .....

View 2 Replies View Related

Stored Procedure For Multiple Inserts

Feb 24, 2008

Hello all,

I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.

Imagine a site that tracks movies. I have 3 tables:

Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)

This is an overly simple example, but it gets to the heart of my problem.

Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance

create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )

that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry

Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.

Thanks.

View 3 Replies View Related

Cascaded Inserts And Updates?

Nov 28, 2007

Hi

I have 3 table (tbl1, tbl2, tbl3).

tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid

tbl2 columns---Pid, CatId,Fid

tbl3 Colums----Pid,BundleId

Using Trigger(After Insert) in tbl1 to insert the data of tbl1.Pid to tbl2.Pid and tbl3.Pid . tbl1 will be uploaded with new rows and existing rows will be updated from the csv file.

Trigger in tbl1 works fine by inserting data to tbl2.Pid and tbl3.Pid.

My questions are :
1. Is it possible to use trigger to update tbl2 and tbl3 if records exist if not insert new record to colum Pid in tbl2 and tbl3 from Pid of tbl1

2.Or is it possible to use sql procedure to do cascaded updates or inserts by merging datasets from tables and csv file using asp.net

3.Or How can I use single Trigger to insert if not exist and update if data exist.

4.Or Do I have to use two trigger one for insert and one for update. If this is the case how do check for data existance.

After insert Triger I am using at the moment is below (replied as an answer in this forum for my previous thread)

create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end

View 5 Replies View Related

Inserts And Updates Issue Using Sp

Sep 15, 2006

Hi, i have the following sp. im using vs.net2005, sqlserver2005.

now how do i implement functions like - > okay using datasets, exception is thrown that the row has been modified and i get to inform the user, reload the modified row, .

how do i get same functionality using sp? is that possible ? how/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER Procedure [dbo].[sp_Insert_Or_Update_Bill]

@BillID_All uniqueidentifier,

@BillID uniqueidentifier,

@Pono nvarchar(25),

@Date_ smalldatetime,

@SupplierCode nvarchar(25),

@Reference nvarchar(25),

@AmountType nchar(10),

@BillType nvarchar(25),

@TypeCode nchar(10),

@AmountFC decimal(18,2),

@ROE decimal(9,2),

@Currency nchar(5),

@LinkBill uniqueidentifier,

@multiplicityID uniqueidentifier,

@payment_or_bill smallint

as

If Exists(Select * from bill where billid_all= @BillID_All and amounttype = @amounttype)

begin

update bill

SET [BillID_All] = @BillID_All

,[BillID] = @BillID

,[Pono] = @Pono

,[Date_] = @Date_

,[SupplierCode] = @SupplierCode

,[Reference] = @Reference

,[AmountType] = @AmountType

,[BillType] = @BillType

,[TypeCode] = @TypeCode

,[AmountFC] = @AmountFC

,[ROE] = @ROE

,[Currency] = @Currency

,[LinkBill] = @LinkBill

,[multiplicityID] = @multiplicityID

,[payment_or_bill] = @payment_or_bill

where billid_all= @BillID_All and amounttype = @amounttype

end

else

if not @AmountFC = 0

begin

begin

INSERT INTO [Costing].[dbo].[Bill]

([BillID_All]

,[BillID]

,[Pono]

,[Date_]

,[SupplierCode]

,[Reference]

,[AmountType]

,[BillType]

,[TypeCode]

,[AmountFC]

,[ROE]

,[Currency]

,[LinkBill]

,[multiplicityID]

,[payment_or_bill])

VALUES

(@BillID_All

,@BillID

,@Pono

,@Date_

,@SupplierCode

,@Reference

,@AmountType

,@BillType

,@TypeCode

,@AmountFC

,@ROE

,@Currency

,@LinkBill

,@multiplicityID

,@payment_or_bill)

end

end

View 1 Replies View Related

SQL Server 2014 :: Stored Procedure With Override?

Nov 5, 2014

I received a request to create a stored proc with following:

I have a view with the following columns

(table1 - AppCode, AgencyID, CompnyID, CustCode, CustVal)

I have a proc that will take the following parameters and return all matching rows (AppCode, AgencyID, CompnyID, CustCode(optional))

The trick: Any customcode with the CompnyID should override the AgencyID parameter.

View 3 Replies View Related

SQL Server 2014 :: Unblocking Stored Procedure

Jul 4, 2015

The other day I was asked to build a SQL Server process to terminate blocking sessions that could be safely destroyed in order not to drain necessary instance resources. The solution is made of below stored procedure and by a SQL Server Agent jobs that runs every 3 minutes just to invoke the sproc. The T-SQL code should be easy to read and has plenty of remarks.

USE [<<yourDBAgoodStuffDatabase...>>]
GO
SET ANSI_NULLS ON
GO

[code]...

View 3 Replies View Related

SQL Server Admin 2014 :: Inserts Causing Locking In Production DB

Apr 2, 2015

I have a production DB that all of a sudden it seems that any and every insert causes massive locks/blocks.

If I kill the offending spid anther spids pops up with the block/lock.!

View 7 Replies View Related

SQL Server Admin 2014 :: Reducing Index Fragmentation During Inserts

Apr 26, 2015

We have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.

This is the DDL for the table:

CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED

[code]...

There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:

1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)

2. Partition the table

3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.

View 9 Replies View Related

Integration Services :: Load Data From Excel 2007 Version Into Server 2014

Aug 5, 2015

I am trying to load data from Excel 2007 version into SQL server 2014 DB. I am getting below error" SSIS Error Code DTS_E_CANNOTACQUIRE CONNECTION FROM CONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed".I have tried all options like changing Delay Validation is TRUE and in properties i changed runtime 64 bit to FALSE but still getting above error.

View 3 Replies View Related

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?

Aug 21, 2007

View 8 Replies View Related

SQL Server 2014 :: Merge Replication Synchronization In Stored Procedure

Oct 24, 2014

I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?

View 1 Replies View Related

SQL Server 2014 :: Using (Try-Catch) And (Rollback) On Read Only Stored Procedure?

Apr 30, 2015

In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.

So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.

Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?

I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved