存储过程，在返回到客户端之前将结果存储在另一个表中

A stored procedure is using below query to fetch and return results to client.

存储过程正在使用下面的查询来获取结果并将结果返回给客户端。

select 
    @Lid, *         
from 
    CurrentProductSet cps 
where 
    cps.State = @state
    and cps.ProductName in (select gbb.ProductName 
                            from HMCGoodBetterBest gbb 
                            where gbb.HMC_Hospital = @hospital 
                              and gbb.HMC_Extras = @extra);

Can you please guide me how I can store these results in anther table for further use before returning them to client. Just don't want to fetch data twice or use a table variable. I have created another table 'Temp_CurrentProductSet'.

请您指导我如何将这些结果存储在另一个表格中以供进一步使用，然后再返回给客户。只是不想两次获取数据或使用表变量。我创建了另一个表“Temp_CurrentProductSet”。

Edit:

编辑:

I tried using into clause trying below code but I get this error:

我尝试使用into子句尝试下面的代码，但我得到这个错误:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

对象或列名丢失或为空。对于SELECT INTO语句，请验证每个列都有一个名称。对于其他语句，请查找空别名名称。定义为“”或[]的别名是不允许的。将别名更改为有效名称。

Code:

代码:

select 
    @Lid, * 
into 
    Temp_CurrentProductSet
from 
    CurrentProductSet cps 
where 
    cps.State = @state
    and cps.ProductName in (select gbb.ProductName 
                            from HMCGoodBetterBest gbb 
                            where gbb.HMC_Hospital = @hospital 
                              and gbb.HMC_Extras = @extra);

3 个解决方案

#1

**You need to use output clause**

insert into Temp_CurrentProductSet output Inserted.*
select 
@Lid, *         
from 
CurrentProductSet cps 
where 
cps.State = @state
and cps.ProductName in (select gbb.ProductName 
                        from HMCGoodBetterBest gbb 
                        where gbb.HMC_Hospital = @hospital 
                          and gbb.HMC_Extras = @extra);

#2

The key to your problem is in the error:

问题的关键在于错误:

An object or column name is missing or empty.

You need to define a column name for your @Lid field, something like:

您需要为您的@Lid字段定义一个列名，比如:

select @Lid as Lid, * 
    into Temp_CurrentProductSet
    from ...

Do realize, using SELECT INTO will create a new table. If you are trying to insert the values into an existing table, you need to use INSERT INTO SELECT.

请注意，使用SELECT INTO将创建一个新表。如果试图将值插入到现有表中，则需要使用insert into SELECT。

#3

As error suggests, you need to define alias name for each column name.

如错误提示，您需要为每个列名定义别名。

Try this instead,

试试这个相反,

insert into Temp_CurrentProductSet
select @Lid, *      
    from CurrentProductSet cps 
    where cps.State=@state
    and 
    cps.ProductName in (select gbb.ProductName from HMCGoodBetterBest gbb where gbb.HMC_Hospital=@hospital and gbb.HMC_Extras=@extra);

#1

**You need to use output clause**

insert into Temp_CurrentProductSet output Inserted.*
select 
@Lid, *         
from 
CurrentProductSet cps 
where 
cps.State = @state
and cps.ProductName in (select gbb.ProductName 
                        from HMCGoodBetterBest gbb 
                        where gbb.HMC_Hospital = @hospital 
                          and gbb.HMC_Extras = @extra);

#2