求一个存储过程???//
tableA
id name adress remark
1 aa 111 11111
2 bb 222 22222
3 cc 333 33333
想在一个存储过程中实现修改和新增
[解决办法]
CREATE PROC(
@id int, -- 自己修改类型
@name varchar(100),
@adress varchar(100),
@remark varchar(500)
)
AS
SET NOCOUNT ON
INSERT INTO tableA(id name adress remark) VALUES(@id @name @adress @remark)
-- UPDATE tableA name=@name, adress=@address, remark = @remark WHERE SET id = @id
SET NOCOUNT OFF
[解决办法]
首先判断ID是否存在，不存在则插入，存在则更新．．．
[解决办法]
CREATE PROC p
@id int=0,
@name varchar(100)= ' ',
@adress varchar(100)= ' ',
@remark varchar(500)= ' '
AS
SET NOCOUNT ON
if not exists(select 1 from tableA where id=@id)
begin
INSERT INTO tableA(id, name, adress, remark) VALUES(@id, @name, @adress, @remark)
end
else
begin
UPDATE tableA set name=@name, adress=@adress, remark = @remark WHERE id = @id
end
SET NOCOUNT OFF
go
[解决办法]
--region Drop Existing Procedures
IF OBJECT_ID(N '[dbo].[pro_InsertUpdateTABLEA] ') IS NOT NULL
DROP PROCEDURE [dbo].[pro_InsertUpdateTABLEA]
--endregion
GO
--region [dbo].[pro_InsertUpdateTABLEA]
------------------------------------------------
-- Generated By: Administrator using CodeSmith 4.0.0.0
-- Template: StoredProcedures.cst
-- Procedure Name: [dbo].[pro_InsertUpdateTABLEA]
-- Date Generated: 2007年7月21日
------------------------------------------------
CREATE PROCEDURE [dbo].[pro_InsertUpdateTABLEA]
@id int,
@name nvarchar(50),
@address nvarchar(50),
@remark nvarchar(50)
AS
SET NOCOUNT ON
IF EXISTS(SELECT [id] FROM [dbo].[TABLEA] WHERE [id] = @id)
BEGIN
UPDATE [dbo].[TABLEA] SET
[name] = @name,
[address] = @address,
[remark] = @remark
WHERE
[id] = @id
END
ELSE
BEGIN
INSERT INTO [dbo].[TABLEA] (
[id],
[name],
[address],
[remark]
) VALUES (
@id,
@name,
@address,
@remark
)
END
--endregion
GO
[解决办法]
CREATE PROC YourProc(
@id int, -- 自己修改类型
@name varchar(100),
@adress varchar(100),
@remark varchar(500)
)
AS
--添加的时候
SET
INSERT INTO tableA(id name adress remark) VALUES(@id @name @adress @remark)
--修改的时候
UPDATE tableA name=@name, adress=@address, remark = @remark WHERE SET id = @id
[解决办法]
我顶一下