SQL查询未获取值 [英] SQL Query not getting values

问题描述

我正在尝试使用 SP 获取值.查询如下.

I am trying get the values using SP.The query is below.

 create proc [dbo].[GetOrdersByUserID11]    
(    
@UserID int    
)    
as    
begin    
declare @status varchar(1000)

set @status=' select a.*, b.CategoryText, Cast('''' as Varchar(10)) as SectionsViewed, PurchasedDate as dateadded into #myCourses1 from dbo.Tbl_CourseInformations  a JOIN Tbl_Categories b ON a.AssignCategory = b.CategoryID '   
set @status=@status+' Join Tbl_Orders c ON c.UserID = '+convert(varchar(10),@UserID)+''

 set @status=@status+'Order By CategoryText, CourseTitle '   

print @status

exec(@status)

 select * from #myCourses1

end

This is message from my query when run my SP[[dbo].[GetOrdersByUserID11] 5085  ]:


 select a.*, b.CategoryText, Cast('' as Varchar(10)) as SectionsViewed, PurchasedDate as dateadded into #myCourses1 from dbo.Tbl_CourseInformations  a JOIN Tbl_Categories b ON a.AssignCategory = b.CategoryID  Join Tbl_Orders c ON c.UserID = 5085

(99 row(s) affected)

我收到消息 99 行受到影响，但在我的结果中只得到带有 0(零)列的标题.

i am getting message 99 rows affected but in my results getting only header with 0(zero) columns.

请帮我获取价值.

提前致谢.

推荐答案

您创建的临时表仅在 EXEC 语句的范围内，而不是外部查询.

The temp table you create is only within scope of the EXEC statements, not the outer query.

exec('select 1 as f into #t') 
select * from #t'  <-- out of scope

在语句中选择:

exec('select 1 as f into #t; select * from #t') 

或者先创建临时表(所以创建/选择在同一范围内).

Or create the temp table first (so create/select are within the same scope).

select 1 as f into #t where 0=1 --force empty
exec('insert #t values (2)') 
select * from #t

这篇关于SQL查询未获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！