SQL Server存储过程中的SELECT列AS @ParameterName

I have a stored procedure and I need to pass in a column alias as a parameter, how can I make this work?

我有一个存储过程,我需要传入列别名作为参数,我该如何使其工作?

This is the line of the stored procedure giving me trouble:

这是存储过程的一行给我带来麻烦:

ManufacturerPriceListQty.Price As @PriceLevelAlias

and here is the stored procedure:

这是存储过程:

ALTER PROCEDURE [dbo].[Export_Products]
    @PriceLevelAlias AS VARCHAR(25),
    @PriceListCodes AS VARCHAR(250) --Exmaple: 'Des', 'Designer', 'Non-Stocking', 'NonStocking'
AS
BEGIN
    SET NOCOUNT ON;
    --PRINT @PriceListCodes
    --SELECT * FROM dbo.Split(@PriceListCodes,',')
    -- Insert statements for procedure here

    SELECT  
        CAST(p.ManufacturerID as varchar(2))+'-'+p.ProductNumber  AS ItemID,
        SUBSTRING(p.ProductName,0,100) as ItemName,
        p.ProductName AS [Description],
        ManufacturerPriceListQty.Price As @PriceLevelAlias,
        ManufacturerPriceListQty.Qty as OnHandQuantity,
        ManufacturerPriceListQty.MultipleQty as OrderMinimumQuantity,
        ManufacturerPriceListQty.MultipleQty as OrderMultipleQuantity,
        Manufacturer.CompanyName AS CatalogName,
        Manufacturer.CompanyName AS CatalogCode,
        p.ProductNumber as UDF1,
        CAST(p.ManufacturerID as varchar(2)) AS UDF2,
        '%'+CAST(p.ProductID as varchar(10)) as UDF5,
        CASE 
           WHEN P.Active ='1' THEN 'FALSE'  
           ELSE 'TRUE'  
        END AS IsDeleted,
        @PriceLevelAlias AS PriceLevel,
        ManufacturerPriceList.PriceListCode,
        ManufacturerPriceListProduct.PriceListID
    FROM         
        ManufacturerPriceListProduct
    INNER JOIN
        ManufacturerPriceList ON ManufacturerPriceListProduct.PriceListID = ManufacturerPriceList.PriceListID 
    INNER JOIN
        Manufacturer ON ManufacturerPriceList.ManufacturerID = Manufacturer.ManufacturerID 
    INNER JOIN
        ManufacturerPriceListQty ON ManufacturerPriceListProduct.PriceListProductID = ManufacturerPriceListQty.PriceListProductID 
    INNER JOIN
        Product p ON ManufacturerPriceListProduct.ProductID = p.ProductID
    WHERE     
        (Manufacturer.Active = 1)
        AND p.Discontinued = 0
        AND PriceListCode IN (SELECT * FROM dbo.Split(@PriceListCodes, ',')) 
END

3 个解决方案

#1

instead of the direct script, try using the final select statement as Query string. Try the below :

而不是直接脚本,尝试使用最终的select语句作为查询字符串。试试以下内容:

ALTER PROCEDURE [dbo].[Export_Products]
    @PriceLevelAlias AS VARCHAR(25),
    @PriceListCodes AS VARCHAR(250) --Exmaple: 'Des', 'Designer', 'Non-Stocking', 'NonStocking'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    --PRINT @PriceListCodes
    --SELECT * FROM dbo.Split(@PriceListCodes,',')
        -- Insert statements for procedure here
    DECLARE @v_Qry VARCHAR(MAX)

    SELECT
        @v_Qry = '
                SELECT  
                CAST(p.ManufacturerID as varchar(2))+''-''+p.ProductNumber  AS ItemID
                ,SUBSTRING(p.ProductName,0,100) as ItemName
                ,p.ProductName AS [Description]
                ,ManufacturerPriceListQty.Price As '+@PriceLevelAlias+'
                ,ManufacturerPriceListQty.Qty as OnHandQuantity
                ,ManufacturerPriceListQty.MultipleQty as OrderMinimumQuantity
                ,ManufacturerPriceListQty.MultipleQty as OrderMultipleQuantity
                ,Manufacturer.CompanyName AS CatalogName
                ,Manufacturer.CompanyName AS CatalogCode
                ,p.ProductNumber as UDF1
                ,CAST(p.ManufacturerID as varchar(2)) AS UDF2
                ,''%''+CAST(p.ProductID as varchar(10)) as UDF5
                ,CASE 
                    WHEN P.Active =''1'' THEN ''FALSE'' 
                    ELSE ''TRUE'' 
                END AS IsDeleted
                ,@PriceLevelAlias AS PriceLevel
                ,ManufacturerPriceList.PriceListCode
                ,ManufacturerPriceListProduct.PriceListID
                FROM         ManufacturerPriceListProduct INNER JOIN
                                      ManufacturerPriceList ON ManufacturerPriceListProduct.PriceListID = ManufacturerPriceList.PriceListID INNER JOIN
                                      Manufacturer ON ManufacturerPriceList.ManufacturerID = Manufacturer.ManufacturerID INNER JOIN
                                      ManufacturerPriceListQty ON ManufacturerPriceListProduct.PriceListProductID = ManufacturerPriceListQty.PriceListProductID INNER JOIN
                                      Product p ON ManufacturerPriceListProduct.ProductID = p.ProductID
                WHERE     (Manufacturer.Active = 1)
                AND p.Discontinued=0
                AND PriceListCode 
                IN(SELECT * FROM dbo.Split(@PriceListCodes,'',''))'


    EXEC(@v_Qry)

