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替換問題求教，求 sql 替換問題，線上等
是資料錯誤了嗎？怎麼會有這樣的替換。如果是一次性的替換，就寫多條語句，一個個來。如果以後還要替換，則可以建立一個表，兩個欄位 原值 新值，把原值設定成6，新值設定成15，其他的一對對的新增資料。然後更新語句寫成。update 表。set strchannel2val 新值。from 表，新建立的表。...
求SQL查詢語句
先將表c列變成行，然後再進行下一步操作 select from select 訊息 單位 a1 標誌 a1 from 表c union all select 訊息 單位 a2 標誌 a2 from 表c union all select 訊息 單位 a2 標誌 a2 from 表c torder b...
求做一道SQL語句題，問一道SQL語句的題目，請各位幫幫忙。
2.update user set password xyw97 where username admin 3.delete from user where usernmae temp 4.insert into user username,password values newuser newpw...