求一sql，表更新

[phoebuswei]

member表
cginfo表
member。asp页面
进入页面填入信息点击修改
更新member表，根据填入信息
查看cginfo表有记录就更新没有插入（相应字段）
——————
member更新sql

update member set 。。。。。where code=@code

cginfo更新

update cginfo set 。。。。。where code=@code

cginfo插入

insert into cginfo。。。。。。

想把两个update合成一个，判断也想加入到sql里面
————我这样写了但不行，报错“，”有语法错我

CREATE PROCEDURE _Update
@code nvarchar(50)
as
begin
DECLARE @TempID int
 select @TempID = count(1) from cginfo where code=@code
 IF @TempID = 0
insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
update member set (......)where code=@code
 ELSE
update cginfo set (......)where code=@code
update member set (......)where code=@code
 end

我不知道可不可以这样写

[wzy_love_sly]

if 
begin
--语句1
--语句2
end
else
begin
--语句3
--语句4
end

[phoebuswei]

[Quote=引用 1 楼 wzy_love_sly 的回复:]
SQL codeif
begin
--语句1
--语句2
end
else
begin
--语句3
--语句4
end
[/Quote]

那if还用begin吗

begin
DECLARE @TempID int
 select @TempID = count(1) from cginfo where code=@code
 IF @TempID = 0
begin
insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
update member set (......)where code=@code
 end
 ELSE
begin
update cginfo set (......)where code=@code
update member set (......)where code=@code
 end
 end

[htl258_Tony]

按1楼的格式可行.

[qqhmitzk]

CREATE PROCEDURE _Update
@code nvarchar(50)
as
begin
DECLARE @TempID int
select @TempID = count(1) from cginfo where code=@code
IF @TempID = 0
begin
insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
update member set (......)where code=@code
end
ELSE
begin
update cginfo set (......)where code=@code
update member set (......)where code=@code
end
end

[mycool2006]

不管有没有全部先删除然后插入？呵呵

[等不到来世]

IF @TempID = 0
update cginfo 
set f1=member.f1,f2=member.f2
from member 
where cginfo.code=@code and member.code=@code

update member set (......)where code=@code
 ELSE
...

[htl258_Tony]

[Quote=引用 2 楼 phoebuswei 的回复:]
引用 1 楼 wzy_love_sly 的回复:
SQL codeif
begin
--语句1
--语句2
end
else
begin
--语句3
--语句4
end


那if还用begin吗

SQL code
begin
DECLARE @TempID int
select @TempID = count(1) from cginfo where code=@code
IF @TempID = 0
begin
insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
update member set (......)w…
[/Quote]

CREATE PROCEDURE _Update
@code nvarchar(50)
as
 DECLARE @TempID int
 select @TempID = count(1) from cginfo where code=@code
 IF @TempID = 0
 begin
     insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
     update member set (......)where code=@code
 end
 ELSE
 begin
     update cginfo set (......)where code=@code
     update member set (......)where code=@code
 end

[dawugui]

CREATE PROCEDURE _Update
@code nvarchar(50)
as
begin
 DECLARE @TempID int
 select @TempID = count(1) from cginfo where code=@code
 IF @TempID = 0
    begin
      insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
      update member set (......)where code=@code
    end
 ELSE
    begin
      update cginfo set (......)where code=@code
      update member set (......)where code=@code
    end
end

[phoebuswei]

update member 例外里都是要执行的，怎么把它提取出来那,这样可以吗

CREATE PROCEDURE _Update
@code nvarchar(50)
as
begin
 DECLARE @TempID int
 select @TempID = count(1) from cginfo where code=@code
 update member set (......)where code=@code
 IF @TempID = 0
    begin
      insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code
    end
 ELSE
    begin
      update cginfo set (......)where code=@code
    end
end

[等不到来世]

[Quote=引用 9 楼 phoebuswei 的回复:]
update member 例外里都是要执行的，怎么把它提取出来那,这样可以吗

SQL code
CREATE PROCEDURE _Update
@code nvarchar(50)
as
begin
DECLARE @TempID int
select @TempID = count(1) from cginfo where code=@code
update member set (......)where code=@code
IF @TempID = 0
begin
insert into cginfo (member.a,member.b,member.c)values(select a,b,c from member code=@cod)where code=@code…
[/Quote]
思路可以，但insert语法错误。参见6楼。

[phoebuswei]

您的意思是说》》》
我要使用条件更新时要使用update form
另外“思路可以”就是说您也不知道ok不
如果要这样写，是不是要另外begin end啊

[等不到来世]

1.更新记录必须使用update,不能使用insert。（当然你也可以先delete,再insert）
2.就你目前的程序，完全可以把update member提出来。
3.分支里的语句只有一句时，不需要begin-end。超过一句必须使用begin-end。

[phoebuswei]

[Quote=引用 12 楼 szx1999 的回复:]
1.更新记录必须使用update,不能使用insert。（当然你也可以先delete,再insert）
2.就你目前的程序，完全可以把update member提出来。
3.分支里的语句只有一句时，不需要begin-end。超过一句必须使用begin-end。
[/Quote]
我整个写完您帮着长长眼

CREATE PROCEDURE UpdateCginfoMember
@m_company nvarchar(50),
@m_shortcompany nvarchar(50),
@m_bankname nvarchar(50),
@m_bank nvarchar(50),
@m_bankcode nvarchar(50),
@m_lxr nvarchar(50),
@m_address nvarchar(50),
@m_postcode nvarchar(50),
@m_email nvarchar(50),
@m_tel nvarchar(50),
@m_combined nvarchar(50),
@m_content nvarchar(50),
@code  nvarchar(50),
@city  nvarchar(50)
as
begin
 DECLARE @TempID int
 --Update member 
 update member set 
m_company = @m_company,
m_shortcompany = @m_shortcompany,
m_bankname = @m_bankname,
m_bank = @m_bank,
m_bankcode = @m_bankcode,
m_lxr = @m_lxr,
m_address = @m_address,
m_postcode = @m_postcode,
m_email = @m_email,
m_tel = @m_tel,
m_combined = @m_combined,
m_content = @m_content
where m_code=@code
--Qyery cginfo state
 select @TempID = count(1) from cginfo where code=@code
 IF @TempID = 0
    --begin
      insert into cginfo (
      m_company,
      m_shortcompany,
      m_code,
      c_city
      )values(
      @m_company,
      @m_shortcompany,
      @code,
      @city
      )
    --end
 ELSE
    --begin
      update cginfo set (
      m_company=@m_company,
      m_shortcompany=@m_shortcompany
      )where code=@code
    --end
end

[phoebuswei]

结束