有一个SQL2000函数 我想返回两个值 然后在一句查询语句中调用,请问如何返回和调用 下面是函数create function dbo.tel_merger(@id varchar) returns varchar(8000)
as
begin
declare @str_tel varchar(8000)
declare @str_id varchar(8000)
set @str_tel = ''
set @str_id = ''
select top 600 @str_tel = @str_tel + ',' + cast(sendtel as varchar),@str_id = @str_id + ',' + cast(id as varchar) from sms_sendhistory where ph = @id and state=0
set @str_tel = right(@str_tel , len(@str_tel) - 1)
set @str_id = right(@str_id , len(@str_id) - 1)
return @str_tel
return @str_id
end
go下面是调用语句,但只能调用其中一个,如何同时调用两个值啊?
select ph ,sendtel= dbo.tel_merger(ph) from sms_sendhistory group by ph
比如
select ph ,sendtel= dbo.tel_merger(ph),id=.... from sms_sendhistory group by ph
as
begin
declare @str_tel varchar(8000)
declare @str_id varchar(8000)
set @str_tel = ''
set @str_id = ''
select top 600 @str_tel = @str_tel + ',' + cast(sendtel as varchar),@str_id = @str_id + ',' + cast(id as varchar) from sms_sendhistory where ph = @id and state=0
set @str_tel = right(@str_tel , len(@str_tel) - 1)
set @str_id = right(@str_id , len(@str_id) - 1)
return @str_tel
return @str_id
end
go下面是调用语句,但只能调用其中一个,如何同时调用两个值啊?
select ph ,sendtel= dbo.tel_merger(ph) from sms_sendhistory group by ph
比如
select ph ,sendtel= dbo.tel_merger(ph),id=.... from sms_sendhistory group by ph
解决方案 »
- MSDE2000的登陆问题
- 为什么我这段SQL语法结果是0
- 请问这样的配置插入一条数据得多长时间??
- 又遇到一个郁闷问题,解决了接贴!
- 关于一个SQL查询语句的问题
- 求救啊`~那位好心的大哥帮我写句SQL语句啊~小弟谢了啊~
- 最近我的邮箱地址不知被谁(也许是病毒)拿去发了许多带有病毒的邮件,我决定不再使用我现有的邮箱
- 怎么判断数据库中字段的位数是多少位?
- SQLite数据库下按周查询数据库的命令求助
- SQL 的重复值问题
- SQL SERVER2005 连接实例的服务器名称写法.\MSSQLSERVER为什么不行?
- 不支持此服务器版本,必须安装Ms Sql Server 2005 或者更高版本。
应该只返回第一个的值 后面的是非法的吧
或者函数可以返回varchar(max)类型么?
RETURNS @tb TABLE(id int,sendtel varchar)
as
begin
declare @str_tel varchar(8000)
declare @str_id varchar(8000) set @str_tel = ''
set @str_id = ''
select top 600 @str_id = @str_id + ',' + cast(id as varchar), @str_tel = @str_tel + ',' + cast(sendtel as varchar) from sms_sendhistory where state=0
set @str_tel = right(@str_tel , len(@str_tel) - 1)
set @str_id = right(@str_id , len(@str_id) - 1)
return
end
goselect * from dbo.tel_merger_tb()
RETURNS @tb TABLE(id varchar(8000),sendtel varchar(8000))
as
begin
declare @str_tel varchar(8000)
declare @str_id varchar(8000) set @str_tel = ''
set @str_id = ''
select top 600 @str_id = @str_id + ',' + cast(id as varchar), @str_tel = @str_tel + ',' + cast(sendtel as varchar) from sms_sendhistory where state=0
set @str_tel = right(@str_tel , len(@str_tel) - 1)
set @str_id = right(@str_id , len(@str_id) - 1)
INSERT INTO @tb
SELECT @str_tel,@str_id
return
end
go
select ph ,
sendtel =
(
select top 600 ',' + cast(sendtel as varchar)
from sms_sendhistory where ph = t.ph and state=0
)
,str_id=
(
select top 600 ',' + cast(id as varchar)
from sms_sendhistory where ph = t.ph and state=0
)
from sms_sendhistory t group by ph
RETURNS TABLE
as
begin
declare @str_tel varchar(8000)
declare @str_id varchar(8000) set @str_tel = ''
set @str_id = ''
select top 600 @str_id = @str_id + ',' + cast(id as varchar), @str_tel = @str_tel + ',' + cast(sendtel as varchar) from sms_sendhistory where state=0
set @str_tel = right(@str_tel , len(@str_tel) - 1)
set @str_id = right(@str_id , len(@str_id) - 1)
SELECT @str_tel as tel,@str_id as ids
return
endgo
select t.*,t1.tel,t1.ids
(
select ph from sms_sendhistory group by ph
)t,dbo.tel_merger_tb() t1
go