Sqlserver存储过程—----传递数组、使用循环 Sqlserver 存储过程 数组 循环 背景： 前一段时间做过一个公司内部的员工项目管理系统，其间有个sqlserver存储过程是要从excel文件中读取员工的项目信息，并从db中找出相关的的其他信息插入到一张员工表中。这里涉及两个难点：怎么向存储过程传递多条记录？在存储过程中怎么对这些记录一条条循环处理。
解决方案： 1. 怎么将excel中数据传入到存储过程中？ 存储过程是没有办法接受数组参数的，我的做法是先把excel中的信息导入到一张全局的临时表中，在把临时表的表名传递给存储过程，在存储过程中使用临时表内容。
2. 怎么在存储过程中做循环处理？ 在存储过程中新建一张临时表，将传递过来的全局临时表的信息保存在该表中，并且在表中添加一个id列，对记录从一开始计数。使用count函数计算记录的个数，使用while语句对id从1到count进行循环
代码：
1.创建临时表：
IF OBJECT_ID('dbo.create_tempTable') IS NOT NULL DROP PROC dbo.create_tempTable; GO CREATE PROC dbo.create_tempTable AS CREATE TABLE ##tempexcel( pjid char(6),--项目id userid char(6),--用户id PRIMARY KEY(pjid, userid) ) GO
2.具体操作的存储过程：
IF OBJECT_ID('dbo.Staff_insert') IS NOT NULL DROP PROC dbo.Staff_insert; GO CREATE PROC dbo.Staff_insert @temptableName nvarchar(20)--临时表名 AS BEGIN --用于实现循环的临时表，intID列用于从1开始对记录计数 CREATE TABLE #excelData(intID int identity(1,1),pjid char(6),userid char(6)); --将全局临时表中的数据插入到临时表中 INSERT INTO #excelData(pjid,userid) EXEC('SELECT pjid, userid FROM '+ @temptableName); --计算记录的个数 SELECT @count= COUNT(1) FROM #excelData; --如果记录存在，进入循环 IF(@count > 0) BEGIN SET @i = 1;--循环变量 WHILE(@i <= @count) BEGIN -- 根据intid取出要操作的记录 SELECT @pjid=pjid,@userid=userid FROM #excelData WHERE intID = @i; --其他操作 -- -- END END END 想把一个数据表里的某个字段筛选出来然后，循环的把值传给另一个存储过程，选择使用了临时表。 SQL代码如下： CREATE TABLE #Temp_TABLE ( ID INT IDENTITY(1,1), TRANSID VARCHAR(30), --出库单号 PRIMARY KEY (ID) ) INSERT INTO #Temp_TABLE SELECT TRANSID FROM A WHERE A.RPID='RP00913031200001'--从A表取出符合条件的数据插入到临时表 DECLARE @TOTAL_COUNT INT,--临时表记录条数 @NOW_COUNT INT,--现在遍历到哪条记录 @TRID VARCHAR(30)--当前遍历的transid SELECT @TOTAL_COUNT=COUNT(*) FROM #Temp_TABLE SET @NOW_COUNT=1; WHILE(@NOW_COUNT<=@TOTAL_COUNT) BEGIN SELECT @TRID=TRANSID FROM #Temp_TABLE WHERE ID=@NOW_COUNT --关键就在这(一切都因这个地方而不一样了哦） EXEC proc_monprocess @TRID--传给另一个存储过程 SET @NOW_COUNT=@NOW_COUNT+1 END DROP TABLE #Temp_TABLE 有关表的数据插入 1、把临时表中的数据插入到另一个表中 insert into 表 select * from #temp 2、把一个表中字段复制到临时表中 select * into #temp from 表 where ... 3、本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的； 4、当用户从 SQL Server 实例断开连接时被删除。 5、判断临时表是否存在 create proc temp(@tablename varchar(200)) as begin declare @exec varchar(8000) set @exec='use tempdb if exists(select * from sysobjects where id=object_id(''tempdb..'+@tablename+''')) select ''存在'' else select ''不存在''' exec (@exec) end
--计算一段时间内工作的天数
select dbo.f_workday('2014-06-01','2014-07-30')
create function f_workday(
-- 参数
@date_begin datetime, --计算开始日期
@date_end datetime ) --计算结束日期
returns int -- 返回值数据类型
as
begin
declare @weeks int, -- 声明变量
@workday int
-- SQL语句(必须有return 变量或值)
---计算整周的工作天数
select @weeks=(datediff (day ,@date_begin, @date_end)+1)/7, -- 计算开始和结束日期的之间的周数（完整的周）
@workday=@weeks*5, --整周的工作天数----
@date_begin=dateadd(day,@weeks*7,@date_begin) --最後一个不完整的周的工作天数
while @date_begin<=@date_end
begin
select @workday = case when (@@datefirst+datepart(weekday,@date_begin)-1)%7 between 1 and 5 then @workday+1
else @workday+1 end, @date_begin =@date_begin+1
end
return (@workday)
end
demo2:
-- 根据 questionID 查询 pName
create FUNCTION [dbo].[getPointNameByQuestionID](@questionID INT)
RETURNs varchar(256)
AS
BEGIN
DECLARE @pName VARCHAR(256)
SELECT DISTINCT TOP 1 @pName = p.pointName
FROM EXPORT_POINT_QUESTION_DHY pq
LEFT JOIN QZ_POINT p ON pq.pointID = p.pointID
WHERE pq.questionID = @questionID
RETURN ISNULL(@pName , '其他' )
END
GO
select dbo.getPointNameByQuestionID(123);
demo3:
-----比较两个值大小，返回小的----
create FUNCTION [dbo].[getMin]
(
@num1 int,
@num2 int
)
RETURNS int
as
BEGIN
declare @num int
if @num1>@num2
begin
set @num=@num2
end
else
begin
set @num=@num1
end
return @num
END
GO
