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 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
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
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---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
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]
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.
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
Hi, I'm reasonably new to ASP.NET 2.0 I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete. For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures). I desperately need the syntax to: a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need). b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters). c) syntax on how to set the values for these parameters (again...coming from form controls and session variables) d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource. If anybody has sample code or a link or two, I would be most appreciative. Thank you in advance for any help!
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
hi need help how to send an email from database mail on row update from stored PROCEDURE multi update but i need to send a personal email evry employee get an email on row update like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[AddPhoto] @AlbumID int, @Caption nvarchar(MAX) AS INSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified]) VALUES ( @AlbumID, @Caption, 'tmpLocation', /* tmpLocation needed because app broke when Location column set to Allow NULLs */ GetDate()) /* Retrieve generated PhotoID */ DECLARE @PhotoID int SET @PhotoID = SCOPE_IDENTITY() /* Build unique location path from album and photo ID */ DECLARE @Location nvarchar(MAX) SET @Location = '' + CONVERT(nvarchar(10), @AlbumID) + '' + CONVERT(nvarchar(10),@PhotoID) + '.jpg' /* Update photo with new location path */ UPDATE [Photos] SET [Location] = @Location WHERE [PhotoID] = @PhotoID
/* Update photo with new location path */ ******************************************The code never executes the statement below******************************************** INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate]) VALUES ( '1234', 'test', getdate() ) /* Return PhotoID and Location */
Can someone walk me through the code for my update_command event?Every article I read and every tutorial I walk through has a slightly different way of doing this task.It's confusing trying to understand which code-behind variables I need in my update_command event and how to pass them to a stored procedure. Please help me connect the dots. I have a SQL server table that looks like this (Both data types are char) Status_Id Status_DescriptionA ActiveP Planned I have a SQL stored procedure that looks like this… create procedure dbo.usp_Update_Status_Master(@status_id char(1),@status_description char(30))asupdate status_masterset status_description = @status_descriptionwhere status_id = @status_idGO Here is my code behind… Imports SystemImports System.DataImports System.Data.SqlClientImports System.ConfigurationImports System.Data.OdbcPublic Class WebForm1 Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then Call LoadStatusMasterGrid() End If End Sub Public Sub LoadStatusMasterGrid() Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("sqlConn")) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("usp_Select_Status_Master", connection) Command.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(Command) Dim table As DataTable = New DataTable adapter.Fill(table) dgStatusMaster.DataSource = table dgStatusMaster.DataKeyField = "status_id" dgStatusMaster.DataBind() Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub Private Sub dgStatusMaster_EditCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.EditCommand dgStatusMaster.EditItemIndex = e.Item.ItemIndex dgStatusMaster.DataBind() Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_CancelCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.CancelCommand dgStatusMaster.EditItemIndex = -1 Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_UpdateCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.UpdateCommand ‘ How do I code this part? End SubEnd Class Thanks in advance for taking the time.Tim
hi i have 2 columns linked ( 1 to a textbox and the other to drop down list) when i try to update i get 1 of the to the update SP but not the other and get this error Procedure or Function 'Update_ActiveCity' expects parameter '@Cities', which was not supplied.
_________________ this is the code of the aspx ____________
here is the procedureALTER PROCEDURE dbo.UpdateContact (@ContactId bigint,@FirstName nvarchar(50), @LastName nvarchar(50),@Telephone nvarchar(50), @Addressline nvarchar(150),@State nvarchar(100), @City nvarchar(100),@PostalCode varchar(50), @Email nvarchar(50), @MobilePhone varchar(50)) AS SET NOCOUNT ON UPDATE ContactSET FirstName = @FirstName, LastName = @LastName, Telephone = @Telephone, MobilePhone = @MobilePhone, Email = @Email, Addressline = @Addressline, City = @City, State = @State, PostalCode = @PostalCodeWHERE ContactId = @ContactId
RETURN what is the problem if i execute the storedprocedure separately it is working but when i call the storedprocedure in the code it fails. It gives an error as "syntax error near Updatecontact" Any ideas???
I have an Update stored procedure that I am trying to update in the query analyzer to make sure it works, because it is not working from .NET. Here is the stored procedure: CREATE PROCEDURE Update_Homeowner (@TransactionID int, @DealerID varchar (50), @FirstName varchar(50), @LastName varchar(50), @Add1 varchar(50), @Add2 varchar(50), @City varchar(50), @State varchar(50), @Zip varchar(50)) AS UPDATE Homeowner SET @DealerID=DealerID, @FirstName=FirstName,@LastName=LastName,@Add1=Add1,@Add2=Add2,@City=City,@State=State,@Zip=Zip WHERE TransactionID = @TransactionID GO Here is how I am calling it in the Query Analyzer: Update_Homeowner 47,'VT125313','test','tests','barb','','test','mo','23423' It will not update, but I get the message (1 row(s) affected). Any ideas???Thanks,Barb Cox
What am I doing wrong in this code:<CODE>Select Results.custIDFrom Results If (Results.custID = DRCMGO.custID)Begin Update Results SET Results.DRCMGO = 'Y'ENDELSEBegin Update Results SET Results.DRCMGO = 'N'END<CODE>I'm trying to do an IF / ELSE statement:-- if the custIDs in my Results table and my DRCMGO table match then I want to set DRCMGO to Y-- if they don't match I want to set it to NWhat is wrong with this syntax. If someone could let me know i would greatly appriciate it (I'm doing it as SQL Books Online is telling me to) Thanks in advance everyone. RB
Trying to Get this to work correctly...I Only want the latest(meaning most recent) entry of the Name(Column) Database = ProductsTest2To be also entered into Name(Column) Database = LocationOutsideUSABut When I run the code below it updates all fields that are contained in the entire [Name(Column)] of Database = LocationOutsideUSA with the same data entered.Thanks Inadvance...____________________________________________________________________________________UPDATE LocationOutsideUSASET Name = ProductsTest2.NameFROM ProductsTest2SELECT MAX(Name) AS MaxName FROM ProductsTest2WHERE ProductsTest2.UID = ProductsTest2.UID
Is it possible to update a temporary file inside a stored procedure from calling another procedure. I am trying to leverage a sp that does a custom pricing routine and want to call it from another sp like so, the second procedure returns a set of records, very simular to a select.
UPDATE #tb_items SET price = T1.sellprice, freight = T1.freight FROM (usp_pricecalculator '700', '', '', '', '("B354-20")' ,'1') T1 WHERE #tb_items.itnbr = T1.itnbr
I have also looked into calling this sp into a cursor and updating in a loop on the cursor and had no luck
CREATE PROCEDURE dbo.Sp_Del_Req_Record ( @abrID int, @logl_del_dt datetime, @phys_del_dt datetime ) AS UPDATE DIM_ABR_REQ_DETLS SET ABR_DETLS_LOGL_DEL_DT = @logl_del_dt, ABR_DETLS_PHYS_DEL_DT = @phys_del_dt WHERE ABR_DETLS_ID = @abrID GO
I have the following command code: Dim Sp_Del_Req_Record__abrID Sp_Del_Req_Record__abrID = "" if(Request("AlloFundID") <> "") then Sp_Del_Req_Record__abrID = Request("AlloFundID")
Dim Sp_Del_Req_Record__logl_del_dt Sp_Del_Req_Record__logl_del_dt = "" if(Now() <> "") then Sp_Del_Req_Record__logl_del_dt = Now()
Dim Sp_Del_Req_Record__phys_del_dt Sp_Del_Req_Record__phys_del_dt = "" if(Now() <> "") then Sp_Del_Req_Record__phys_del_dt = Now()
I get a wrong data type error thrown at the following line: Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@abrID", 3, 1,4,Sp_Del_Req_Record__abrID)
I'm not sure where I am going wrong. Any help is appreciated. Thanks. -D-
I need to create a stored procedure that will update, an insert will try to insert an entire row and I am only trying to update old data with new data. For instance if I move all the 99 terms from the active table to the term table and lets say for example their [hiredate], [ID], [firstname], but after the update is done I realize I forgot to include the [lastname] field, see what I mean??? Or I just wanted to UPDATE old data with new data?? Would this stored procedure work
CREATE PROCEDURE [InsertTerms] AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] (ReasonTerminated)
SELECT a.DESCRIPTION FROM DesireeTerm3 WHERE TERIMINATION.TM#= DesireeTerm3.Employee RETURN GO
In this stored procedure I attempt to update the AGE and SERV_AGE fields with the days difference between a date parameter and a List Date and Service Date in any of a number of tables we have that contain these two fields. I attempt to pass in the date parameter and the table name to use. ===================================== CREATE PROCEDURE up_UpdateAcctAge @strTableName nvarchar(50), @dteWeekDate datetime
AS UPDATE @strTableName SET AGE = DATEDIFF(D, ASSIGN_DT, CONVERT(DATETIME, @dteWeekDate, 102)), SERV_AGE = DATEDIFF(D, SERV_DT, CONVERT(DATETIME,@dteWeekDate, 102)) GO =========================================
Any idea why I am getting the following message when I check syntax?
Server: Msg 137, Level 15, State 2, Procedure up_UpdateAcctAge, Line 7 Must declare the variable '@strTableName'.
I have been given the task to create a stored procedure that will update employee's leave balances. My part of the task was just to create the stored procedure within my sql server 2005 database that the programmer can use each time leave balances change in the current leave system. There are 4 types of leave balances: Annual Leave, Sick Leave, Family Leave, and Other Leave. The problem I am having is that I was told that all 4 values will not be passed each time, so my stored procedure needs to allow empty/Null values to be entered for some fields without overwriting the existing data. Also any errors should not cause issues with the current leave system, I am guessing if an error occurs then I need an email sent to me stating the issue and if successful then no message.
I have written this stored procedure:
CREATE PROCEDURE [dbo].[usp_updateuser]
@Emp_SSN int,
@Annual_Forward decimal(10,2),
@Sick_Forward decimal(10,2),
@Family_Forward decimal(10,2),
@Other_Forward decimal(10,2)
AS
UPDATE OT_MAIN
SET
EmpAnnual_Forward = @Annual_Forward,
EmpSick_Forward = @Sick_Forward,
EmpFamily_Forward = @Family_Forward,
EmpOther_Forward = @Other_Forward
WHERE
Emp_SSN=@Emp_SSN
I can execute the procedure using exec and then passing the 4 variables, but I don't know how to do the errors and messages and the allow null values without over writing.
Any help would be greatly appreciated. I am really new to sql server, and expecially new to stored procedures.
