不看会后悔的Windows XP之经验谈	简单快捷DIY实用家庭影院

高分求一个简单存储过程，在线等，立节

楼主huangyan229（）2005-01-18 11:49:07 在 MS-SQL Server / 应用实例提问

非常简单,由于我没写过存储过程，特求教于大家

select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=?
and DATEPART(mm,mt.update_time)=?
?为参数
将上面的选择结果插入到Model_File表中，插入过程中，如果ModelFile表中存在相应纪录则更新，
否则插入新纪录，判断条件是在Model_File中PNC是否已存在

在线等，立节问题点数：100、回复次数：9Top

1 楼daijingjie2002（艰苦创业）回复于 2005-01-18 11:53:51 得分 5

insert into Model_File
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=?
and DATEPART(mm,mt.update_time)=?
and pnc not in(select pnc from Model_File)Top

2 楼huangyan229（）回复于 2005-01-18 11:55:23 得分 0

您这个只有插入没有更新阿Top

3 楼pbsql（风云）回复于 2005-01-18 12:00:51 得分 30

update Model_File
set update_time=t.update_time,mt_code=t.mt_code,subgroup=t.subgroup,supplier code=t.supplier code
from
(
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=?
and DATEPART(mm,mt.update_time)=?
) t
where Model_File.PNC=t.PNC

insert into Model_File(update_time,pnc,mt_code,subgroup,supplier code)
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=?
and DATEPART(mm,mt.update_time)=?
and not exists(select * from Model_File where Model_File.PNC=mt.PNC)
Top

4 楼huangyan229（）回复于 2005-01-18 12:22:27 得分 0

忘了写,希望又返回值,成功为1失败为0Top

5 楼xluzhong（Ralph）回复于 2005-01-18 12:37:27 得分 10

create procedure test
@test1 nvarchar(100),
@test2 nvarchar(100)
as
if exists(select * from ModelFile where 条件 )
begin
update ModelFile
set update_time=mt.update_time,
pnc=mt.pnc,
mt_code=mt.mt_code,
subgroup=i.subgroup,
[supplier code]=i.[supplier code]
from ModelFile
LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@test1
and DATEPART(mm,mt.update_time)=@test2

end
else
begin
insert into ModelFile(update_time,pnc,mt_code,subgroup,[supplier code])
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@test1
and DATEPART(mm,mt.update_time)=@test2
end
goTop

6 楼camel_yang（骆驼）回复于 2005-01-18 12:37:39 得分 30

--参考pbsql(风云)的,写成存储过程,加上判断,加上年/月参数

CREATE PROCEDURE proc_name
@intYear int = 2005,
@intMonth int=1
AS

if not Exist(select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@intYear
and DATEPART(mm,mt.update_time)=@intMonth )
begin
select '没有满足条件的记录'
return 0
end
Else

update Model_File
set update_time=t.update_time,mt_code=t.mt_code,subgroup=t.subgroup,supplier code=t.supplier code
from
(
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@intYear
and DATEPART(mm,mt.update_time)=@intMonth
) t
where Model_File.PNC=t.PNC

if @@ErrorCode<>0
begin
select '不能更新model_File', @ErrorCode
return 0
end

insert into Model_File(update_time,pnc,mt_code,subgroup,supplier code)
select distinct mt.update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@intYear
and DATEPART(mm,mt.update_time)=@intMonth
and not exists(select * from Model_File where Model_File.PNC=mt.PNC)

if @@ErrorCode<>0
begin
select '不能插入model_File', @ErrorCode
return 0
endTop

7 楼gsh945（太平洋底）回复于 2005-01-18 12:58:17 得分 5

治标不治本：：看下面的
语法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

例子：
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO
Top

8 楼Littleming（小明）回复于 2005-01-18 13:22:56 得分 20

-- 上面的做法效率不太高，只能分两步
-- 1.插入：你要先mt对照Model_File，再插入在Model_File没有的记录
-- 2.更新：满足条件就更新
declare proc pro_insert_or_update_records
(@year int,@month int)
as
begin
-- insert
insert into Model_File
select distinct mt.update_time update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from (select * from mt where PNC not in(select PNC from Model_File)) mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@year
and DATEPART(mm,mt.update_time)=@year
-- update
update Model_File
set update_time=aaa.update_time,mt_code=aaa.mt_code,subgroup=aaa.subgroup,
[supplier code]=aaa.[supplier code] from
(select distinct mt.update_time update_time,mt.pnc,mt.mt_code,i.subgroup,i.[supplier code]
from (select * from mt where PNC in(select PNC from Model_File)) mt LEFT JOIN iniqessubg as i ON i.pnc=mt.pnc where
DATEPART(yy,mt.update_time)=@year
and DATEPART(mm,mt.update_time)=@year) aaa
where Model_File.PNC=aaa.PNC
end
Top

9 楼huangyan229（）回复于 2005-01-18 14:48:43 得分 0

看来只能分两步走了Top