Insert/update In One Stored Procedure
Jul 30, 2007Hi:
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?
ADVERTISEMENT
Insert Or Update With Stored Procedure
Dec 27, 2006I'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 RelatedUpdate/Insert Stored Procedure
Nov 16, 2004I 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!
Stored Procedure - INSERT/UPDATE
Aug 27, 2007HI,
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'.
Calling Update And Insert In One Stored Procedure
Jan 25, 2006Hello,
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.
Stored Procedure For Insert / Update And Delete
Nov 26, 2013How 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]...
Stored Procedure - INSERT INTO Or UPDATE - INNER JOIN TWO TABLES
Jun 13, 2008Hi 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 RelatedInsert, Update Issue - Stored Procedure Workaround
Apr 30, 2006any 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
Transact SQL :: Stored Procedure To Update And Insert In Single SP
Jul 17, 2015I 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] .....
Transact SQL :: Insert Or Update Stored Procedure Return ID
Nov 1, 2015I 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] ....
SQL Server Insert Update Stored Procedure - Does Not Work The Same Way From Code Behind
Mar 13, 2007All:
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.
General Stored Procedure, For Insert, Update, Select, Delete?
May 7, 2007Hi 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
SQL Server 2012 :: Stored Procedure To Update And Insert In Single SP
Jul 17, 2015I 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] ....
Instead Of Insert, Update Trigger Calling A Stored Procedure Question
Oct 26, 2006I 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
May 27, 2007I 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
Can I Use A SqlDataSource Control Exclusively To Pass Data To A Stored Procedure For Execution (insert/update Only)?
Feb 28, 2008Hi,
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!
T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2
Apr 30, 2015table2 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.
Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update
May 27, 2008hi 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 update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Jul 30, 2007Hello,
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?
Problem Doing Update And Insert To Different Tables In Same Procedure.
Feb 14, 2007We 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 */
SELECT @PhotoID, @Location
RETURN
Thanks,
Jason
Insert/Update Statements Or Stored Procs
Mar 11, 2004When 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
Need Help With A SQL Update Stored Procedure
Jun 16, 2006Can 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
Update To Stored Procedure
Feb 18, 2008hi
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 ____________
<asp:GridView ID="grdD" runat="server" AutoGenerateColumns="False" DataKeyNames="CountryCode" DataSourceID="dsGrdD" OnRowDataBound="grdD_RowDataBound"><Columns><asp:TemplateField><ItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Bind("Country") %>' />
<asp:DropDownList ID="ddlCities" runat="server" />
</ItemTemplate></asp:TemplateField></Columns></asp:GridView>
<asp:SqlDataSource ID="dsGrdD" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
SelectCommand="Select_Cities" SelectCommandType="StoredProcedure" UpdateCommand="'Update_ActiveCity' " UpdateCommandType="StoredProcedure" CacheExpirationPolicy="Sliding">
<SelectParameters> <asp:SessionParameter Name="ListCode" SessionField="ListCode" Type="String" /> </SelectParameters>
</asp:SqlDataSource>
_______________________ this is the code behind ____________________protected void grdD_RowDataBound(object sender, GridViewRowEventArgs e)
{DropDownList ddl = e.Row.FindControl("ddlCities") as DropDownList;if (ddl != null)
{
string s = DataBinder.Eval(((GridViewRow)e.Row).DataItem, "Cities").ToString();ddl.DataSource = s.Split(',');
ddl.DataBind();
}
}
_______________________________________________________________________________-
Update Stored Procedure
May 3, 2008here 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???
Stored Procedure Update..
May 27, 2004I'm sorry for asking a lot of questions, but it is driving me crazy that I can't figure out how to do this update..
Let say I got two tables, both with an "ID".
TBL 1 TBL 2
ID <--> ID
STATUS
If they match, I want to update the status on tbl1 as "matched"
How would I preform this with a stored procedure??
I am currently using a view and then updating the view (I KNOW ITS BAD!)
Update Stored Procedure HELP
Apr 22, 2005I 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
IF / ELSE -- Update Stored Procedure
Aug 4, 2005What 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
View 1 Replies View RelatedUpdate Stored Procedure Help!!!
Nov 30, 2005Trying 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
Update From Stored Procedure
May 16, 2002Is 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
please help, much appreciation
JIM
Update Stored Procedure
Jan 7, 2005I'm updating a record using the following SP:
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()
%>
<%
set Sp_Del_Req_Record = Server.CreateObject("ADODB.Command")
Sp_Del_Req_Record.ActiveConnection = MM_DBConn_STRING
Sp_Del_Req_Record.CommandText = "dbo.Sp_Del_Req_Record"
Sp_Del_Req_Record.CommandType = 4
Sp_Del_Req_Record.CommandTimeout = 0
Sp_Del_Req_Record.Prepared = true
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@RETURN_VALUE", 3, 4)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@abrID", 3, 1,4,Sp_Del_Req_Record__abrID)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@logl_del_dt", 135, 1,8,Sp_Del_Req_Record__logl_del_dt)
Sp_Del_Req_Record.Parameters.Append Sp_Del_Req_Record.CreateParameter("@phys_del_dt", 135, 1,8,Sp_Del_Req_Record__phys_del_dt)
Sp_Del_Req_Record.Execute()
%>
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-
Update Stored Procedure
Jan 22, 2006I 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
UPDATE Stored Procedure
May 9, 2007In 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'.
Any help you can give this novice is appreciated.
Update Stored Procedure Help
Jan 2, 2008I 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.
Thank you.