Slee Slee - 3 days ago 4
SQL Question

SELECT Column AS @ParameterName in SQL Server stored procedure

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

Answer

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

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
Comments