求SQL问题答案

求SQL问题答案

1,IF EXISTS (SELECt name FROM sysobjects
WHERe name = 'num_em' AND type = 'P')
DROp PROCEDURE num_em
GO
CREATE PROCEDURE num_em
AS
-- 员工号 userid
SELECT count(userid) userid FROM employeesGO
2,IF EXISTS (SELECt name FROM sysobjects
WHERe name = 'is_em' AND type = 'P')
DROp PROCEDURE is_em
GO
CREATE PROCEDURE is_em@userid varchar(20),@zt bit out
ASbegin
-- 员工号 userid
if EXISTS (SELECT userid FROM employees where userid=@userid) begin return false end else begin delete from salary where userid=@userid if @@error <>0 return true endselect * FROM employees where userid='00001'endGO 3,
IF EXISTS (SELECt name FROM sysobjects
WHERe name = 'is_yfb' AND type = 'P')
DROp PROCEDURE is_yfb
GO
CREATE PROCEDURE is_yfb@userid varchar(20),@xueli varchar(20) out
ASbegin declare @xuli1 varchar(20)
--部门 cdept 员工 userid
if EXISTS (SELECT @xuli1=xueli FROM employees where cdept='研发部' and userid=@userid) begin return @xuli1 end else begin return 'no' endselect * FROM employees where userid in ('000001','302566')endGO 4, IF EXISTS (SELECt name FROM sysobjects
WHERe name = 'abc' AND type = 'P')
DROp PROCEDURE abc
GO
CREATE PROCEDURE abc@userid varchar(20),@zt bit out
ASbegin
--入职日期为djoindate 员工号位userid
if EXISTS (SELECT userid FROM employees where
(DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=4
and userid=@userid) begin
--工资 gz
update employees set gz=gz+500 where userid=@userid
return true end else begin return false end
endGO5,
IF EXISTS (SELECT name FROM sysobjects
WHERe name = 'zj' AND type = 'P')
DROp PROCEDURE zj
GO
CREATE PROCEDURE zj@userid varchar(20)
ASbegin
--入职日期为djoindate 员工号位userid
if EXISTS (SELECT userid FROM employees where
(DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=6
and userid=@userid) begin
--部门 cdept
update employees set cdept='经理办公室' where userid=@userid end
endGO
6,IF EXISTS (SELECT name FROM sysobjects
WHERe name = 'zengjia1' AND type = 'P')
DROp PROCEDURE zengjia1
GO
CREATE PROCEDURE zengjia1@zt bit out
ASbegin
declare @userid varchar(20)
DECLARE userid_cursor CURSOR FOR
SELECT userid
FROM employees
where (DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=4
OPEN userid_cursor
FETCH NEXT FROM userid_cursor INTO @userid IF @@FETCH_STATUS <> 0
PRINT '无4年以上员工 ' WHILE @@FETCH_STATUS = 0
BEGIN
--工资 gz 员工号=userid
update employees set gz=gz+500 where userid=@userid
if @@error <>0
begin
return false
end FETCH NEXT FROM userid_cursor INTO @userid

END

CLOSE userid_cursor
DEALLOCATE userid_cursor return true
endGO 7,IF EXISTS (SELECt name FROM sysobjects
WHERe name = 'zj' AND type = 'P')
DROp PROCEDURE zj
GO
CREATE PROCEDURE zj@zt bit out
ASbegin
declare @userid varchar(20)
DECLARE userid_cursor CURSOR FOR
SELECT userid
FROM employees
where (DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=6
OPEN userid_cursor
FETCH NEXT FROM userid_cursor INTO @userid IF @@FETCH_STATUS <> 0
PRINT '无6年以上员工 ' WHILE @@FETCH_STATUS = 0
BEGIN
--部门 cdept
update employees set cdept='经理办公室' where userid=@userid
if @@error <>0
begin
return false
end
FETCH NEXT FROM userid_cursor INTO @userid

END

CLOSE userid_cursor
DEALLOCATE userid_cursor
return trueendGO 以上存储过程没具体测试，

分在哪呢？我天天都在写存储过程