alter proc p_getEmployeeInfo
@CategoryId int
AS
declare @@CategoryId int
exec LanYu_Categories_GetList @CategoryId
select * from LanYu_Products where CategoryId = @@CategoryId这个是我原本的sql 但是明显有问题我需要的是 执行 LanYu_Categories_GetList 这个存储过程 返回一个带有 CategoryId的列表
再通过获取的 CategoryId执行一个select * from Products语句
不知道怎么写。希望大哥大姐给予解答先拜谢
@CategoryId int
AS
declare @@CategoryId int
exec LanYu_Categories_GetList @CategoryId
select * from LanYu_Products where CategoryId = @@CategoryId这个是我原本的sql 但是明显有问题我需要的是 执行 LanYu_Categories_GetList 这个存储过程 返回一个带有 CategoryId的列表
再通过获取的 CategoryId执行一个select * from Products语句
不知道怎么写。希望大哥大姐给予解答先拜谢
是的
LanYu_Categories_GetList 是返回一个树形列表 其中某一列是 CategoryId列
if EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('LanYu_Products'))
drop table LanYu_Productscreate table LanYu_Products
(CategoryId int not null,CategoryNameA char(1) null,CategoryNameB char(1) null
,CategoryNameC char(1) null,CategoryNameD char(1) null)insert into LanYu_Products
select 1,'A','E','f','n' UNION ALL
select 2,'B','F','f','n' UNION ALL
select 3,'C','G','f','n' UNION ALL
select 4,'B','F','e','n' UNION ALL
select 5,'C','G','f','x' UNION ALL
select 6,'B','F','w','d' UNION ALL
select 7,'C','G','f','x'if EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('LanYu_Categories_GetList')
AND OBJECTPROPERTY(OBJECT_ID('LanYu_Categories_GetList'),'IsProcedure')=1)
drop proc LanYu_Categories_GetList
go
create proc LanYu_Categories_GetList
@CategoryId int
as
insert into #Category_temp
select * from
(select 1 as CategoryId,'A' as CategoryNameA,'E' as CategoryNameB UNION ALL
select 2,'B','F' UNION ALL
select 2,'B','F' UNION ALL
select 3,'C','G' UNION ALL
select 3,'C','G') as t where CategoryId=@CategoryId
goif EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('p_getEmployeeInfo')
AND OBJECTPROPERTY(OBJECT_ID('p_getEmployeeInfo'),'IsProcedure')=1)
drop proc p_getEmployeeInfogo
create proc p_getEmployeeInfo
@CategoryId int
AS
--创建用于存储临时数据的临时表
create table #Category_temp
(CategoryId int not null,CategoryNameA char(1) null,CategoryNameB char(1) null)
--通过传递的ID值使用存储过程LanYu_Categories_GetList,填充临时表#Category_temp
exec LanYu_Categories_GetList @CategoryId
--使用临时表的CategoryId,最终获得查询结果
select * from LanYu_Products where CategoryId in ( select CategoryId from #Category_temp)
GO--test
exec p_getEmployeeInfo 3
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, @compareprice money OUTPUT
, @listprice money OUT
AS
SELECT p.name AS Product, p.ListPrice AS 'List Price'
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO
另一个存储过程调用的时候:Create Proc Test
as
DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%Bikes%', 700,
@compareprice OUT,
@cost OUTPUT
IF @cost <= @compareprice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'
第二种方法:创建一个临时表create proc GetUserName
as
begin
select 'UserName'
endCreate table #tempTable (userName nvarchar(50))
insert into #tempTable(userName)
exec GetUserNameselect #tempTable--用完之后要把临时表清空
drop table #tempTable--需要注意的是,这种方法不能嵌套。例如: procedure a
begin
...
insert #table exec b
end
procedure b
begin
...
insert #table exec c
select * from #table
end
procedure c
begin
...
select * from sometable
end --这里a调b的结果集,而b中也有这样的应用b调了c的结果集,这是不允许的,
--会报“INSERT EXEC 语句不能嵌套”错误。在实际应用中要避免这类应用的发生。
第三种方法:声明一个变量,用exec(@sql)执行:1);EXEC 执行SQL语句declare @rsql varchar(250)
declare @csql varchar(300)
declare @rc nvarchar(500)
declare @cstucount int
declare @ccount int
set @rsql='(select Classroom_id from EA_RoomTime where zc='+@zc+' and xq='+@xq+' and T'+@time+'=''否'') and ClassroomType=''1'''
--exec(@rsql)
set @csql='select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '
set @rc=@csql+@rsql
exec sp_executesql @rc,N'@a int output,@b int output',@cstucount output,@ccount output--将exec的结果放入变量中的做法
--select @csql+@rsql
--select @cstucount本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/23/4584118.aspx
还有 谢谢
xys_777大大的补充