Insert/Update Statements Or Stored Procs

Mar 11, 2004

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

View 14 Replies


ADVERTISEMENT

Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????

Feb 7, 2008

I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRYINSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
 SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURNEND TRY
BEGIN CATCH
--Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1
END CATCH
END

View 2 Replies View Related

Should Insert, Update And Delete Stored Procs Be Wrapped In Transaction?????

Feb 7, 2008

I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie

ALTER PROCEDURE [dbo].[syl_Category_Insert]
@CategoryName nvarchar(64), @LanguageID int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY
INSERT INTO [syl_Categories]

VALUES(

@CategoryName,

@LanguageID)


SELECT SCOPE_IDENTITY() AS [CategoryID]

RETURN
END TRY

BEGIN CATCH

--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];

--Being in a Catch Block indicates failure.

--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1

END CATCH

END

View 3 Replies View Related

Finding Stored Procs That Ref/Update A Column

Jul 23, 2005

Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.

View 5 Replies View Related

Insert And Update Statements

May 14, 2002

Hello,

I am creating my companys' database and I have a small problem that must be
solved.

I have a pictures table:
PicturesTable
-------------
ProductID int ForeignKey
Picture nvarchar(30)
...

(A product can have many pictures & the ProductID is unique for any product,
but not for the table of pictures).

What I want to do is to somehow do a procedure to:
1) Check if any images (for a productID) exists in the table
2) if they do not exist then add the appropriate images into the table
3) if the images exist, then update the images with the new one that I have.

What I thought was to just delete all the images from the table for the
specific product:

DELETE FROM PicturesTable
WHERE ProductID = '10-11'

and then add the appropriate images:
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic1.gif')
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic2.gif')
INSERT INTO PicturesTable (ProductID, Picture)
VALUES ('10-11', 'Dir1/Pic3.gif')

but I do not like a lot this idea because if a user tries to read the pictures
for that product (at the same time I was deleting them) s/he would get
nothing. Is any other way that I can do it please?

I would appreciate it if someone answers me.

Yours, sincerely
Efthymios Kalyviotis
ekalyviotis@comerclub.gr

View 1 Replies View Related

Lookups And INSERT OR UPDATE Statements ...

Apr 8, 2008

Our existing DW's ETL was written in a very complex fashion by the previous team. They use DTS package lookups to read a row in the Source SQL Server database see if that row exists in the taget SQL Server database. If the row does not exist, they use ActiveX scripts to INSERT the row in the target SQL Server database. If it exists, they update the row on the target side. How would you do this in SSIS? Apologize if this sounds like a basic question, however, I would have done this via Stored Procedures or SQL Scripts especially since it involves SQL Servers alone. Appreciate any help.

View 6 Replies View Related

Additional INSERT, UPDATE, And DELETE Statements Disabled.

Apr 21, 2007

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one.  So I created a user here on www.asp.net just for making this post.
Before I continue I just want to apologies if there is another post where this question is already answered.
 

 
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “
As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.
The MDF file is located in the App_data folder and is called ASPNETDB.
And when I try to add custom SQL statements, it gives me Syntax error near “=â€?. Something like that.  I bought the Total Training Set1 package and it works perfect in their examples.
I just want to thank you for reading my post and I hope that you got some useful information for me.
By the way, I’, from Sweden so you have to excuse me if my English is rusty.
Thanks!
PS: Can it be that I’m running windows Vista?
 

View 4 Replies View Related

INSERT, UPDATE And DELETE Statements Checkbox Inactive

Mar 26, 2008

I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance

the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG 

View 7 Replies View Related

Is It Possible To Audit Failed Insert, Update And Delete Statements?

Oct 25, 2004

Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?

They also want us to track schema changes. Is this possible?

Thanks, Dave

View 5 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

SqlDataSource: Can't Gen INSERT/UPDATE/DELETE Statements; Using 2.0 Membership Aspnetdb

Jul 20, 2006

Hi,I'm new to ASP.NET and having a problem configuring the SqlDataSource control.  I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts.  The problem is this:When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out.   I've searched this forum and found that this can be a symptom of no primary keys in the tables.  However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe).  When I use the wizard, I make the following choices:How would you like to retrieve data from your database?-> Select "Specify columns from a table or view"-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list-> Select "UserId", "Email", "UserName"  from "Columns:"After this, still no option to auto-generate I/U/D statements.  Any thoughts on why this isn't working??? Thanks,Leah.

View 1 Replies View Related

SQL Data Source Control...Generate Insert, Update && Delete Statements...

