查指定的表的所有字段中是否有指定的字符串

[zuoguangxin18]

-- 判断要创建的存储过程名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[全文查详单]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- 删除存储过程
drop procedure [dbo].[全文查详单]
GO

create procedure [dbo].[全文查详单]
(
@value varchar(1024),---传入的查的字符
@biaojiHE nvarchar(4000)=‘( select 手机号码 from [案件信息表])’---传入的值select 手机号码 from [案件信息表]
)
as
begin

set nocount on;---不返回影响行
declare @sql varchar(1024)
declare @sql2 nvarchar(4000) -- 这里要用这个类型
declare @table varchar(64)
declare @column varchar(64)
declare @jishu varchar(64)

create table #t (
详单名 varchar(64),
字段名 varchar(64),
出现次数 varchar(64)
)

declare tables cursor
for
---查所有表和列
select o.name, c.name
from syscolumns c
inner join sysobjects o on c.id = o.id
where o.type = 'u' and c.xtype in (167, 175, 231, 239) and (o.name in ( select 手机号码 from [案件信息表]))---用变量不行
order by o.name, c.name

open tables
fetch next from tables into @table, @column
..........................
以上是代码，现在我想把：where o.type = 'u' and c.xtype in (167, 175, 231, 239) and (o.name in ( select 手机号码 from [案件信息表])) 中的select 手机号码 from [案件信息表] 用 @biaojiHE 代替，总是不行。

请教大家如何用传入的值 @biaojiHE

[zuoguangxin18]

引用 1 楼 qq_37170555 的回复:

select o.name, c.name 
from syscolumns c 
inner join sysobjects o on c.id = o.id 
where o.type = 'u' and c.xtype in (167, 175, 231, 239)  and (o.name in ( select 手机号码 from [案件信息表]))---用变量不行
order by o.name, c.name 


--把上面sql替换成下面的就ok了
DECLARE @sql VARCHAR(max),
		@biaojiHE VARCHAR(max)
SET @biaojiHE ='select 手机号码 from [案件信息表]'
SET @sql='select o.name, c.name 
    from syscolumns c 
    inner join sysobjects o on c.id = o.id 
    where o.type = ''u'' and c.xtype in (167, 175, 231, 239)  and (o.name in ( '+@biaojiHE+'))
    order by o.name, c.name 
' 
--PRINT @sql
EXEC (@sql)

那怎么和 declare tables cursor for 这个联起来？

[听雨停了]

引用 4 楼 zuoguangxin18 的回复:

[quote=引用 1 楼 qq_37170555 的回复:]

select o.name, c.name 
from syscolumns c 
inner join sysobjects o on c.id = o.id 
where o.type = 'u' and c.xtype in (167, 175, 231, 239)  and (o.name in ( select 手机号码 from [案件信息表]))---用变量不行
order by o.name, c.name 


--把上面sql替换成下面的就ok了
DECLARE @sql VARCHAR(max),
		@biaojiHE VARCHAR(max)
SET @biaojiHE ='select 手机号码 from [案件信息表]'
SET @sql='select o.name, c.name 
    from syscolumns c 
    inner join sysobjects o on c.id = o.id 
    where o.type = ''u'' and c.xtype in (167, 175, 231, 239)  and (o.name in ( '+@biaojiHE+'))
    order by o.name, c.name 
' 
--PRINT @sql
EXEC (@sql)

那怎么和 declare tables cursor for 这个联起来？ [/quote]

DECLARE @sql VARCHAR(max),
        @biaojiHE VARCHAR(max)
SET @biaojiHE ='select 手机号码 from [案件信息表]'

SET @sql='
	declare tables cursor 
	for
	select o.name, c.name 
    from syscolumns c 
    inner join sysobjects o on c.id = o.id 
    where o.type = ''u'' and c.xtype in (167, 175, 231, 239)  and (o.name in ( '+@biaojiHE+'))
    order by o.name, c.name 
    open tables 
	fetch next from tables into '+@table+','+@column
--PRINT @sql
EXEC (@sql) 

把创建游标的sql也一起拼接进去就ok了

[听雨停了]

select o.name, c.name 
from syscolumns c 
inner join sysobjects o on c.id = o.id 
where o.type = 'u' and c.xtype in (167, 175, 231, 239)  and (o.name in ( select 手机号码 from [案件信息表]))---用变量不行
order by o.name, c.name 


--把上面sql替换成下面的就ok了
DECLARE @sql VARCHAR(max),
		@biaojiHE VARCHAR(max)
SET @biaojiHE ='select 手机号码 from [案件信息表]'
SET @sql='select o.name, c.name 
    from syscolumns c 
    inner join sysobjects o on c.id = o.id 
    where o.type = ''u'' and c.xtype in (167, 175, 231, 239)  and (o.name in ( '+@biaojiHE+'))
    order by o.name, c.name 
' 
--PRINT @sql
EXEC (@sql)

[中国风]

看你的需求 @biaojiHE--这条件应该放在拼凑语句里

[中国风]

在这个位置加、 fetch next from tables into @table, @column 后面

SET @sql='SELECT * FROM '+@table+' WHERE '+@column+' IN('+@biaojiHE+')'