这是我的插入存储过程。如何为更新编写相同内容。 [英] this is my stored procedure for insert. how to write the same for update.
本文介绍了这是我的插入存储过程。如何为更新编写相同内容。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
ALTER PROCEDURE [dbo]。[Resource_sp]
@ FirstName varchar ( 50 ) ,
@ LastName varchar ( 50 ),
@ PrimarySkill varchar ( 50 ),
@ SecondarySkill varchar ( 50 ),
@ Email varchar ( 50 ),
@ Phone int = 0,
@ResourceManager varchar ( 50 ),
@ Customer varchar ( 50 ),
@ StartDate DateTime ,
@ CompanyName varchar ( 50 ),
@ ContactLastName varchar ( 50 ),
@ContactFirstName varchar ( 50 ),
@Duns varchar ( 50 ),
@ PayRate varchar ( 50 ),
< span class =code-sdkkeyword> @ citizen varchar ( 50 ),
@ GC varchar ( 50 ),
@ EndDate DateTime ,
@ CustomerManager varchar ( 50 ),
@ CreatedAt varchar ( 50 ),
@ CanadianCitizen varchar ( 50 ),
@ ReferenceName1 varchar ( 50 ),
@ ReferenceName2 varchar ( 50 ),
@ Email1 varchar ( 50 ),
@ Email2 varchar ( 50 )
AS
BEGIN
插入 进入资源(FirstName,LastName,PrimarySkill,SecondarySkill,Email,Phone,ResourceManager,Customer,StartDate,CompanyName,ContactLastName,ContactFirstName,Duns,PayRate,citizen,GC,CanadianCitizen,EndDate,CustomerManager,CreatedAt,ReferenceName1,ReferenceName2 ,Email1,Email2)值( @ FirstName , @ LastName , @ PrimarySkill , @ SecondarySkill , @电子邮件, @ Phone , @ ResourceManag呃, @ Customer , @ StartDate , @CompanyName , @ ContactLastName , @ ContactFirstName , @Dunns , @ PayRate , @ citizen , @GC , @ CanadianCitizen , @ EndDate , @ CustomerManager , @ CreatedAt , @ ReferenceName1 , @ ReferenceName2 , @ Email1 , @ Email2 )
SELECT * 来自资源
END 跨度>
<二v class =h2_lin>解决方案
创建程序[dbo]。[Resource_Update_sp]
@ID int,//用于更新特定记录的ID
@FirstName varchar(50),
@ LastName varchar(50),
@PrimarySkill varchar(50),
@SecondarySkill varchar(50),
@Email varchar(50 ),
@Phone int = 0,
@ResourceManager varchar(50),
@Customer varchar(50),
@StartDate DateTime,
@CompanyName varchar( 50),
@ContactLastName varchar(50),
@ContactFirstName varchar(50),
@Duns varchar(50),
@PayRate varchar(50),
@citizen varchar(50),
@GC varchar(50),
@EndDate DateTime,
@CustomerManager varchar(50),
@CreatedAt varchar(50),
@CanadianCitizen varchar(50),
@ ReferenceName1 varchar(50),
@ ReferenceName2 varchar(50),
@ Email1 varchar(50),
@ Email2 varchar (50)
AS
BEGIN
更新资源设置
FirstName = @ FirstName,
LastName = @ LastName,
PrimarySkill = @ PrimarySkill ,
Secon darySkill = @ SecondarySkill,
Email = @Email,
Phone = @ Phone,
ResourceManager = @ ResourceManager,
Customer = @ Customer,
StartDate = @ StartDate,
CompanyName = @ CompanyName,
ContactLastName = @ ContactLastName,
ContactFirstName = @ ContactFirstName,
Duns = @Duns,
PayRate = @ PayRate,
citizen = @ citizen,
GC = @ GC,
CanadianCitizen = @ CanadianCitizen,
EndDate = @ EndDate,
CustomerManager = @ CustomerManager,
CreatedAt = @ CreatedAt,
ReferenceName1 = @ ReferenceName1,
ReferenceName2 = @ ReferenceName2,
Email1 = @ Email1,
Email2 = @ Email2其中ID = @ ID
SELECT * from资源
END
您需要在运行更新之前为您的表识别主键集 - 它可能是firstname,姓氏组合甚至是新的唯一主键身份。
您可以根据此键轻松更新。
例如更新primaryskill = @ primaryskill,secondaryskill = @ secondaryskill ...
其中firstname = @firstname和lastname = @ lastname
少数事情和步骤:
1.发布插入后,必须有一些主键能够唯一地代表插入的记录。
2.对于更新,此KEY将用于查找记录和更新。
3.与上述相比,不同之处在于:
a。添加额外的唯一ID参数
b。将INSERT查询更改为更新
因此,它将类似于:UPDATE
资源
SET
FirstName = ' Sandeep',
LastName = ' Mewara'
WHERE
UniqueIdColumnName = @ UniqueIDAsPrimaryKey
试试吧!看看你是否遇到任何问题。
ALTER PROCEDURE [dbo].[Resource_sp]
@FirstName varchar(50),
@LastName varchar(50),
@PrimarySkill varchar(50),
@SecondarySkill varchar(50),
@Email varchar(50),
@Phone int=0,
@ResourceManager varchar(50),
@Customer varchar(50),
@StartDate DateTime,
@CompanyName varchar(50),
@ContactLastName varchar(50),
@ContactFirstName varchar(50),
@Duns varchar(50),
@PayRate varchar(50),
@citizen varchar(50),
@GC varchar(50),
@EndDate DateTime,
@CustomerManager varchar(50),
@CreatedAt varchar(50),
@CanadianCitizen varchar(50),
@ReferenceName1 varchar(50),
@ReferenceName2 varchar(50),
@Email1 varchar(50),
@Email2 varchar(50)
AS
BEGIN
Insert into Resource(FirstName,LastName,PrimarySkill,SecondarySkill,Email,Phone,ResourceManager,Customer,StartDate,CompanyName,ContactLastName,ContactFirstName,Duns,PayRate,citizen,GC,CanadianCitizen,EndDate,CustomerManager,CreatedAt,ReferenceName1,ReferenceName2,Email1,Email2) values (@FirstName,@LastName,@PrimarySkill,@SecondarySkill,@Email,@Phone,@ResourceManager,@Customer,@StartDate,@CompanyName,@ContactLastName,@ContactFirstName,@Duns,@PayRate,@citizen,@GC,@CanadianCitizen,@EndDate,@CustomerManager,@CreatedAt,@ReferenceName1,@ReferenceName2,@Email1,@Email2)
SELECT * from Resource
END
解决方案
create procedure [dbo].[Resource_Update_sp] @ID int, //ID for updating a specific records @FirstName varchar(50), @LastName varchar(50), @PrimarySkill varchar(50), @SecondarySkill varchar(50), @Email varchar(50), @Phone int=0, @ResourceManager varchar(50), @Customer varchar(50), @StartDate DateTime, @CompanyName varchar(50), @ContactLastName varchar(50), @ContactFirstName varchar(50), @Duns varchar(50), @PayRate varchar(50), @citizen varchar(50), @GC varchar(50), @EndDate DateTime, @CustomerManager varchar(50), @CreatedAt varchar(50), @CanadianCitizen varchar(50), @ReferenceName1 varchar(50), @ReferenceName2 varchar(50), @Email1 varchar(50), @Email2 varchar(50) AS BEGIN Update Resoure set FirstName=@FirstName, LastName=@LastName , PrimarySkill=@PrimarySkill , SecondarySkill=@SecondarySkill, Email=@Email, Phone=@Phone, ResourceManager=@ResourceManager, Customer=@Customer, StartDate=@StartDate, CompanyName=@CompanyName, ContactLastName=@ContactLastName, ContactFirstName=@ContactFirstName, Duns=@Duns, PayRate=@PayRate, citizen=@citizen, GC=@GC, CanadianCitizen=@CanadianCitizen, EndDate=@EndDate, CustomerManager=@CustomerManager, CreatedAt=@CreatedAt, ReferenceName1=@ReferenceName1, ReferenceName2=@ReferenceName2, Email1=@Email1, Email2=@Email2 where ID=@ID SELECT * from Resource END
You need to identify a primary key set for your table before you run an update - it could be firstname, lastname combination or even a new unique primary key identity.
You can then easily update based on this key.
E.g.Update primaryskill=@primaryskill, secondaryskill=@secondaryskill... where firstname = @firstname and lastname=@lastname
Few things and Steps:
1. Post insert, there must be some primary key that would represent the inserted record uniquely.
2. For an Update, this KEY would be the one used to find the record and update.
3. Comparing to above, the difference would be:
a. add additional unique id parameter
b. change INSERT query to an UPDATE
Thus, it would be something like:UPDATE Resources SET FirstName = 'Sandeep', LastName = 'Mewara' WHERE UniqueIdColumnName = @UniqueIDAsPrimaryKey
Try out! See if you face any issues.
这篇关于这是我的插入存储过程。如何为更新编写相同内容。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文