Apr 23, 2008

I have an SQL data source on my page and I select "Table".  On the next screen I pick the fields I want to show.  Then I click the "Advanced" button because I want to allow Inserts, updates and deletes.  But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too.  Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success

View 2 Replies View Related

Stored Procedure With Multiple Update Statements

Jan 31, 2008

I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. "
 The procedure takes in three parameters and retrieves 23 values from the DB to display on my form.
Any ideas?
 

View 4 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Insert Or Update With Stored Procedure

Dec 27, 2006

I'm doing this more as a learning exercise than anything else.  I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key.  If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr

View 5 Replies View Related

Update/Insert Stored Procedure

Nov 16, 2004

I am trying to take some SQL queries written by Visual Studio, one insert and one update and combine them into a single stored procedure. The insert procedure should be included in the update procedure and a check should be done for an existing record based upon the primary key. If it exist, an update command should be performed, else an insert. I also need to wrap the procedure in a transaction and rollback if any errors have occurred, else commit the transaction. If I have the following Insert and Update statements, can anyone help me write the stored procedure I need? Again, the current statements were automatically created and could be modified as needed.

INSERT INTO tblClub(ClubPKID, ClubName) VALUES (@ClubPKID, @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @@IDENTITY)

UPDATE tblClub SET ClubPKID = @ClubPKID, ClubName = @ClubName WHERE (ClubPKID = @Original_ClubPKID) AND (ClubName = @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @ClubPKID)

Thanks!

View 2 Replies View Related

Insert/update In One Stored Procedure

Jul 30, 2007

Hi:

I am trying to write a stored procedure that when passed a name, value it will

1. update the record if the name exists
2. create a new record if it doesnt

how would i do this?

View 5 Replies View Related

Stored Procedure - INSERT/UPDATE

Aug 27, 2007

HI,

I€™m trying to do a Stored Procedure (SP) that stores data from a form in ASP.NET.
If the record does not exist, it will create a new record first.

This is my first SP, so if best practise is done another way, I would like to hear about it.

Code:




Code Snippet
CREATE PROCEDURE dbo.StorUserRecord

/* some of these are for future use */
@UpdateMetode Int,
@UserIndex Int,
@UserFirstName NVarChar,
@UserLastName NVarChar,
@UserPassword NVarChar,
@UserPrivateMail NVarChar,
@UserCompanyMail NVarChar,
@UserTitle Int,
@UserSecLevel NVarChar,
@UserAddressLine1 NVarChar,
@UserAddressLine2 NVarChar,
@UserZipCode Int,
@UserPrivateMobilNumber NVarChar,
@UserCompanyMobilNumber NVarChar,
@UserHomeNumber NVarChar,
@UserCompanyDirectNumber NVarChar,
@UserCPR NVarChar,
@UserWorkerID NVarChar,
@UserDepartment Int,
@UserActive Bit,
@UserAccess Bit,
@UserDeleted Bit
AS

BEGIN


DECLARE @UseThisUserIndex Int;
IF NOT @UserIndex > 0

/* Create new record and return the "auto id" from UserIndex */
BEGIN

INSERT INTO [User] (UserFirstName) VALUES (@UserFirstName)

OUTPUT INSERTED.UserIndex

INTO @UseThisUserIndex
GO
END
ELSE

/* Use the "auto id" from the @UserIndex */
BEGIN

@UseThisUserIndex = @UserIndex
END

END
RETURN





I'm getting this error:


Msg 102, Level 15, State 1, Procedure StorUserRecord, Line 33

Incorrect syntax near 'OUTPUT'.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'ELSE'.

View 6 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Calling Update And Insert In One Stored Procedure

Jan 25, 2006

Hello,
Basically i want to have a stored procedute which will do an insert statement if the record is not in the table and update if it exists.
The Id is not autonumber, so if the record doesn't exists the sp should return the last id+1 to use it for the new record.
Is there any example i can see, or somebody can help me with that?
Thanks a lot.

View 4 Replies View Related

Stored Procedure For Insert / Update And Delete

Nov 26, 2013

How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.

CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL

[code]...

View 5 Replies View Related

Stored Procedure - INSERT INTO Or UPDATE - INNER JOIN TWO TABLES

Jun 13, 2008

Hi all,can somebody help to write this stored procedure  Table1                   Table2LogID                    MigIDUserMove              LogIDUserNew               Domain                            User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks

View 1 Replies View Related

Insert, Update Issue - Stored Procedure Workaround

Apr 30, 2006

any stored procedure guru's around ?

