开发者社区> 技术小牛人> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQLServer-存储过程中使用字符串和分隔符实现传递数组参数(转)

简介:
+关注继续查看
< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd>

一 简介

在高级语言中,很容易编写带有数组参数的函数。但在数据库的存储过程中却没有那么容易,因为存储过程的参数只能以一些基本类型作为参数。我们希望数组作为参数的情况是很常见的,例如有一个表Table(Id int, Data nvarchar(50)),需要向该表一次存入一批数据。如果存储过程以基本数据类型作为参数,定义为InsertData(@data nvarchar(50)), 那么需要循环多次调用该存储过程。

要使存储过程支持数组参数,需做一点变通。可以将需输入的数据转换成字符串,并以某一个分隔符隔开组成一个大的字符串,可以用基本类型text 或 ntext表示。将这个字符串传给存储过程,存储过程内部将其解析,即去掉分隔符,把这批数据放入零时表或某个标变量中,最后批量插入数据表中。

二 解析含有分隔符的字符串生成表变量的流程

SQLServer-存储过程中使用字符串和分隔符实现传递数组参数(转) - netcorner - netcorner的博客

三 解析含有分隔符的字符串生成表变量的用户函数脚本



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitTextToStringArray]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[SplitTextToStringArray]

GO

-- =============================================

-- Author:      fishinthewind

-- Create date:   7/10/2007

-- Description: Split string variant with type of ntext

-- =============================================

CREATE FUNCTION [dbo].[SplitTextToStringArray]

(

   @text ntext,

   @delimiter char(1)

)

RETURNS @arrayTable TABLE(Idx bigint, [Value] nvarchar(200))

AS

BEGIN

     DECLARE @splitlen int

    SET @splitlen = 4000



    DECLARE @Idx int SET @Idx = 0



    -- 定义取子串的起始位置

    DECLARE @textsplit bigint

    SET @textsplit = 1

    WHILE( @textsplit <= DATALENGTH(@text) )

    BEGIN



        -- 由于许多字符串处理函数无法用于ntext数据类型

        -- 所以需要循环按批处理ntext字符串,一批取出

        -- 个字符放入nvarchar(4000)类型的变量中.

        DECLARE @string nvarchar(4000)

        SELECT @string = SUBSTRING(@text,@textsplit,@splitlen)



        -- 能够取出满个字符

        IF LEN(@string) = @splitlen

        BEGIN

            -- 确保取出的个字符是完整的由分隔符隔开的字符串组合

            DECLARE @lastcomma int

            SELECT @lastcomma = CHARINDEX(@delimiter,REVERSE(@string),1)

            -- 最后一个分隔符后面的字符串不完整,应抛弃

            IF @lastcomma > 0

            BEGIN

                SELECT @string = SUBSTRING(@string,1,@splitlen - @lastcomma)

                -- 设置下一次从@text取字符的起始位置

                SELECT @textsplit = @textsplit + @splitlen - @lastcomma + 1

            END

            -- 最后一个分隔符后面的字符串完整.

            ELSE

            BEGIN

                SELECT @textsplit = @textsplit + @splitlen + 1

            END

        END

        -- 取出不满个字符

        ELSE

        BEGIN

            SELECT @textsplit = @textsplit + @splitlen + 1

        END

      

        -- 解析@string,取出以分隔符为界限的子字符串

        DECLARE @i1 int SET @i1 = 1

        DECLARE @i2 int SET @i2 = 1

        WHILE @i1 <= LEN(@string)

        BEGIN

            SET @i2 = CHARINDEX(@delimiter,@string,@i1+1)

            IF @i2 = 0

                SET @i2 = LEN(@string) + 1

            INSERT @arrayTable (Idx, Value)

            SELECT @Idx, SUBSTRING(@string,@i1,@i2-@i1)

            SET @i1 = @i2 + 1

            SET @Idx = @Idx + 1

        END

    END

    RETURN

END

GO



四 使用该字符串分隔函数的过程脚本



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCategoryRevisionIds]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[GetCategoryRevisionIds]

GO



-- =============================================

-- Author:      fishinthewind

-- Create date:   7/10/2007

-- Description: 使用字符分割函数

-- =============================================

CREATE PROCEDURE [dbo].[GetCategoryRevisionIds]

(

   @stringArray ntext

)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    DECLARE @stringArrayT TABLE(Idx bigint, [Value] nvarchar(200))

    INSERT INTO @stringArrayT(Idx, [Value])

    (

       SELECT Idx, [Value]

       FROM SplitTextToStringArray(@stringArray, ',')

    )



    SELECT * FROM @stringArrayT                  

END

GO
本文转自 netcorner 博客园博客,原文链接:http://www.cnblogs.com/netcorner/archive/2008/03/31/2912171.html  ,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
python连接sql server数据库
记录一下pyodbc连接数据库的使用方法和注意事项,基于python2.7:  前提: pip install pyodbc  。下载pyodbc包。   pyodbc.connect('DRIVER=;SERVER=;DATABASE=;UID=;PWD=') DRIVER:对应数据库版本的驱动器,SQL server 2008是“SQL Server Native Client 10.0”, SERVER:数据库服务器名称 DATABASE:数据库名称,UID:账号,PWD:密码。
1455 0
绑定sql server数据库的用户与登录名
服务器重装系统,重新安装上sql server,附加上以前的数据库, 然后以前是每个数据库都有一个登录用于操作该数据库,其他数据库不能操作的, 附加上数据库后该数据库安全性里的用户能看到以前建立好的用户,但是在外面的系统级安全性的登录名中没有对应的用户,一个登录用户了又直接提示用户已存在的, ...
796 0
sql server 复制表从一个数据库到另一个数据库
/*不同服务器数据库之间的数据操作*/ --创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.
989 0
SqlServer定时备份数据库和定时杀死数据库死锁解决
原文:SqlServer定时备份数据库和定时杀死数据库死锁解决 上周五组长更我说了一句要杀死数据库的死锁进程,因为自己对数据库不是很熟悉,突然组长说了我也就决定一定要倒腾一下,不然自己怎么提高呢?现在不研究,说不定下次还是要研究呢,倒腾出来了就可以在下次用到了,后来组长又补了一句:"还有定是备份数据库的问题要解决",说干就干。
1363 0
『开源』仿SQLServer山寨一个 跨数据库客户端
原文:『开源』仿SQLServer山寨一个 跨数据库客户端 002 Laura.SqlForever项目简单介绍   相关文章 《『练手』001 Laura.SqlForever架构基础(Laura.XtraFramework 的变迁)》 《『练手』003 Laura.SqlForever如何扩展 兼容更多数据库引擎》 《『练手』004 Laura.SqlForever如何扩展 导航栏 工具栏 右键菜单 插件》 《『练手』005 Laura.SqlForever历史遗留 的 架构思想缺陷》     项目简介 Laura.SqlForever 是一个 跨数据库 的客户端。
986 0
将ACCESS数据库迁移到SQLSERVER数据库
原文:将ACCESS数据库迁移到SQLSERVER数据库 将ACCESS数据库迁移到SQLSERVER数据库 ACCESS2000文件 用ACCESS2007打开,并迁移到SQLSERVER2005里  打开ACCESS2007的数据库工具 方法一:使用ACCESS2007自带的数据库迁移工...
1509 0
SQLServer数据库降级方法详解
右击数据库,有一个任务选项——扩展里面有一个生成脚本 设置脚本选项 高级 最下方“要编写的脚本类型” 选择 框架及数据 完成
562 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载