Question

下面是我写的。但是,在试图处理这一争执时,正发现这一错误:

There is already an object named #myCourses1 in the database.

因此,这在另外两处 lo。并且

create proc [dbo].[GetOrdersByUserIDwithSubscription]     
(    
    @UserID int    
)    
as    
begin    

    declare @status varchar(500)

    declare @substatus char(2)

    select @substatus=Subscribe_status from tbl_user where userid=@userid

    print @substatus

    if  @substatus= N 

    BEGIN    
        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 = @UserID and c.CourseID = a.CourseID  and c.courseprice<> subscriber   
        Order By CategoryText, CourseTitle         
    END  

    else if @substatus=  

    BEGIN    
        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 = @UserID and c.CourseID = a.CourseID and c.courseprice<> subscriber    
        Order By CategoryText, CourseTitle       
    END  

    else if @substatus= Y     
    BEGIN    
        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 = @UserID and c.CourseID = a.CourseID    
        Order By CategoryText, CourseTitle 
    END

Answer 1

在诉讼开始时就明确设定了轮值。

CREATE TABLE #myCourses1 (
    ...
)

接着写如下:

INSERT INTO #myCourses1
    select a.*, b.CategoryText, Cast(   as Varchar(10)) as SectionsViewed, PurchasedDate as dateadded 
        from dbo.Tbl_CourseInformations
        ...

Answer 2

帕塞尔正在拼凑,因为你在<编码>IF说明的不同部分使用了相同的温标名称。 IF>/code>与其它方案拟订语文没有类似的余地。

如果你不需要在<条码>(IF><>>>>>/条码>之外提及时间表,你可以通过在每一部分使用不同的表格名称来解决这一问题。

Have a look at my answer to a similar question.

Answer 3

此外,可以将你 que的风气降低到:

create proc [dbo].[GetOrdersByUserIDwithSubscription](    
    @UserID int    
)    
as    
begin
    declare @substatus char(2)

    select @substatus = Subscribe_status 
    from tbl_user 
    where userid = @userid

    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 = @UserID 
            and c.CourseID = a.CourseID 
            and (@substatus =  N  or c.courseprice <>  subscriber )
    order by CategoryText, CourseTitle         

END

Answer 4

页: 1

SELECT [Column-List] INTO #TempTable FROM [Rest-of-Query]

Sql服务器在使用这一辛塔克斯时,试图根据您的栏目清单()在飞行上设定数字。

在所储存程序开始时(如果你不需要其数据超出特殊标准范围),或将其列为永久表格。

友情链接