Sql代码 收藏代码
1. USE [fau]
2. GO
3. /****** 对象: StoredProcedure [dbo].[sp_Check_Emp_Holiday] 脚本日期: 06/09/2010 13:34:40 ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8.
9. CREATE PROCEDURE [dbo].[sp_Check_Emp_Holiday]
10. as
11. declare @E_ID int,
12. @E_TYPE nvarchar(50),
13. @E_HIRE_DATE datetime,
14. @E_SOCIAL_WORK_DATE datetime
15. declare @Holiday numeric(4, 2),
16. @Days int
17. --定义一个游标并打开它
18. begin
19. declare mycursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
20. open mycursor
21. --开始提取记录，放入指定的变量
22. fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
23. while @@fetch_status = 0
24. begin
25. set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
26. set @Holiday=0
27. /*员工类型为Loacl*/
28. if (@E_TYPE=N'Local')
29. begin
30. /*1.员工加入佛吉亚工作未满一年时，年假的计算按月份比例计算（每月为1.25天）, 15日前入职（含15日）不计算当月年假，15日后入职员工当月年假计算为1.25天。*/
31.
32. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
33. begin
34. set @Days=DATEPART(day, @E_HIRE_DATE)
35. if (@Days<=15)
36. set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
37. else
38. set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
39. end
40. /* 员工加入佛吉亚后的第二年至第五年*/
41. /*员工加入佛吉亚后的第十一年至第二十年*/
42. --else if (CONVERT(int,@E_HIRE_DATE,0)/365.0>10 and CONVERT(int,@E_HIRE_DATE,0)/365.0<=20)
43. else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
44. set @Holiday=15
45. /* 员工加入佛吉亚后的第六年至第十年*/
46. else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
47. set @Holiday=17
48. /*员工加入佛吉亚后的第十一年至第二十年*/
49. else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
50. set @Holiday=20
51. else
52. set @Holiday=20
53. end
54. /*当员工类型为Expats*/
55. else if (@E_TYPE=N'Expats')
56. begin
57. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
58. begin
59. set @Days=DATEPART(day, @E_HIRE_DATE)
60. if (@Days<=15)
61. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
62. else
63. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
64. end
65. else
66. begin
67. set @Holiday=24
68. end
69. end
70. /*当员工类型为Secondee*/
71. else if (@E_TYPE='Secondee')
72. begin
73. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
74. begin
75. set @Days=DATEPART(day, @E_HIRE_DATE)
76. if (@Days<=15)
77. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
78. else
79. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
80. end
81. else
82. begin
83. set @Holiday=24
84. end
85. end
86. /*当员工类型为VIE*/
87. else if (@E_TYPE=N'VIE')
88. begin
89. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
90. begin
91. set @Days=DATEPART(day, @E_HIRE_DATE)
92. if (@Days<=15)
93. set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
94. else
95. set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
96. end
97. else
98. begin
99. set @Holiday=30
100. end
101. end
102. /*当员工类型为spcial*/
103. else if (@E_TYPE=N'special')
104. begin
105. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
106. begin
107. set @Days=DATEPART(day, @E_HIRE_DATE)
108. if (@Days<=15)
109. set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
110. else
111. set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
112. end
113. else
114. begin
115. set @Holiday=20
116. end
117. end
118. /*当员工类型为spcial2*/
119. else if (@E_TYPE=N'special2')
120. begin
121. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
122. begin
123. set @Days=DATEPART(day, @E_HIRE_DATE)
124. if (@Days<=15)
125. set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
126. else
127. set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
128. end
129. else
130. begin
131. set @Holiday=24
132. end
133. end
134. /*当员工类型为spcial3*/
135. else if (@E_TYPE=N'special3')
136. begin
137. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
138. begin
139. set @Days=DATEPART(day, @E_HIRE_DATE)
140. if (@Days<=15)
141. set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
142. else
143. set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
144. end
145. else
146. begin
147. set @Holiday=26
148. end
149. end
150. --update dbo.EMPLOYEE_HOLIDAY set H_CREATE_NUM=@Holiday where H_STATUS='Y' and E_ID=@E_ID
151. exec dbo.SP_MODIFY_HOLIDAY @E_ID,@Holiday
152. fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
153. end
154. --关闭游标，释放内存
155. close mycursor
156. deallocate mycursor
157. end
158.
159.
160.
161. USE [fau]
162. GO
163. /****** 对象: StoredProcedure [dbo].[SP_EMP_HOLIDAY] 脚本日期: 06/09/2010 13:35:15 ******/
164. SET ANSI_NULLS ON
165. GO
166. SET QUOTED_IDENTIFIER ON
167. GO
168.
169. CREATE PROCEDURE [dbo].[SP_EMP_HOLIDAY]
170. as
171.
172. declare @E_ID int,
173. @E_TYPE nvarchar(50),
174. @E_HIRE_DATE datetime,
175. @E_SOCIAL_WORK_DATE datetime
176. declare @Holiday numeric(4, 2),
177. @Days int
178. --定义一个游标并打开它
179. declare tablecursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
180.
181. begin
182. open tablecursor
183. --开始提取记录，放入指定的变量
184. fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
185. while @@fetch_status = 0
186. begin
187. update dbo.EMPLOYEE_HOLIDAY
188. set H_STATUS='N'
189. where E_ID=@E_ID
190. set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
191. set @Holiday=0
192. /*员工类型为Loacl*/
193. if (@E_TYPE=N'Local')
194. begin
195. /*1.员工加入佛吉亚工作未满一年时，年假的计算按月份比例计算（每月为1.25天）, 15日前入职（含15日）不计算当月年假，15日后入职员工当月年假计算为1.25天。*/
196.
197. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
198. begin
199. set @Days=DATEPART(day, @E_HIRE_DATE)
200. if (@Days<=15)
201. set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
202. else
203. set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
204. end
205. /* 员工加入佛吉亚后的第二年至第五年*/
206. else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
207. set @Holiday=15
208. /* 员工加入佛吉亚后的第六年至第十年*/
209. else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
210. set @Holiday=17
211. /*员工加入佛吉亚后的第十一年至第二十年*/
212. else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
213. set @Holiday=20
214. else
215. set @Holiday=20
216. end
217. /*当员工类型为Expats*/
218. else if (@E_TYPE=N'Expats')
219. begin
220. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
221. begin
222. set @Days=DATEPART(day, @E_HIRE_DATE)
223. if (@Days<=15)
224. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
225. else
226. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
227. end
228. else
229. begin
230. set @Holiday=24
231. end
232. end
233. /*当员工类型为Secondee*/
234. else if (@E_TYPE='Secondee')
235. begin
236. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
237. begin
238. set @Days=DATEPART(day, @E_HIRE_DATE)
239. if (@Days<=15)
240. set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
241. else
242. set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
243. end
244. else
245. begin
246. set @Holiday=24
247. end
248. end
249. /*当员工类型为VIE*/
250. else if (@E_TYPE=N'VIE')
251. begin
252. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
253. begin
254. set @Days=DATEPART(day, @E_HIRE_DATE)
255. if (@Days<=15)
256. set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
257. else
258. set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
259. end
260. else
261. begin
262. set @Holiday=30
263. end
264. end
265. /*当员工类型为spcial*/
266. else if (@E_TYPE=N'special')
267. begin
268. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
269. begin
270. set @Days=DATEPART(day, @E_HIRE_DATE)
271. if (@Days<=15)
272. set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
273. else
274. set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
275. end
276. else
277. begin
278. set @Holiday=20
279. end
280. end
281. /*当员工类型为spcial2*/
282. else if (@E_TYPE=N'special2')
283. begin
284. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
285. begin
286. set @Days=DATEPART(day, @E_HIRE_DATE)
287. if (@Days<=15)
288. set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
289. else
290. set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
291. end
292. else
293. begin
294. set @Holiday=24
295. end
296. end
297. /*当员工类型为spcial3*/
298. else if (@E_TYPE=N'special3')
299. begin
300. if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
301. begin
302. set @Days=DATEPART(day, @E_HIRE_DATE)
303. if (@Days<=15)
304. set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
305. else
306. set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
307. end
308. else
309. begin
310. set @Holiday=26
311. end
312. end
313. -- print(@Holiday)
314. insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
315. (@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
316. fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
317. end
318. --关闭游标，释放内存
319. close tablecursor
320. deallocate tablecursor
321. end
322.
323. USE [fau]
324. GO
325. /****** 对象: StoredProcedure [dbo].[SP_MODIFY_HOLIDAY] 脚本日期: 06/09/2010 13:35:49 ******/
326. SET ANSI_NULLS ON
327. GO
328. SET QUOTED_IDENTIFIER ON
329. GO
330.
331. CREATE PROCEDURE [dbo].[SP_MODIFY_HOLIDAY]
332. (
333. @E_ID int,
334. @Holiday numeric(4, 2)
335. )
336. AS
337. declare @H_CREATE_NUM numeric(4, 2)
338. declare mycursor1 cursor for select H_CREATE_NUM from dbo.EMPLOYEE_HOLIDAY where E_ID=@E_ID and H_STATUS='Y'
339. BEGIN
340. open mycursor1
341. --开始提取记录，放入指定的变量
342. fetch next from mycursor1 into @H_CREATE_NUM
343. while @@fetch_status = 0
344. begin
345. if(@H_CREATE_NUM!=@Holiday)
346. begin
347. /*print @E_ID
348. print @H_CREATE_NUM;
349. print @Holiday;*/
350. update dbo.EMPLOYEE_HOLIDAY set H_STATUS='N' where E_ID=@E_ID and H_STATUS='Y'
351. insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
352. (@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
353. end
354. break;
355. end
356. --关闭游标，释放内存
357. close mycursor1
358. deallocate mycursor1
359. END
360.
361. USE [fau]
362. GO
363. /****** 对象: UserDefinedFunction [dbo].[ISOyear] 脚本日期: 06/09/2010 13:36:06 ******/
364. SET ANSI_NULLS ON
365. GO
366. SET QUOTED_IDENTIFIER ON
367. GO
368.
369. CREATE FUNCTION [dbo].[ISOyear] (@DATE datetime)
370. RETURNS int
371. WITH EXECUTE AS CALLER
372. AS
373. BEGIN
374. DECLARE @ISOyear int;
375. if(Year(Getdate())>Year(@DATE))
376. set @ISOyear=Year(Getdate())-Year(@DATE)
377. if(Month(Getdate())=Month(@DATE))
378. begin
379. if(Day(Getdate())>=Day(@DATE))
380. set @ISOyear=@ISOyear+1
381. end
382. else if (Month(Getdate())>Month(@DATE))
383. set @ISOyear=@ISOyear+1
384. RETURN(@ISOyear);
385. END;
386.
387.
388. set ANSI_NULLS ON
389. set QUOTED_IDENTIFIER ON
390. go
391.
392.
393. ALTER PROCEDURE [dbo].[sp_alterdiagram]
394. (
395. @diagramname sysname,
396. @owner_id int = null,
397. @version int,
398. @definition varbinary(max)
399. )
400. WITH EXECUTE AS 'dbo'
401. AS
402. BEGIN
403. set nocount on
404.
405. declare @theId int
406. declare @retval int
407. declare @IsDbo int
408.
409. declare @UIDFound int
410. declare @DiagId int
411. declare @ShouldChangeUID int
412.
413. if(@diagramname is null)
414. begin
415. RAISERROR ('Invalid ARG', 16, 1)
416. return -1
417. end
418.
419. execute as caller;
420. select @theId = DATABASE_PRINCIPAL_ID();
421. select @IsDbo = IS_MEMBER(N'db_owner');
422. if(@owner_id is null)
423. select @owner_id = @theId;
424. revert;
425.
426. select @ShouldChangeUID = 0
427. select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
428.
429. if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
430. begin
431. RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
432. return -3
433. end
434.
435. if(@IsDbo <> 0)
436. begin
437. if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
438. begin
439. select @ShouldChangeUID = 1 ;
440. end
441. end
442.
443. -- update dds data
444. update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;
445.
446. -- change owner
447. if(@ShouldChangeUID = 1)
448. update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;
449.
450. -- update dds version
451. if(@version is not null)
452. update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;
453.
454. return 0
455. END
456.
457.
458.
459.
460. set ANSI_NULLS ON
461. set QUOTED_IDENTIFIER ON
462. go
463.
464.
465. ALTER PROCEDURE [dbo].[sp_creatediagram]
466. (
467. @diagramname sysname,
468. @owner_id int = null,
469. @version int,
470. @definition varbinary(max)
471. )
472. WITH EXECUTE AS 'dbo'
473. AS
474. BEGIN
475. set nocount on
476.
477. declare @theId int
478. declare @retval int
479. declare @IsDbo int
480. declare @userName sysname
481. if(@version is null or @diagramname is null)
482. begin
483. RAISERROR (N'E_INVALIDARG', 16, 1);
484. return -1
485. end
486.
487. execute as caller;
488. select @theId = DATABASE_PRINCIPAL_ID();
489. select @IsDbo = IS_MEMBER(N'db_owner');
490. revert;
491.
492. if @owner_id is null
493. begin
494. select @owner_id = @theId;
495. end
496. else
497. begin
498. if @theId <> @owner_id
499. begin
500. if @IsDbo = 0
501. begin
502. RAISERROR (N'E_INVALIDARG', 16, 1);
503. return -1
504. end
505. select @theId = @owner_id
506. end
507. end
508. -- next 2 line only for test, will be removed after define name unique
509. if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
510. begin
511. RAISERROR ('The name is already used.', 16, 1);
512. return -2
513. end
514.
515. insert into dbo.sysdiagrams(name, principal_id , version, definition)
516. VALUES(@diagramname, @theId, @version, @definition) ;
517.
518. select @retval = @@IDENTITY
519. return @retval
520. END
521.
522.
523. set ANSI_NULLS ON
524. set QUOTED_IDENTIFIER ON
525. go
526.
527.
528. ALTER PROCEDURE [dbo].[sp_dropdiagram]
529. (
530. @diagramname sysname,
531. @owner_id int = null
532. )
533. WITH EXECUTE AS 'dbo'
534. AS
535. BEGIN
536. set nocount on
537. declare @theId int
538. declare @IsDbo int
539.
540. declare @UIDFound int
541. declare @DiagId int
542.
543. if(@diagramname is null)
544. begin
545. RAISERROR ('Invalid value', 16, 1);
546. return -1
547. end
548.
549. EXECUTE AS CALLER;
550. select @theId = DATABASE_PRINCIPAL_ID();
551. select @IsDbo = IS_MEMBER(N'db_owner');
552. if(@owner_id is null)
553. select @owner_id = @theId;
554. REVERT;
555.
556. select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
557. if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
558. begin
559. RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
560. return -3
561. end
562.
563. delete from dbo.sysdiagrams where diagram_id = @DiagId;
564.
565. return 0;
566. END
567.
568.
569. set ANSI_NULLS ON
570. set QUOTED_IDENTIFIER ON
571. go
572.
573.
574. ALTER PROCEDURE [dbo].[sp_helpdiagrams]
575. (
576. @diagramname sysname = NULL,
577. @owner_id int = NULL
578. )
579. WITH EXECUTE AS N'dbo'
580. AS
581. BEGIN
582. DECLARE @user sysname
583. DECLARE @dboLogin bit
584. EXECUTE AS CALLER;
585. SET @user = USER_NAME();
586. SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
587. REVERT;
588. SELECT
589. [Database] = DB_NAME(),
590. [Name] = name,
591. [ID] = diagram_id,
592. [Owner] = USER_NAME(principal_id),
593. [OwnerID] = principal_id
594. FROM
595. sysdiagrams
596. WHERE
597. (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
598. (@diagramname IS NULL OR name = @diagramname) AND
599. (@owner_id IS NULL OR principal_id = @owner_id)
600. ORDER BY
601. 4, 5, 1
602. END
603.
发表评论
相关推荐
SqlServer存储过程的导出导入 近日在研究SQL的存储过程，这里我陆续将我在使用存储过程中碰到的问题及解决办法发布到网上，请各位大虾指正。 SqlServer存储过程的导出导入，网上相关资料特别少，经过摸索，这里写...
SQLserver存储过程异常处理
主要介绍了SQLServer存储过程中事务的使用方法，简短的代码带大家更好的学习使用SQLServer存储过程中事务，感兴趣的小伙伴们可以参考一下
详细描述SqlServer存储过程，从认识、创建到原理和写法
执行Sqlserver存储过程返回DataSet
详细介绍了SqlServer的存储过程调试方法，适合初学者及想深入研究的开发人员
文档记录了SQL Server在存储运用时的语法，需要有一定的SQL语言学习基本，本文档只是做了梳理与总结，以便查看和识记。
SQLServer存储过程（stored procedure）从入门到精通 内附详细实例
亲测SqlServer存储过程解密工具(dbForge SQL Decryptor 3.1.24) 支持sql2000、2005、2008(R2）、2012、2014、2016、2017的存储过程的解密； 程序运行需要 .NET Framework 4.5.2 or higher installed
SQLSERVER 存储过程 语法
SQL Server数据库中直接调用Web Service，不需要通过前台调用
SQLSERVER存储过程例子
SQL存储过程习题,SQL触发器习题,SQL习题,基础语法，与W3School类似，初学者学习使用。
SqlServer存储过程游标 ，一个例子
SQLServer存储过程在系统开发中的应用 SQLServer存储过程在系统开发中的应用
简单的学习例子 sqlserver 存储过程、函数的创建和使用 和 常用知识点
VB 调用 sql server 存储过程。文档
SQL Server存储过程的命名标准,整理了sql中的一些存储过程
可以将SQL Server存储过程转为oracle存储过程的工具
SQL SERVER 存储过程的操作与管理