create table stu
(
id int,
name varchar(50)
)
goinsert into stu values(1,'aa')
insert into stu values(2,'bb')
insert into stu values(3,'cc')
insert into stu values(4,'dd')goCREATE FUNCTION dbo.fun_stu()
RETURNS varchar(500)
AS
BEGIN
declare @s varchar(50)
select @s=isnull(@s+',','')+cast(id as varchar(50)) from stu
RETURN @s
END
GO
select dbo.fun_stu()go------------ 现在我要执行一个子查询
--例如
select * from stu where id in(dbo.fun_stu()) ---这里我不知道如何做了 通过函数查询出来的为字符串
(
id int,
name varchar(50)
)
goinsert into stu values(1,'aa')
insert into stu values(2,'bb')
insert into stu values(3,'cc')
insert into stu values(4,'dd')goCREATE FUNCTION dbo.fun_stu()
RETURNS varchar(500)
AS
BEGIN
declare @s varchar(50)
select @s=isnull(@s+',','')+cast(id as varchar(50)) from stu
RETURN @s
END
GO
select dbo.fun_stu()go------------ 现在我要执行一个子查询
--例如
select * from stu where id in(dbo.fun_stu()) ---这里我不知道如何做了 通过函数查询出来的为字符串
declare @sql nvarchar(1000), @str varchar(500)
select @str=dbo.fun_stu()
set @sql='select * from stu where id in(' + @str + ')'
exec sp_executesql @sql
from stu
where charindex(ltrim(id),dbo.fun_stu()) > 0
最好改成SQL codeselect *
from stu
where charindex(','+ltrim(id)+',',dbo.fun_stu()) > 0
create table stu
(
id int,
name varchar(50)
)
go insert into stu values(1,'aa')
insert into stu values(2,'bb')
insert into stu values(3,'cc')
insert into stu values(4,'dd')
go
CREATE FUNCTION dbo.fun_stu()
RETURNS varchar(500)
AS
BEGIN
declare @s varchar(50)
select @s=isnull(@s+',','')+cast(id as varchar(50)) from stu
RETURN @s
END
GO
--select dbo.fun_stu() go ------------ 现在我要执行一个子查询
--例如
select * from stu where charindex( CAST( id as varchar(10)), ( dbo.fun_stu()) )>0 id name
----------- --------------------------------------------------
1 aa
2 bb
3 cc
4 dd(4 行受影响)