END

#2

It is not possible to set a column's alias dynamically. The only way to achieve this, is to create the full statement dynamically (there is an answer already).

无法动态设置列的别名。实现这一目标的唯一方法是动态创建完整语句(已有答案)。

With a closed set of possible values you could use a structure with IF to define your statement in all possible combinations.

使用一组封闭的可能值,您可以使用带有IF的结构来定义所有可能组合的语句。

The following might help you, if you have a closed set of values and the calling code knows, which column name is to be used.

如果您有一组封闭的值并且调用代码知道要使用哪个列名,以下内容可能会对您有所帮助。

As your SP is returning a result set, you might include the same value with all possible names. Look at this example

当您的SP返回结果集时,您可能包含所有可能名称的相同值。看看这个例子

SELECT *
      ,type_desc AS Name1 
      ,type_desc AS Name2 
      ,type_desc AS Name3
      --add all possible types here
FROM sys.objects

Which column you use after the call, will then be the decision of the calling code...

您在调用后使用哪一列,将是调用代码的决定...

#3

There were two other variables that need to be taken outside of the string:

在字符串之外还需要另外两个变量:

`ALTER PROCEDURE [dbo].[Export_Products] @PriceLevelAlias AS VARCHAR(25), @PriceListCodes AS VARCHAR(250) --Exmaple: 'Des', 'Designer', 'Non-Stocking', 'NonStocking' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --PRINT @PriceListCodes --SELECT * FROM dbo.Split(@PriceListCodes,',') -- Insert statements for procedure here DECLARE @v_Qry VARCHAR(MAX)

`ALTER PROCEDURE [dbo]。[Export_Products] @PriceLevelAlias AS VARCHAR(25),@ PriceListCodes AS VARCHAR(250) - 例子:'Des','Designer','Non-Stocking','NonStocking'AS BEGIN - 添加了SET NOCOUNT ON以防止额外的结果集 - 干扰SELECT语句。 SET NOCOUNT ON; --PRINT @PriceListCodes --SELECT * FROM dbo.Split(@PriceListCodes,',') - 在此处插入过程语句DECLARE @v_Qry VARCHAR(MAX)

SELECT
    @v_Qry = '
            SELECT  
            CAST(p.ManufacturerID as varchar(2))+''-''+p.ProductNumber  AS ItemID
            ,SUBSTRING(p.ProductName,0,100) as ItemName
            ,p.ProductName AS [Description]
            ,ManufacturerPriceListQty.Price As '+@PriceLevelAlias+'
            ,ManufacturerPriceListQty.Qty as OnHandQuantity
            ,ManufacturerPriceListQty.MultipleQty as OrderMinimumQuantity
            ,ManufacturerPriceListQty.MultipleQty as OrderMultipleQuantity
            ,Manufacturer.CompanyName AS CatalogName
            ,Manufacturer.CompanyName AS CatalogCode
            ,p.ProductNumber as UDF1
            ,CAST(p.ManufacturerID as varchar(2)) AS UDF2
            ,''%''+CAST(p.ProductID as varchar(10)) as UDF5
            ,CASE 
                WHEN P.Active =''1'' THEN ''FALSE'' 
                ELSE ''TRUE'' 
            END AS IsDeleted
            ,''' + @PriceLevelAlias + ''' AS PriceLevel
            ,ManufacturerPriceList.PriceListCode
            ,ManufacturerPriceListProduct.PriceListID
            FROM         ManufacturerPriceListProduct INNER JOIN
                                  ManufacturerPriceList ON ManufacturerPriceListProduct.PriceListID = ManufacturerPriceList.PriceListID INNER JOIN
                                  Manufacturer ON ManufacturerPriceList.ManufacturerID = Manufacturer.ManufacturerID INNER JOIN
                                  ManufacturerPriceListQty ON ManufacturerPriceListProduct.PriceListProductID = ManufacturerPriceListQty.PriceListProductID INNER JOIN
                                  Product p ON ManufacturerPriceListProduct.ProductID = p.ProductID
            WHERE     (Manufacturer.Active = 1)
            AND p.Discontinued=0
            AND PriceListCode 
            IN(SELECT * FROM dbo.Split(''' + @PriceListCodes + ''','',''))'


EXEC(@v_Qry)

END`