store procedure example

最新推荐文章于 2022-07-26 08:37:46 发布

weixin_30552811

最新推荐文章于 2022-07-26 08:37:46 发布

阅读量121

点赞数

CC 4.0 BY-SA版权

文章标签：测试

原文链接：http://www.cnblogs.com/jingRegina/p/5443396.html

本文介绍了一个SQL存储过程的实现方式，该过程能够从指定的表中选择数据并使用自定义分隔符将其导出到另一个目标表。通过游标的使用，此存储过程可以灵活地处理不同表结构的数据导出需求。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [Procedure_Name] 
@tableName VARCHAR(100)    ='',
@destination VARCHAR(100)    ='',
@seprator VARCHAR(100)    =''
AS
BEGIN

SET NOCOUNT ON;


DECLARE @name VARCHAR(100)
select @name=isnull(@name+N',','')+name from tempdb.dbo.syscolumns where id=object_id('euseraccess')
DECLARE @fields VARCHAR(100)

DECLARE @sql VARCHAR(MAX)

DECLARE    FIELD_CUR CURSOR FOR 
select Name FROM SysColumns Where id=Object_Id(@tableName) 
FETCH NEXT FROM FIELD_CUR INTO @fields 
WHILE (@@FETCH_STATUS <> -1) 
BEGIN 
SELECT @fields =concat(@fields,@seprator,@fields); 
FETCH NEXT FROM FIELD_CUR INTO @fields 

END 
CLOSE FIELD_CUR

set @sql = 'select concat(' + @fields + ')from ' +@tableName

DECLARE    result_CUR CURSOR FOR
EXEC(@sql)
FETCH NEXT FROM result_CUR into result
WHILE (@@FETCH_STATUS <> -1) 
begin
insert into table value result
FETCH NEXT FROM result_CUR into result 
end

close result_CUR

END
GO