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!
We have two databases right now that house miscellaneous small applications. We have them in place so developers can develop small applications in them without going through the database request process and bother the DBAs. This was set up before I got here - so I wasn't involved in the decision to do such a thing. The obvious drawback to this is that a restore would wipe out data for multiple applications. As we move to a new 2005 server we are re-evaluating our methods and would like some input on this - and some other aspects.
I know that we could use filegroups and put objects related to certain applications within them so restore is independent of other apps. Each application has its own user which is granted execute on it's stored procedures. Are we missing anything here? I'm almost tempted to try to get separate databases created to uncomplicate it. If a developer can spend hours working on an app, they can spend 10 minutes on a form and wait 30 for us to create it.
Another thing I've noticed it that it can take quite a while to grant permissions to many stored procedures to a user in 2005. In 2000 there was a grid and you could arrow down and hit space, granting execute. You could also use code to do this, be we never really needed to since developers granted as they went and we would script the object and check the 'script obect level permissions' checkbox. This has since disappeared and granting execute is another step for us.
What do you think of 'sub schemas' for each role in the application - which is usually 'User' and 'Admin'? So we would have MylittleApp_User.ProcedureName and MylittleApp_Admin.ProcedureName with execute granted on the schema for each application user. Tables would be placed in MylittleApp schema.
In the past developers simply prefix their procedure with the application name to denote what application it belongs to.
Thanks for your advice on this - we don't want to get headed down the wrong path.
Sorry, not sure if this is the right forum for this but hopefully you may be able to give me an answer.I have a website which fetches data from a SQL Server database using stored procedures, so I only grant execute rights to the proceduresto avoid granting select rights to the tables. This has been working fine but I would like to try writing this code in C# instead. I have writtenstored procedure that runs a select to test, and I am getting the error that the user has no rights to do select over the table.My question is, is the code writen this way (in C#) considered equivalent to dynamic SQL (which would require select rights over the tables)?Would this mean that I want to code my procedures in C# I need to grant select privileges over the tables to the db user?Thanks for your time
Dear All,Please suggest some of the best practices for writing SQL serverstored procedures?I'm writing a business function (stored procedure), which callsmany-stored procedure one after another.I want this to be best optimized, so that speed can be very good.Suggestion in this regard will be appreciated.Thanks in advance,T.S.Negi
I’m currently writing a web application on student exam timetables, I’m using SQL Server 2005 as the back-end for the database.
At present, the case states that if a student is in one examination, he/she can’t attend, or be allocated another examination while the first examination is in place, which would result in a clash
The way I’m going to target this is by writing a stored procedure in SQL Server 2005 to return an error code, which I’ll translate using ASP,NET, however at present I’m having difficulty writing the SQL code. This is because…
I’m using SQL Server 2005 Management Studio; I created the tables using MS Access and upsized them using the wizard. I can now access my database, but having difficulty editing my tables and with code…
I have a Product Table. And now I have to create its Stored Procedures. I am asking the best practice regarding the methods Insert And Update. There are two options. 1. Create separate 2 procedures like InsertProduct and UpdateProduct. 2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his article http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 Can any one explain the better one. Waiting for helpful replies. http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 a
I have an allergy table which has a patientid and an allergy id. i would like to create a view(or SQL statement) that will give me a crosstab of a patient and there allergies(like below)
Hi, I wonder if anyone knows any good web tutorials or books where I can learn how to make update using sprocs (in ASP.NET and MSSQL environment). Thanks
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using: USE [DBName] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[AddToProject] (@DeptCode varchar(20), @ProjectTitle varchar(300), @ProjectDetails varchar(3000), @ProjectManagerID int, @RequestedBy varchar(100), @DateRequested datetime, @DueDate datetime, @ProjectStatusID int) AS BEGIN TRANSACTION SET NOCOUNT ON
DECLARE @Dept varchar(50), @ProjID varchar(50)
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
GO I get the following errors: Msg 128, Level 15, State 1, Line 1The name "BAT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@ProjID".Msg 515, Level 16, State 2, Procedure AddToProject, Line 17Cannot insert the value NULL into column 'ProjID', table 'NYCEDC.dbo.tbl_Project'; column does not allow nulls. INSERT fails. If I run just the GetProjID sp, I get the following error: Must declare the scalar variable "@ProjID". -------------------------------------What I'm trying to do is, get a bunch of values from the aspx page, insert the DeptCode into the field DeptCode of the table 'tbl_ProjectNumber'+DeptCode so that the IDENTITY value field 'ProjectNumber'+DeptCode can increment by 1. After 'ProjectNumber'+DeptCode has incremented, I want to run the GetProjID sp so that I can insert ProjID into the 'tbl_Project' table along with the other values taken from the aspx page. ProjID is the primary key for the 'tbl_Project' table so it needs to be added along with the other values (other they obviously can't be added). I'm running the AddToProject as a Transaction to make sure that if more than 1 person is adding a project at the same time, the project numbers and other information don't get messed up. Any help would be appreciated.Thanks.
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not. CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO <asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button> Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub ____________________________________________________________ CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO <asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button> Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
hi i need to insert the list of ipaddress using stored procedures. the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values. how to do this..
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE ----- CREATE TABLE sequences ( -- sequence is a reserved word seq varchar(100) primary key, sequence_id int );
MS SQL SERVER STORED PROCEDURE: ------------------------------- CREATE PROCEDURE nextval @sequence varchar(100)AS BEGIN -- return an error if sequence does not exist -- so we will know if someone truncates the table DECLARE @sequence_id int set @sequence_id = -1
UPDATE sequences SET @sequence_id = sequence_id = sequence_id + 1 WHERE seq = @sequence
Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?
I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I setup the databse and Visual Web Developer to use stored procedures when the insert command is used. The database also uses the field UserName that I pass using a SessionParameter within the InserParameter block from the Membership.GetUser.Username from the aspnet_ tables.
My difficulty is when using the "Formview" as the body to with which to insert (I wanted the design versatility of FormView) I get an error: "system.formatExpression: Input string was not in a correct format" when I leave the textboxes empty and invoke the insert command. I first assumed that the bound textboxes are not being converted correctly when left blank, so I used the ConvertEmptyStringToNull=True, with no success. I then coupled that with the Type=[correct format] still with the same error. Alas, my final attempt was to set defaults in the "ALTER PROCEDURE" within the stored procedure itself.
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.
Hence, I wanted to write a script, Here the code below.
-- These two variables must contain a valid database name. DECLARE @vchSearch VarChar(15), @vchReplacement VarChar(15)
SET @vchSearch = 'Search' SET @vchReplacement = 'Replacement' /* -- Select the Kaplan Database Names in the Current Server */
DECLARE @tblDBNames TABLE (vchDBName VarChar(30)) INSERT INTO @tblDBNames SELECT Name FROM MASTER.DBO.SYSDATABASES WHERE Has_DBAccess(Name)=1 And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')
--SELECT * FROM @DBNames
IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames) BEGIN PRINT 'Not a Valid Search DB Name' GOTO Terminate END IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames) BEGIN PRINT 'Not a Valid Replacement DB Name' GOTO Terminate END
-- We have Valid DB Names, lets proceed... --USE @vchReplacement
SET @vchSearch = '%' + @vchSearch + '..%' SET @vchReplacement = '%' + @vchReplacement + '..%'
-- Get Names of Stored Procedures to be altered DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))
INSERT INTO @tblSProcNames SELECT DISTINCT so.Name FROM SYSOBJECTS so INNER JOIN SYSCOMMENTS sc ON sc.Id = so.Id WHERE so.XType='P' AND sc.Text LIKE @vchSearch ORDER BY so.name
-- Now, the table @tblSprocNames has the names of stored procedures to be updated. -- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to -- update the database reference -- Then, use cursors to loop through each stored proc and upate the reference
Now, I have got stuck how to extract the body of a stored procedure into a variable.
Please Help.... I dont want spend weeks of time in the future to do this work manually.
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
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
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)
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.
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
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
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
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
