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


ADVERTISEMENT

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

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

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

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

Can I Use A SqlDataSource Control Exclusively To Pass Data To A Stored Procedure For Execution (insert/update Only)?

Feb 28, 2008

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!

View 5 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

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.

View 2 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

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 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

View 2 Replies View Related

Stored Procedure - Update Statement Does Not Seem To Update Straight Away

Jul 30, 2007

Hello,

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?

View 6 Replies View Related

Problem Doing Update And Insert To Different Tables In Same Procedure.

Feb 14, 2007

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 */
 
SELECT @PhotoID, @Location
RETURN
Thanks,
 
Jason
 
 

View 3 Replies View Related

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 View Related

Need Help With A SQL Update Stored Procedure

Jun 16, 2006

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  

View 1 Replies View Related

Update To Stored Procedure

Feb 18, 2008

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 ____________
 
<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();
}
}
_______________________________________________________________________________-

View 9 Replies View Related

Update Stored Procedure

May 3, 2008

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???
 

View 1 Replies View Related

Stored Procedure Update..

May 27, 2004

I'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!)

View 4 Replies View Related

Update Stored Procedure HELP

Apr 22, 2005

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

View 4 Replies View Related

IF / ELSE -- Update Stored Procedure

Aug 4, 2005

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

View 1 Replies View Related

Update Stored Procedure Help!!!

Nov 30, 2005

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

View 3 Replies View Related

Update From Stored Procedure

May 16, 2002

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

please help, much appreciation

JIM

View 1 Replies View Related

Update Stored Procedure

Jan 7, 2005

I'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-

View 3 Replies View Related

Update Stored Procedure

Jan 22, 2006

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

View 14 Replies View Related

UPDATE Stored Procedure

May 9, 2007

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'.

Any help you can give this novice is appreciated.

View 4 Replies View Related

Update Stored Procedure Help

Jan 2, 2008

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.

Thank you.

View 3 Replies View Related







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