1樓：匿名使用者
1,if exists (select name from sysobjects
where name = 'num_em' and type = 'p')
drop procedure num_em
gocreate 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
gocreate 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
gocreate 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
gocreate 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
gocreate 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
gocreate 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
endclose userid_cursor
deallocate userid_cursor return true
endgo 7,if exists (select name from sysobjects
where name = 'zj' and type = 'p')
drop procedure zj
gocreate 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
endfetch next from userid_cursor into @userid
endclose userid_cursor
deallocate userid_cursor
return trueendgo 以上儲存過程沒具體測試，
2樓：匿名使用者
分在哪呢？我天天都在寫儲存過程
！！！！高分求sql問題答案，懂得大師請幫幫忙
sql題目求答案
3樓：匿名使用者
/*建立moonfox_db資料庫*/
use master
if exists(select * from sysdatabases where name='moonfox_db')
drop database moonfox_db
create database moonfox_dbon(
name='moonfox_db_data',
filename='d:\visual studio 2008 & sql server 2005\sql server\moonfox_db.mdf',
size=10,
filegrowth=2mb
)log on
(name='moonfox_db_log',
filename='d:\visual studio 2008 & sql server 2005\sql server\moonfox_db.ldf',
size=5,
filegrowth=20%
)/*建立department表*/
use moonfox_db
if exists(select * from sysobjects where name='department')
drop table department
create table department
(did int identity (1,1)primary key,--部門編號，主鍵
dname nvarchar(20),--部門名稱
address nvarchar(50),--部門地址
photo decimal(12,0),--**
)/*建立employee表*/
use moonfox_db
if exists(select * from sysobjects where name='employee')
drop table employee
create table employee
(eid int identity (1,1)primary key,--職工編號，主鍵
ename varchar(10),--職工名
gender nchar(2) check(gender='男' or gender='女'),--性別，新增限制
position nvarchar(10) check(position='員工' or position='組長' or position='經理'),--職務，新增限制
address nvarchar(50),--家庭地址
did int,--部門編號，外來鍵
foreign key(did) references department(did)--外來鍵約束
)/*建立care表*/
use moonfox_db
if exists(select * from sysobjects where name='care')
drop table care
create table care
(cid int identity (1,1)primary key,--保健卡編號，主鍵
eid int,--職工號，外來鍵
foreign key(eid) references employee(eid),--外來鍵約束
checkdate datetime,--檢查身體日期
physicalcondition nvarchar(4) check(physicalcondition='一般' or physicalcondition='差' or physicalcondition='好'),--健康狀況
)/*建立care表約束*/
alter table care
addconstraint df_checkdate default(getdate()) for checkdate--預設，預設淨時間為當前計算機時間 路徑自己修改，試圖自己做，選擇語句自己寫。我該睡覺了，抱歉，你試著在sql server中執行下，我等著休息，也不知道寫的有沒有錯誤，沒時間幫你寫省下的了。不急著用的話我明天幫你寫吧。
sql語句的問題？求高人解答
create table t account int,id1 char 1 id2 char 1 insert into t select 1,a union allselect 1,b union allselect 1,c union allselect 2,d union allselect ...
求個SQL指令碼解釋
刪除 sysparam 表中 p name 等於 authercase 的記錄 delete from sysparam where p name authercase go from date 2010 05 01 to date 2010 06 01 37 oteu2venn6280ckc se...
關於sql資料庫的題目求幫忙,關於SQL資料庫的題目,求幫忙
du1 zhiselect from c where ame like 資料dao庫 2 select a.s a.sname from s aleft join sc b on a.s b.s left join c on b.c c.c where c.ame maths 3 select a....