I'm going nuts around here.
ok basically I've create a multilangual website using global en local
resources for the static parts and a DB for the dynamic part.
I'm using the PROFILE option in asp.net 2.0 to store the language preference of visitors. It's working perfectly.

but Now I have some problems trying to get the right inserts.

basically I have designed my db based on this article:
http://www.codeproject.com/aspnet/LocalizedSamplePart2.asp?print=true

more specifically:
http://www.codeproject.com/aspnet/LocalizedSamplePart2/normalizedSchema.gif



ok now let's take the example of Categorie, Categorie_Local, and Culture

I basically want to create an insert that will let me insert categories into my database with the 2 language:

eg.
in categorie I have ID's 1 & 2
in culture I have:
ID: 1
culture: en-US
ID 2
culture: fr-Be

now the insert should create into Categorie_Local:

cat_id culture_id name
1 1 a category
1 2 une categorie


and so on...


I think this thing is only do-able with a stored procedure because:

1. when creating a new categorie, a new ID has to be entered into Categorie table
2. into the Categorie_local I need 2 rows inserted with the 2 values for 2 different cultures...



any idea on how to do this right ?
I'm a newbie with ms sql and stored procedures :s



help would be very very appreciated!
thanks a lot

View 1 Replies View Related

Transact SQL :: Stored Procedure To Update And Insert In Single SP

Jul 17, 2015

I have Table Staffsubjects with columns and Values
            
Guid  AcademyId  StaffId  ClassId  SegmentId  SubjectId   Status

 1      500       101        007     101       555           1
 2      500       101        007     101       201           0
 3      500       22         008     105       555           1

I need to do 3 scenarios in this table.

1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and  SegmentId<>@SegmentId and  SubjectId<>@SubjectId

I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate

Here is the stored Procedure what i have wrote

ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier

[Code] .....

View 10 Replies View Related

Transact SQL :: Insert Or Update Stored Procedure Return ID

Nov 1, 2015

I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?

ALTER PROCEDURE [dbo].[AddNode]
@Name VARCHAR(15),
@Thumbprint VARCHAR(40),
@new_identity [uniqueidentifier] = NULL OUTPUT
AS
BEGIN
UPDATE dbo.NODES

[Code] ....

View 7 Replies View Related

SQL Server Insert Update Stored Procedure - Does Not Work The Same Way From Code Behind

Mar 13, 2007

All:
 I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID  int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
 Begin  begin transaction
   --Insert record   Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4)    Values (@FLD1, @FLD2, @FLD3,@FLD4)   SET @FID = SCOPE_IDENTITY();      --Check for error   if @@error <> 0    begin     rollback transaction     select @rtncode = 0     return @rtncode    end   else    begin     commit transaction     select @rtncode = 1     return @rtncode    end      endELSE
 Begin  begin transaction
   --Update record   Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4    where FormID=@FID;
   --Check for error   if @@error <> 0    begin     rollback transaction     select @rtncode = 0     return @rtncode    end   else    begin     commit transaction     select @rtncode = 2     return @rtncode   end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 Thanks,
Blue.

View 5 Replies View Related

General Stored Procedure, For Insert, Update, Select, Delete?

May 7, 2007

Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin

exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out


end
 
Is this a good approach design, or its bad???
 
Thanks all

View 5 Replies View Related

SQL Server 2012 :: Stored Procedure To Update And Insert In Single SP

Jul 17, 2015

I have Table Staffsubjects

with columns and Values

Guid AcademyId StaffId ClassId SegmentId SubjectId Status

1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1

I need to do 3 scenarios in this table.

1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId

I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate

Here is the stored Procedure what i have wrote

ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]

@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier

[Code] ....

View 8 Replies View Related

Instead Of Insert, Update Trigger Calling A Stored Procedure Question

Oct 26, 2006

I have to control my business rules in a Instead of Insert, Update Trigger.

Since the Control Flow is quite complicated I wanted to break it into stored procedures that get called from within the trigger.

I know that Insert Statements embedded in a Instead of Trigger do not execute the Insert of the trigger you are calling.



But... If I embed stored procedures that handle my inserts in the Instead of Insert trigger call the trigger and put in a endless loop or are the stored procedure inserts treated the same as trigger embedded inserts.

View 7 Replies View Related

Writing Insert And Update Stored Procedures For Normalized Schemas?

May 25, 2006

I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.

Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.

Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?

I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!

View 1 Replies View Related

I Have One Stored Procedure For Insert, Update And Delete Operations, I Need Automatically Logged

May 27, 2007

I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks

View 1 Replies View Related







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