动态执行--获取某数据库某表某些字段的唯一值

USE [kjcore] GO /****** Object: StoredProcedure [dbo].[up_获取某数据库某表某些字段的唯一值] Script Date: 2014-12-11 15:25:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================

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

create PROCEDURE [dbo].[up_获取某数据库某表某些字段的唯一值]

@dbName nvarchar(100)='',

@tableName nvarchar (100),

@columnNameList nvarchar (100),

@whereKeyList nvarchar(500),

@whereValueList nvarchar(2000),

@returnValue nvarchar(2000) output,

@whereKeySplit nvarchar(2)=',', @whereValueSplit nvarchar(2)=',', @columnNameSplit nvarchar(2)=',', @columnValueSplit nvarchar(2)=','

DECLARE @sql nvarchar(2000),@tempValue nvarchar(2000) ,@whereCondition nvarchar(1000)

DECLARE @columnNumber int=1,@columnNext int =1,@columnName nvarchar(100) ,@colunmValue nvarchar(500)

SET@dbName=RTRIM(LTRIM(@dbName)) --数据库名

SET @tableName =RTRIM(LTRIM(@tableName)) --表名

SET @columnNameList=RTRIM(LTRIM(@columnNameList)) --要生成编号的字段名

SET@returnValue=''

BEGIN TRY

if LEN(@whereKeyList)>0

begin select @whereCondition=key字段名等于值 from [ufn_获取表复合主键相关信息](@whereKeyList,@whereValueList,@whereKeySplit,@whereValueSplit) end

select @columnNumber= kjcore.dbo.ufn_getStrArrayLength(@columnNameList,@columnNameSplit)

while @columnNext<=@columnNumber

begin

set @columnName=kjcore.dbo.ufn_getStrArrayStrOfIndex(@columnNameList,@columnNameSplit,@columnNext)

set @sql='select @tempValue='+@columnName+' from '+@dbName+'.dbo.'+@tableName

set @sql=@sql+' where '+@whereCondition

--print @sql

exec sp_executesql @sql,N'@tempValue nvarchar(2000) output',@colunmValue output

if @columnNext=1 begin set @returnValue= isnull(@colunmValue,'NULL') end

else begin set @returnValue= @returnValue+@columnValueSplit+isnull(@colunmValue,'NULL') end

set @columnNext=@columnNext+1

end -

END TRY

BEGIN CATCH EXECUTE [kjcore].[dbo].[up_PrintError]; RAISERROR ('获取某数据库某表某字段的唯一值，请及时联系系统管理员',16,1) END CATCH;

--declare @returnValue nvarchar(200)

--exec [up_获取某数据库某表某些字段的唯一值] 'mywt_mis_kj_jyglxt_____','T各煤业公司出库表B','本日','myid','20',@returnValue output --select @returnValue

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

--执行本过程用到的自定义函数（ufn_getStrArrayLength）

USE [kjcore]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_getStrArrayLength]    Script Date: 2014-12-11 15:35:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[ufn_getStrArrayLength]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
   while @location<>0
     begin
      set @start=@location+1
      set @location=charindex(@split,@str,@start)
      set @length=@length+1
     end
   return @length
end

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

USE [kjcore] GO /****** Object: UserDefinedFunction [dbo].[ufn_获取表复合主键相关信息] Script Date: 2014-12-11 15:37:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[ufn_获取表复合主键相关信息](@keyList varchar(2000),@valueList varchar(4000),@keySplit varchar(20)=',',@valueSplit varchar(20)=',') RETURNS @t Table( key字段定义 nvarchar(2000), --动态参数1（@单位号 varchar(200),@统计日期 varchar(200),@统计项目号 varchar(200)） key字段值 nvarchar(4000), --动态参数2（ '1011','2014-04-21','01'） key字段名等于值 nvarchar(4000),--StaticCondition（单位号='1011' and 统计日期='2014-05-05'） nkey字段定义 nvarchar(2000), ----N'@单位号 varchar(200),@统计日期 varchar(200)' key字段名等于双引号值 nvarchar(4000) --（单位号=''1011'' and 统计日期=''2014-05-05''） ) AS BEGIN

declare @next int=1,@key值 nvarchar(500)='',@key名 nvarchar(200)='',@key字段定义 nvarchar(4000)='',@key字段值 nvarchar(4000)='',@key字段名等于值 nvarchar(4000)='',@nkey字段定义 nvarchar(2000)='',@key字段名等于双引号值 nvarchar(2000)='' if(@keyList!='') begin set @key值=kjcore.dbo.ufn_getStrArrayStrOfIndex(@valueList,@valueSplit,@next) set @key名=kjcore.dbo.ufn_getStrArrayStrOfIndex(@keyList,@keySplit,@next) set @key字段定义='@'+@key名+' varchar(200)' set @key字段值=QUOTENAME(@key值,'''') if(@key值='NULL') BEGIN set @key字段名等于值=@key名+' IS '+@key值 set @key字段名等于双引号值=@key名+' IS '+@key值 END ELSE BEGIN set @key字段名等于值=@key名+'='+ QUOTENAME(@key值,'''') set @key字段名等于双引号值=@key名+'='+''''+QUOTENAME(@key值,'''')+'''' END set @nkey字段定义='N'+''''+'@'+@key名+' varchar(200)' set @next=@next+1 while @next<=kjcore.dbo.ufn_getStrArrayLength(@valueList,@valueSplit) begin set @key值=kjcore.dbo.ufn_getStrArrayStrOfIndex(@valueList,@valueSplit,@next) set @key名=kjcore.dbo.ufn_getStrArrayStrOfIndex(@keyList,@keySplit,@next) set @key字段定义=@key字段定义+','+'@'+@key名+' varchar(200)' set @key字段值=@key字段值+','+QUOTENAME(@key值,'''') if(@key值='NULL') BEGIN set @key字段名等于值=@key字段名等于值+ ' and '+@key名+' IS '+@key值 set @key字段名等于双引号值=@key字段名等于双引号值+ ' and '+@key名+' IS '+@key值 END ELSE BEGIN set @key字段名等于值=@key字段名等于值+ ' and '+@key名+'='+QUOTENAME(@key值,'''') set @key字段名等于双引号值=@key字段名等于双引号值+ ' and '+@key名+'='+''''+QUOTENAME(@key值,'''')+'''' END set @nkey字段定义=@nkey字段定义+','+'@'+@key名+' varchar(200)' set @next=@next+1 end set @nkey字段定义=@nkey字段定义+'''' end insert @t(key字段定义,key字段值,key字段名等于值,nkey字段定义,key字段名等于双引号值) values(@key字段定义,@key字段值,@key字段名等于值,@nkey字段定义,@key字段名等于双引号值)

return END

--select * from [ufn_获取表复合主键相关信息]( '单位号,统计日期','2,3',default,default)