SQL2005 自定义函数

来源：互联网发布：在线模拟post请求源码编辑：程序博客网时间：2024/06/17 20:02

单表

CREATE FUNCTION get_pxlb085_zw (@pxlb CHAR(10))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @sReturnValue VARCHAR(20)

select @sReturnValue=o_text from SYS_SELECT where S_ENAME='bs_pxlb' and o_value=@pxlb order by o_order

RETURN @sReturnValue
END

带游标的查询

CREATE FUNCTION fx_get_ldps (@idVal as numeric)
RETURNS nvarchar(4000) AS
BEGIN
declare @reVal nvarchar(4000)
declare @nameVal nvarchar(100)
declare @conVal nvarchar(2000)
declare @dateVal nvarchar(50)
declare myCur cursor for select u.us_name,content,CONVERT(nvarchar(50), publish_date,20) as publish_date from SYS_FLOW_BIZ_TEXT t,sys_users u where (t.us_id=u.id and t.belongto = @idVal and t.type_tag='ldps' and t.delete_tag='0') ORDER BY t.publish_date
open myCur
    fetch next from myCur into @nameVal,@conVal,@dateVal
    set @reVal = @nameVal+'：'+@conVal
    while @@fetch_status = 0
    begin
fetch next from myCur into @nameVal,@conVal,@dateVal
if (@@fetch_status = 0)
     set @reVal =@reVal+'<br>'+@nameVal+'：'+@conVal

    end
    CLOSE myCur
    DEALLOCATE myCur
    return @reVal
END