create proc p_test @Id int, @Name nvarchar(50), @Gender bit, @Address nvarchar(200), @Tel nvarchar(50) AS INSERT Employees VALUES(@Id,@Name,@Gender,@Address,@Tel ) GO
create procedure my_Employees as begin update my_Employees set Address = (case when Name = '张三' then '...' when name = '李四' then '....' when name = '王五' then '...' else ....end) end
CREATE PROCEDURE up_emp @Id int, @Name nvarchar(50), @Gender bit, @Address nvarchar(200), @Tel nvarchar(50), @type int AS begin if @type =1 --insertINSERT Employees VALUES(@Id,@Name,@Gender,@Address,@Tel ) if @type =2 --update update Employees set Name=@Name, Gender=@Gender, Address=@Address, Tel=@Tel where Id=@id if @type =3 --delete delete from Employees where Id=@id if @type =4 --select select * from Employees where Id=@id if @type =5 --.... select * from Employees end
写个够长的,最长也就这么长了: insert into Employees select 1,2,3,4,5 union select 1,2,3,4,5 union .... 省略无穷大行,写到sql语句大小上限或者内存放不下为止 ... union select 1,2,3,4,5这个带走.
@Id int,
@Name nvarchar(50),
@Gender bit,
@Address nvarchar(200),
@Tel nvarchar(50)
AS
INSERT Employees
VALUES(@Id,@Name,@Gender,@Address,@Tel )
GO
begin
update my_Employees set Address = (case when Name = '张三' then '...'
when name = '李四' then '....' when name = '王五' then '...' else ....end)
end
CREATE PROCEDURE up_emp
@Id int,
@Name nvarchar(50),
@Gender bit,
@Address nvarchar(200),
@Tel nvarchar(50),
@type int
AS
begin
if @type =1 --insertINSERT Employees
VALUES(@Id,@Name,@Gender,@Address,@Tel )
if @type =2 --update
update Employees
set Name=@Name,
Gender=@Gender,
Address=@Address,
Tel=@Tel
where Id=@id
if @type =3 --delete
delete from Employees where Id=@id
if @type =4 --select
select * from Employees where Id=@id
if @type =5 --....
select * from Employees
end
insert into Employees
select 1,2,3,4,5
union
select 1,2,3,4,5
union
....
省略无穷大行,写到sql语句大小上限或者内存放不下为止
...
union
select 1,2,3,4,5这个带走.
在里面多加if else ,可以批量搞的,用代码一行行写,不用循环,可以把代码搞的很长很臭,呵呵,这,我真的写不出
begin
select ....
if @dd<>@bb
else
if
else
.................
end