SQL Query not getting values

Question

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)

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

Plz do help me for getting values.

Thanks in advance.

Answer 1

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

Select within the statement:

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

Answer 2

Why do you build the select request in the store procedure? As I understand your request you only want the result. In this case your better use a view with parameters in the call.

CREATE VIEW [dbo].[GetOrdersByUserID11]
AS
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 

GO

And you call it like this:

SELECT * FROM GetOrdersByUserID11 Join Tbl_Orders c WHERE c.UserID = 5085

Answer 3

I really don't understand why you first put your query in a string variable. Does the query below do what you need ?

create proc [dbo].[GetOrdersByUserID11]    
(    
    @UserID int    
)    
AS    
BEGIN

SELECT a.*, b.CategoryText, Cast("" as Varchar(10)) as SectionsViewed, 
    PurchasedDate as dateadded
FROM dbo.Tbl_CourseInformations a 
    JOIN Tbl_Categories b ON a.AssignCategory = b.CategoryID
    JOIN Tbl_Orders c ON c.UserID = convert(varchar(10),@UserID)
ORDER By CategoryText, CourseTitle

END

Call stored procedure:

EXEC GetOrdersByUserID11(1234);

Answer 4

Your last statement is selecting the data into a temporary table instead of your result set which is being returned. If you add the following to the end of your procedure you should get your data:

select * from #myCourses1