CREATE PROC [OWNER].[UDF_Test] (@where varchar(200)) AS exec('select * from T_OthA04 Where '+@where) go 和函数一样用法!
ok ! you can see from sql server BOL: CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees ed 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END
-- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO-- Example invocation SELECT * FROM fn_FindReports('11234') GO
CREATE FUNCTION [OWNER].[UDF_Test] (@where varchar(200)) RETURNS table AS RETURN exec('select * from T_OthA04 Where '+@where) go 这样写是有问题的 ----------------------- SELECT * FROM fn_FindReports('11234') GO 这种方式太不灵活了, 相当与我要先定死要对那些字段进行查询操作, 我的程序 字段是动态的,查询条件语句也是动态的. ----------------------------- 查了一些资料用过程到是可以将查询语句当参数,但怎么传出来呢请指教
函数不支持execute,可以用存储过程
参考:B. 内嵌表值函数 下例返回内嵌表值函数。USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)
create proc 名 @where varchar(8000) as exec('select * from T_OthA04 Where '+@where) go ---调用: exec 名 '1=1'
需要的是 返回的结果 怎么表示如 select * from proc 名
select * into # from 表 where 1=2 insert # exec 名
select * into # from 表 where 1=2 insert # exec 名 必须要先创建临时表吗?
是的!要不你在过程里用##全局临时表create procedure prc as select * into ##1 from tbl go--调用: exec prc select * from ##1 drop table ##1 ---别望了删掉它
问题是 我只想 用一条 sql 来完成 查询如果那样的话, 一句肯定是不能完成的
declare @strtemp nvarchar(300) declare @sql varchar(300) set @strtemp=N'select * from [table] where '+@sql sp_executesql @strtemp
(@where varchar(200))
AS
exec('select * from T_OthA04 Where '+@where)
go
和函数一样用法!
you can see from sql server BOL:
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
(@where varchar(200))
RETURNS table AS
RETURN exec('select * from T_OthA04 Where '+@where)
go
这样写是有问题的
-----------------------
SELECT *
FROM fn_FindReports('11234')
GO
这种方式太不灵活了, 相当与我要先定死要对那些字段进行查询操作, 我的程序 字段是动态的,查询条件语句也是动态的.
-----------------------------
查了一些资料用过程到是可以将查询语句当参数,但怎么传出来呢请指教
下例返回内嵌表值函数。USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
@where varchar(8000)
as
exec('select * from T_OthA04 Where '+@where)
go
---调用:
exec 名 '1=1'
select * from proc 名
insert # exec 名
insert # exec 名
必须要先创建临时表吗?
as
select * into ##1 from tbl
go--调用:
exec prc
select * from ##1
drop table ##1 ---别望了删掉它
declare @sql varchar(300)
set @strtemp=N'select * from [table] where '+@sql
sp_executesql @strtemp
insert @ values(1,2)
select * from @