用INSERT INTO替换SELECT INTO子句

Question

下面是一个用于在商店中搜索商品的存储过程。它在SQL Server 2008中运行良好。但是当我在SQL Azure中运行这个时，我发现不支持SELECT INTO。

我决定用CREATE #Table，用INSERT INTO(Col) Values()。

问题是根据搜索条件，列的数量是动态的。下面的示例为DISTANCE列。只有当用户选择城市时才会返回。

如何使用动态SQL的CREATE TABLE和INSERT INTO ?

CREATE PROCEDURE [dbo].[SearchItems]
(
 @ShopId INT
,@unitItems INT = 20
,@sortOrder INT = 0
,@language CHAR(2) = 'EN'
,@catId INT
,@search NVARCHAR(100) = ''
,@cityId INT = 0
,@page INT
,@currentDate DATETIME2(0)
,@currentUserId INT = 0
,@itemType TINYINT = 0
,@unitRows INT OUTPUT
,@unitPages INT OUTPUT
)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
        ,@sqlSelect NVARCHAR(MAX) = ''
        ,@sqlTempTable NVARCHAR(MAX) = 'itemSearch'
        ,@sqlInto NVARCHAR(MAX) = ''
        ,@sqlFrom NVARCHAR(MAX) = ''
        ,@sqlClause NVARCHAR(MAX) = ''
        ,@sqlGroup NVARCHAR(MAX) = ''
        ,@params NVARCHAR(MAX)
        ,@sortOrderString NVARCHAR(MAX) = 'ORDER BY IT.CREATEDATE DESC'

SET @sqlSelect =
    'SELECT     
                 IT.ITEMId
                ,IT.USERId
                ,IT.ShopId
                ,IT.ITEMID
                ,IT.ITEMNAME                    
                ,IT.LASTUPDATED
                ,CY.COUNTRYNAME AS COUNTRYNAME
                ,CI.CITYNAME AS CITY
                ,ROW_NUMBER() OVER (' + @sortOrderString + ') AS RowNumber'
SET @sqlGroup =
    ' GROUP BY  IT.ITEMId
                ,IT.USERId
                ,IT.ShopId
                ,IT.ITEMID
                ,IT.ITEMNAME                    
                ,IT.LASTUPDATED                 
                ,CY.COUNTRYNAME
                ,CI.CITYNAME'
SET @sqlFrom =
    ' FROM      dbo.ITEM AS IT
    INNER JOIN  dbo.Shop AS J
            ON  IT.ShopId = J.ShopId
    INNER JOIN  dbo.CITY AS CI
            ON  CI.CITYID = J.CITYID
    INNER JOIN  dbo.COUNTRY AS CY
            ON  CI.COUNTRYISO = CY.COUNTRYISO
    INNER JOIN  dbo.REGION AS R
            ON  CI.REGIONId = R.REGIONId'
    IF (@cityId > 0)
    BEGIN
        SET @sqlFrom = @sqlFrom +
            ' INNER JOIN    dbo.CITY AS CI2
                        ON  CI2.CITYID = @cityId'       
        SET @sqlSelect = @sqlSelect +
            ',dbo.GetDistance(CI2.LATITUDE) AS DISTANCE '       
        SET @sqlGroup = @sqlGroup +
            ',CI2.LATITUDE'
    END
    SET @sqlInto = ' INTO ' + @sqlTempTable + ' ';      
    SET @sql =  @sqlSelect +
                @sqlInto +
                @sqlFrom +
                @sqlClause +
                @sqlGroup
END

任何帮助/建议吗?

Answer 1

您的临时表也需要动态创建，否则，它不在相同的上下文中，它将返回您的临时表未定义错误。基本上你要做的是:添加如下参数<>以前 declare @definetmp varchar(max) set @definetemp =' declare @tempTable table ( ItemID int, UserID int, .... ' ..... set @sqlInto = 'Insert into @tempTable' ...... set @sql = @definetemp + @sqlInto + @sqlSelect+ .... 关键是，临时表也是在动态sql中创建的。