sql自定义函数不能使用子查询赋值吗

[凡人皆侑一死]

use
testok
go
alter function getmess(@para varchar(1000),@seekindex int)
returns varchar(50)
as
begin
declare @index int,@temp varchar(1000),@i int
set @i=0
while @i<=@seekindex
begin
set @index=CHARINDEX(',',@para)
set @i=@i+1
set @temp=SUBSTRING(@para,@index,LEN(@para)-@index)
end
return @temp
end

create table testlog(id int,parama varchar(1000),createdate date default getdate())
insert into testlog values(1,'a,dd,cc,eee,tttt,ooo,,,d','2047-12-01')
select * from testlog
select dbo.getmess('a,dd,cc,eee,tttt,ooo,,,d',3)

自己写了个函数，为什么我用select dbo.getmess(select parama from testlog where id=1,3) 查询不行啊

[顺势而为1]

你的参数是字符串 select parama from testlog where id=1 可不是字符串, 下面这样才行.

DECLARE @para varchar(1000)
Select @para=parama from testlog 

select dbo.getmess(@Para,3) 

[凡人皆侑一死]

不好意思，函数写的有问题，我重写了

use
testok
go
alter function getmess(@para varchar(1000),@seekindex int)
returns varchar(50)
as 
begin
declare @index int,@temp varchar(1000),@i int
set @i=1
  while @i<=@seekindex
  begin
  set @index=CHARINDEX(',',@para)
  set @i=@i+1
  set @temp=SUBSTRING(@para,@index+1,LEN(@para)-@index+1)
  set @para=@temp
  end
return @temp
end

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('getmess') IS NOT NULL
DROP FUNCTION getmess
GO
CREATE function getmess(@para varchar(1000),@seekindex int)
returns varchar(50)
as 
begin
declare @index int,@temp varchar(1000),@i int
set @i=1
  while @i<=@seekindex
  begin
  set @index=CHARINDEX(',',@para)
  set @i=@i+1
  set @temp=SUBSTRING(@para,@index+1,LEN(@para)-@index+1)
  set @para=@temp
  end
return @temp
END
GO
if OBJECT_ID('testlog') is not null drop table testlog
create table testlog(id int,parama varchar(1000),createdate date default getdate())
insert into testlog values(1,'a,dd,cc,eee,tttt,ooo,,,d','2047-12-01')

select *,dbo.getmess(parama,3) as funValue from testlog where id=1
/*
id	parama	                    createdate	funValue
1	a,dd,cc,eee,tttt,ooo,,,d	2047-12-01	eee,tttt,ooo,,,d
*/

[二月十六]

DECLARE @str NVARCHAR(100); 
SELECT  @str = STUFF(( SELECT   ',' + parama
                       FROM     testlog
                       WHERE    id = 1
                     FOR
                       XML PATH('')
                     ), 1, 1, '');

SELECT  dbo.getmess(@str, 3);

[凡人皆侑一死]

引用 3 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('getmess') IS NOT NULL
DROP FUNCTION getmess
GO
CREATE function getmess(@para varchar(1000),@seekindex int)
returns varchar(50)
as 
begin
declare @index int,@temp varchar(1000),@i int
set @i=1
  while @i<=@seekindex
  begin
  set @index=CHARINDEX(',',@para)
  set @i=@i+1
  set @temp=SUBSTRING(@para,@index+1,LEN(@para)-@index+1)
  set @para=@temp
  end
return @temp
END
GO
if OBJECT_ID('testlog') is not null drop table testlog
create table testlog(id int,parama varchar(1000),createdate date default getdate())
insert into testlog values(1,'a,dd,cc,eee,tttt,ooo,,,d','2047-12-01')

select *,dbo.getmess(parama,3) as funValue from testlog where id=1
/*
id	parama	                    createdate	funValue
1	a,dd,cc,eee,tttt,ooo,,,d	2047-12-01	eee,tttt,ooo,,,d
*/

谢谢，看来你知道我想干嘛了