一个查询的存储过程执行后返回的应该是一个结果集,有什么办法直接条件查询这个结果集呢,不用临时表和临时表变量,就是直接查询返回的结果集。
就是这样:
假设执行一个存储过程,比如 EXEC SP_SelectData
执行完之后返回查询得到的信息,比如:
A B C
-----------------
1 2 3
4 5 6
7 8 9
以上是执行完存储过程后返回的查询结果,
我的意思是,怎么条件查询以上的那个结果中的数据
比如:
查询A = 1 的数据。
不用临时表和临时表变量,因为数据字段较多时,建临时表和临时表变量都比较麻烦。
有什么方法直接条件查询返回的结果
就是这样:
假设执行一个存储过程,比如 EXEC SP_SelectData
执行完之后返回查询得到的信息,比如:
A B C
-----------------
1 2 3
4 5 6
7 8 9
以上是执行完存储过程后返回的查询结果,
我的意思是,怎么条件查询以上的那个结果中的数据
比如:
查询A = 1 的数据。
不用临时表和临时表变量,因为数据字段较多时,建临时表和临时表变量都比较麻烦。
有什么方法直接条件查询返回的结果
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
--WHERE 可以在这里再加上条件..
ORDER BY Sort;
GO
CREATE PROCEDURE xxzx
@param varchar(50)
as
declare @sql varchar(8000)
set @sql = 'select recordid'
select @sql = @sql + ' , max(case propertyname when ''' + propertyname + ''' then stringvalue else null end) [' + propertyname + ']'
from (select distinct propertyname from GCMS_PROVALUE where unitcode=@param) as a
set @sql = @sql + ' from GCMS_PROVALUE where unitcode='''+@param+''' group by recordid'
exec(@sql)GO
EXEC SP_SelectData
select * from ## where A=1
EXEC SP_SelectData
select * from ## where A=1
这个试试,说实话全局变量我都不会设啊,sql的编程基本不会
go
create table # (A int,B varchar(10), C varchar(10) )
insert # exec P_test
select * from # where A=1
@param varchar(50)
as
declare @sql varchar(8000)
set @sql = 'select recordid'
select @sql = @sql + ' , max(case propertyname when ''' + propertyname + ''' then stringvalue else null end) [' + propertyname + ']'
from (select distinct propertyname from GCMS_PROVALUE where unitcode=@param) as a
set @sql = @sql + ' from GCMS_PROVALUE where unitcode='''+@param+''' group by recordid'
exec(@sql)就医我上面这个程序来说应该怎么写,wufeng4552请指教一下,就是加几句话的事,我急用。
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=local;UID=sa;PWD=',
'EXEC SP_SelectData') AS t
where t.A=1