Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    81

    Unanswered: Dynamic name of result column in s-proc?

    How can I make this stored procedure concept work:

    -----------------------------------------------
    Create Procedure MyProc @MyValue varchar(5)
    As
    Declare @ColumnName as varchar(11)
    Set @ColumnName = 'Price_' + @MyValue
    Select Sum(Price) As @ColumnName --????
    From MyTable where Item = @MyValue
    Return
    -----------------------------------------------

    Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?

    Bjorn

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    DECLARE @ColumnName sysname, @sql varchar(8000), @myValue varchar(5)
     SELECT @myValue = '23'
        SET @ColumnName = 'Price_' + @MyValue
    SELECT @sql = 'SELECT Sum(UnitPrice) As ' + @ColumnName 
    + ' FROM  [Order Details] '
    + ' WHERE ProductID = ' + @MyValue
    
    SELECT @sql
    
    EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    81
    Thank you thank you!

    Bjorn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •