CREATE PROCEDURE dbo.sp_valueSearch
@value sql_variant,
@precision bit = 1
AS
set NOCOUNT on
if @value is null return
select xtype into #t from systypes
where name =sql_variant_property(@value,N'BaseType')
declare @sql nvarchar(4000),@sql1 nvarchar(4000)
if @precision =1
set @sql = case
sql_variant_property(@value,N'BaseType')
when N'text' then N'like N ''%'+cast(@value as varchar(8000))+'%'''
when N'ntext' then N' like ''%'+cast(@value as nvarchar(4000))+'%'''
else N'=@value' end
else
begin
set @sql=cast(sql_variant_property(@value,N'BaseType') as sysname)
if @sql like N'%char' or @sql like N'%text'
begin
insert #t select xtype from systypes
where name like N'%char' or name like N'%text'
select @sql=N' like N''%''+cast(@values as ' +case
when left(@sql,1)=N'n' then 'nvarchar(4000)'
else 'varchar(8000)' end
+N')+N''%'''
end
else if @sql like N'%datetime'
begin
insert #t select xtype from systypes
where name like N'%datetime'
set @sql=N'=@value'
end
else if @sql like N'%int'
or @sql like N'%money'
or @sql in(N'real',N'float',N'decimal',N'numeric')
begin
insert #t select xtype from systypes
where name like N'%int'
or name like N'%money'
or name in(N'real',N'float',N'decimal')
set @sql=N'=@value'
end
else
set @sql=N'=@value'
end
--保存结果的临时表
create table #(tableName sysname,FiledName sysname, Type sysname ,SQL nvarchar(2000))
declare tb cursor local
for
select N'select * from '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+Quotename(o.name)
+N' where ' + QUOTENAME(c.name)
+@sql,
N'insert # values(N'+QUOTENAME(o.name ,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTEName(QUOTENAME(t.name)+case
when t.name in (N'decimal',N'numeric')
then N'('+cast(c.prec as varchar)+N','+cast(c.scale as varchar)+N')'
when t.name=N'float'
or t.name like N'%char'
or t.name like N'%binary'
then N'('+cast(c.prec as varchar)+N')'
else N'' end ,N'''')
+N',@sql)'
from sysobjects o,syscolumns c,systypes t ,#t tt
where o.id=c.id
and c.xusertype=t.xusertype
and t.xtype=tt.xtype
and objectproperty(o.id ,N'IsUsertable')=1 open tb
fetch tb into @sql ,@sql1
while @@fetch_status=0
begin
set @sql1=N' if exists('+@sql+N')'+@sql1
exec sp_executesql @sql1,N'@value sql_variant ,@sql nvarchar(4000)',@value,@sql
fetch tb into @sql,@sql1
end
close tb
deallocate tb
select * from # go
exec sp_valueSearch 'abc'drop proc sp_valueSearch
@value sql_variant,
@precision bit = 1
AS
set NOCOUNT on
if @value is null return
select xtype into #t from systypes
where name =sql_variant_property(@value,N'BaseType')
declare @sql nvarchar(4000),@sql1 nvarchar(4000)
if @precision =1
set @sql = case
sql_variant_property(@value,N'BaseType')
when N'text' then N'like N ''%'+cast(@value as varchar(8000))+'%'''
when N'ntext' then N' like ''%'+cast(@value as nvarchar(4000))+'%'''
else N'=@value' end
else
begin
set @sql=cast(sql_variant_property(@value,N'BaseType') as sysname)
if @sql like N'%char' or @sql like N'%text'
begin
insert #t select xtype from systypes
where name like N'%char' or name like N'%text'
select @sql=N' like N''%''+cast(@values as ' +case
when left(@sql,1)=N'n' then 'nvarchar(4000)'
else 'varchar(8000)' end
+N')+N''%'''
end
else if @sql like N'%datetime'
begin
insert #t select xtype from systypes
where name like N'%datetime'
set @sql=N'=@value'
end
else if @sql like N'%int'
or @sql like N'%money'
or @sql in(N'real',N'float',N'decimal',N'numeric')
begin
insert #t select xtype from systypes
where name like N'%int'
or name like N'%money'
or name in(N'real',N'float',N'decimal')
set @sql=N'=@value'
end
else
set @sql=N'=@value'
end
--保存结果的临时表
create table #(tableName sysname,FiledName sysname, Type sysname ,SQL nvarchar(2000))
declare tb cursor local
for
select N'select * from '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+Quotename(o.name)
+N' where ' + QUOTENAME(c.name)
+@sql,
N'insert # values(N'+QUOTENAME(o.name ,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTEName(QUOTENAME(t.name)+case
when t.name in (N'decimal',N'numeric')
then N'('+cast(c.prec as varchar)+N','+cast(c.scale as varchar)+N')'
when t.name=N'float'
or t.name like N'%char'
or t.name like N'%binary'
then N'('+cast(c.prec as varchar)+N')'
else N'' end ,N'''')
+N',@sql)'
from sysobjects o,syscolumns c,systypes t ,#t tt
where o.id=c.id
and c.xusertype=t.xusertype
and t.xtype=tt.xtype
and objectproperty(o.id ,N'IsUsertable')=1 open tb
fetch tb into @sql ,@sql1
while @@fetch_status=0
begin
set @sql1=N' if exists('+@sql+N')'+@sql1
exec sp_executesql @sql1,N'@value sql_variant ,@sql nvarchar(4000)',@value,@sql
fetch tb into @sql,@sql1
end
close tb
deallocate tb
select * from # go
exec sp_valueSearch 'abc'drop proc sp_valueSearch
解决方案 »
- SQL存储过程能否传一个table参数,如不能则能不能在两个存储过程之间共享这个table参数,急.....
- 请问,在服务端用哪一个函数可以取得客户端名称?
- 关于日志压缩 不敢独享,快来看看!
- SQL的定时作业,存储过程等可以不给用户看吗?
- SQL中删除某个表的一条纪录时出错:键列信息不足或不正确,更新影响更多行!请问如何解决啊!
- 为什么Insert.....select语句不能运行
- 如何实现下面的数据转换?
- sqlserver访问被拒绝,已打sp3补丁
- 怎样在存储过程中分别取得某个日期字段的年部分月部分
- 只要答案符合逻辑每人100分!!!
- 请教表记录分组横向显示问题!谢谢
- 如何恢复某一时间段的数据?
@value sql_variant,
@precision bit = 1
AS
set NOCOUNT on
if @value is null return
select xtype into #t from systypes
where name =sql_variant_property(@value,N'BaseType') declare @sql nvarchar(4000),@sql1 nvarchar(4000)
if @precision =1
set @sql = case
sql_variant_property(@value,N'BaseType')
when N'text' then N'like N ''%'+cast(@value as varchar(8000))+'%'''
when N'ntext' then N' like ''%'+cast(@value as nvarchar(4000))+'%'''
else N'='''+convert(char(19),@value,120)+'''' end
else
begin
set @sql=cast(sql_variant_property(@value,N'BaseType') as sysname)
if @sql like N'%char' or @sql like N'%text'
begin
insert #t select xtype from systypes
where name like N'%char' or name like N'%text'
select @sql=N' like N''%''+cast(@values as ' +case
when left(@sql,1)=N'n' then 'nvarchar(4000)'
else 'varchar(8000)' end
+N')+N''%'''
end
else if @sql like N'%datetime'
begin
insert #t select xtype from systypes
where name like N'%datetime'
set @sql=N'='''+convert(char(19),@value,120)+''''
end
else if @sql like N'%int'
or @sql like N'%money'
or @sql in(N'real',N'float',N'decimal',N'numeric')
begin
insert #t select xtype from systypes
where name like N'%int'
or name like N'%money'
or name in(N'real',N'float',N'decimal')
set @sql=N'='''+convert(char(19),@value,120)+''''
end
else
set @sql=N'='''+convert(char(19),@value,120)+''''
end
--保存结果的临时表
create table #(tableName sysname,FiledName sysname, Type sysname ,SQL nvarchar(2000))
declare tb cursor local
for
select N'select * from '
+QUOTENAME(USER_NAME(o.uid))
+N'.'+Quotename(o.name)
+N' where ' + QUOTENAME(c.name)
+@sql,
N'insert # values(N'+QUOTENAME(o.name ,N'''')
+N',N'+QUOTENAME(c.name,N'''')
+N',N'+QUOTEName(QUOTENAME(t.name)+case
when t.name in (N'decimal',N'numeric')
then N'('+cast(c.prec as varchar)+N','+cast(c.scale as varchar)+N')'
when t.name=N'float'
or t.name like N'%char'
or t.name like N'%binary'
then N'('+cast(c.prec as varchar)+N')'
else N'' end ,N'''')
+N',@sql)'
from sysobjects o,syscolumns c,systypes t ,#t tt
where o.id=c.id
and c.xusertype=t.xusertype
and t.xtype=tt.xtype
and objectproperty(o.id ,N'IsUsertable')=1 open tb
fetch tb into @sql ,@sql1
while @@fetch_status=0
begin
set @sql1=N' if exists('+@sql+N')'+@sql1
--print @sql
exec sp_executesql @sql1,N'@value sql_variant ,@sql nvarchar(4000)',@value,@sql
fetch tb into @sql,@sql1
end
close tb
deallocate tb
select * from # go declare @dt datetime
set @dt='2006-03-14 16:40:12.410'
exec sp_valuesearch @value=@dt,@precision=0
drop proc sp_valueSearch