如何写一个存储过程 ?

2022-09-22 DB DB, 整理评论

存储过程相当于把一段动态SQL封装到一个方法里面. 传入参数不同, 执行的SQL不同.

创建存储过程


--选中执行
USE YXHIS
GO
drop PROCEDURE [dbo].[MoveDataTest] 
GO
CREATE  PROCEDURE [dbo].[MoveDataTest]
-- 此处下面的4个带@符号的 表示外部传入4个参数 最后一个带out表示此参数可以返回给调用方
@ITYPE varchar(10),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@AERROR varchar(max) out
AS
BEGIN

   PRINT('ok 你调用成功了 你传进来的ITYPE:'+ @ITYPE)
   --时间不能直接打印 因为类型不同 需要转换成字符串
   PRINT('ok 你调用成功了 你传进来的STARTDATE是:'+ CONVERT(VARCHAR(20), @STARTDATE, 21))


END

调用存储过程

--选中执行
DECLARE @AERROR2 VARCHAR(MAX)
EXEC MoveDataTest '999','2021-12-01 13:43:45.000','2020-03-17 13:43:45.000', @AERROR2 OUTPUT
SELECT @AERROR2 AS OUTVALUE

时间转字符串有个21 表示格式不同的值代表不同的格式 参考: https://www.cnblogs.com/alsf/p/6148927.html

1
2
3

需求

根据传入的时间找到挂号表, 把挂号表的第一个病人的门诊号和姓名,写入到中间表. tbmzghmx_mid_test

建表

CREATE TABLE [tbmzghmx_mid_test](cxm [VARCHAR](64) NOT NULL,cmzh [VARCHAR](64) NOT NULL)

如何执行一段sql?

DELETE tbmzghmx_mid_test

-------------------------------------执行SQL的第1种方法-------------------------------------
--肯定要执行下面这个sql
INSERT INTO tbmzghmx_mid_test(cmzh, cxm) SELECT TOP 1 cmzh ,cxm FROM yxhis2022..TBMZGHMX202204
PRINT('第1种方式执行完成')


-------------------------------------执行SQL的第2种方法-------------------------------------
--根据传入时间 这是一个参数 所以不能直接执行 转成字符串 用exec()
EXEC('INSERT INTO tbmzghmx_mid_test(cmzh, cxm) SELECT TOP 1 cmzh ,cxm FROM yxhis2022..TBMZGHMX202204')
PRINT('第2种方式执行完成')


-------------------------------------执行SQL的第3种方法-------------------------------------
---上面两个SQL 执行效果是完全一样的 我们还可以定义一个变量把SQL存起来
--定义一个变量
DECLARE @csql VARCHAR(100) 
--把变量赋值一段SQL
SET @csql = 'INSERT INTO tbmzghmx_mid_test(cmzh, cxm) SELECT TOP 1 cmzh ,cxm FROM yxhis2022..TBMZGHMX202204'
--执行这个变量
EXEC(@csql)
--因为是一个变量 我们还可以打印出来
PRINT(@csql)
PRINT('第3种方式执行完成')

SELECT * FROM tbmzghmx_mid_test

此时只需要把第三种方式放到存储过程,把表名改成动态的即可

放进存储过程


USE YXHIS
GO
drop PROCEDURE [dbo].[MoveDataTest] 
GO
CREATE  PROCEDURE [dbo].[MoveDataTest]
-- 此处下面的4个带@符号的 表示外部传入4个参数 最后一个带out表示此参数可以返回给调用方
@ITYPE varchar(10),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@AERROR varchar(max) out
AS
BEGIN
	--直接赋值到存储过程

	DECLARE @csql VARCHAR(100) 
	--把变量赋值一段SQL
	SET @csql = 'INSERT INTO tbmzghmx_mid_test(cmzh, cxm) SELECT TOP 1 cmzh ,cxm FROM yxhis2022..TBMZGHMX202204'
	--执行这个变量
	EXEC(@csql)
	--因为是一个变量 我们还可以打印出来
	PRINT(@csql)
	PRINT('第3种方式执行完成')


END


DELETE tbmzghmx_mid_test

DECLARE @AERROR2 VARCHAR(MAX)
EXEC MoveDataTest '999','2021-12-01 13:43:45.000','2020-03-17 13:43:45.000', @AERROR2 OUTPUT
SELECT @AERROR2 AS OUTVALUE

SELECT * FROM tbmzghmx_mid_test

时间改成动态


USE YXHIS
GO
drop PROCEDURE [dbo].[MoveDataTest] 
GO
CREATE  PROCEDURE [dbo].[MoveDataTest]
-- 此处下面的4个带@符号的 表示外部传入4个参数 最后一个带out表示此参数可以返回给调用方
@ITYPE varchar(10),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@AERROR varchar(max) out
AS
BEGIN
	--直接赋值到存储过程

	PRINT(@STARTDATE)
	PRINT(CONVERT(varchar(20),@STARTDATE,112))
	PRINT(LEFT(CONVERT(varchar(20),@STARTDATE,112),4))
	PRINT(LEFT(CONVERT(varchar(20),@STARTDATE,112),6))

END


DELETE tbmzghmx_mid_test

DECLARE @AERROR2 VARCHAR(MAX)
EXEC MoveDataTest '999','2021-12-01 13:43:45.000','2020-03-17 13:43:45.000', @AERROR2 OUTPUT
SELECT @AERROR2 AS OUTVALUE

SELECT * FROM tbmzghmx_mid_test

把3.1的存储过程时间改成动态


USE YXHIS
GO
drop PROCEDURE [dbo].[MoveDataTest] 
GO
CREATE  PROCEDURE [dbo].[MoveDataTest]
-- 此处下面的4个带@符号的 表示外部传入4个参数 最后一个带out表示此参数可以返回给调用方
@ITYPE varchar(10),
@STARTDATE DATETIME,
@ENDDATE DATETIME,
@AERROR varchar(max) out
AS
BEGIN

	DECLARE @csql VARCHAR(100) 
	--把变量赋值一段SQL
	SET @csql = 'INSERT INTO tbmzghmx_mid_test(cmzh, cxm) SELECT TOP 1 cmzh ,cxm '
	+' FROM yxhis'+LEFT(CONVERT(varchar(20),@STARTDATE,112),4)+'..TBMZGHMX'+LEFT(CONVERT(varchar(20),@STARTDATE,112),6)
	--执行这个变量
	EXEC(@csql)
	--因为是一个变量 我们还可以打印出来
	PRINT(@csql)
	PRINT('第3种方式执行完成')
END


DELETE tbmzghmx_mid_test

DECLARE @AERROR2 VARCHAR(MAX)
EXEC MoveDataTest '999','2021-12-01 13:43:45.000','2020-03-17 13:43:45.000', @AERROR2 OUTPUT
SELECT @AERROR2 AS OUTVALUE

SELECT * FROM tbmzghmx_mid_test

对比一下修改前后

此时已经完成了需求, 在调用 MoveDataTest时, 把’2021-12-01 13:43:45.000’ 改成任意值, 就可以把当月的数据抓取一条转移到中间表

文档的存储过程解析

其实很简单跟上面的需求基本一致.
主要是要理解到 print函数的意义, 如果执行了print 就会显示到消息页.
如果不知道哪行是否执行没有, 就加一个print, 如果加了2个print, 消息页只有一个print, 那么两个print之间就出问题了.
存储过程的调试, 就是加一堆print , 然后调用. 观察消息看看是否执行了, 加到哪里需要根据经验, 可以隔行加, 可以分析一下哪行可能有问题, 再加到哪行.