Results 1 to 10 of 10

Thread: dynamic columns

  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: dynamic columns

    Hi all, a quicky.

    What is wrong with:

    CREATE PROCEDURE dbo.QuotePrice
    @DateFrom datetime,
    @DateTo datetime
    AS
    declare @days as int
    declare @price as smallint
    declare @daycolumn as nvarchar(6)

    set @price = 0

    set @days = DATEDIFF(day, @DateFrom, @DateTo)

    set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

    set @price = (SELECT @daycolumn FROM pasPriceTable)

    return @price
    GO


    Problem is:
    Syntax error converting the nvarchar value 'Day_10' to a column of data type smallint

    How do I set a variable to a result from a query?
    Thanks,
    / j0rge

  2. #2
    Join Date
    Dec 2003
    Posts
    7

    need to use EXEC for this...

    You can't use a variable to reference a column name...
    You need to build a string and then eval the string... see below.

    BTW, you'll want to put a where clause in to be sure you return a single value for price..

    You should look into using sp_executesql as well...

    Hope this helps...
    jzapp
    ----------------------------------------------------------------
    CREATE PROCEDURE dbo.QuotePrice
    @DateFrom datetime,
    @DateTo datetime
    AS
    declare @days as int

    declare @daycolumn as nvarchar(6)

    declare @str varchar(500)

    set @days = DATEDIFF(day, @DateFrom, @DateTo)

    set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

    SET @str = '
    declare @price as smallint
    set @price = 0
    set @price = (SELECT ' + @daycolumn + ' FROM pasPriceTable WHERE ID =1)
    return @price
    '
    EXEC(@str)

    GO

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    Re: need to use EXEC for this...

    You can't use a variable to reference a column name...
    YES YOU CAN. This works:


    CREATE PROCEDURE dbo.pasQuotePrice
    @DateFrom datetime,
    @DateTo datetime
    AS
    declare @days as int
    declare @price as smallint
    declare @daycolumn as nvarchar(6)

    set @price = 0

    set @days = DATEDIFF(day, @DateFrom, @DateTo)

    set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

    SELECT @daycolumn FROM pasPriceTable
    GO

    BUT the result gets returned as a record not as a value with return which is what I need.
    / jorge





    Originally posted by jzapp
    You can't use a variable to reference a column name...
    You need to build a string and then eval the string... see below.

    BTW, you'll want to put a where clause in to be sure you return a single value for price..

    You should look into using sp_executesql as well...

    Hope this helps...
    jzapp
    ----------------------------------------------------------------
    CREATE PROCEDURE dbo.QuotePrice
    @DateFrom datetime,
    @DateTo datetime
    AS
    declare @days as int

    declare @daycolumn as nvarchar(6)

    declare @str varchar(500)

    set @days = DATEDIFF(day, @DateFrom, @DateTo)

    set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

    SET @str = '
    declare @price as smallint
    set @price = 0
    set @price = (SELECT ' + @daycolumn + ' FROM pasPriceTable WHERE ID =1)
    return @price
    '
    EXEC(@str)

    GO

  4. #4
    Join Date
    Dec 2003
    Posts
    7
    When you select a variable such as...
    SELECT @colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
    It's equivalent to...
    SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
    Your recordset is probably...
    Day_10
    Day_10
    Day_10
    .
    .
    .

    Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

    regards...

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Weird, i swar it worked somehow before.
    Thanks.
    / jorge

    Originally posted by jzapp
    When you select a variable such as...
    SELECT @colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
    It's equivalent to...
    SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
    Your recordset is probably...
    Day_10
    Day_10
    Day_10
    .
    .
    .

    Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

    regards...

  6. #6
    Join Date
    Mar 2004
    Posts
    6
    Oh no, trouble running the EXEC version.
    A RETURN statement with a return value cannot be used in this context.
    / jorge

    Originally posted by j0rge
    Weird, i swar it worked somehow before.
    Thanks.
    / jorge

  7. #7
    Join Date
    Dec 2003
    Posts
    7
    rats...

    How about
    SELECT @price AS Price

  8. #8
    Join Date
    Mar 2004
    Posts
    6
    Nope.. same thing.

    See the problem is I need a return value and returns cannot be a part of a EXEC statement.

    BUT if there is a @@ command to get the last run query then I could:

    set @price = (select price from @@lastrunstatementrowone)
    return @price

    I've battled with this for a while now.

    Maybe there is another way to make the table.
    I have a specific amount of days which have one price for a certain period.

    that is:
    Valid from 12/2003 to 12/2004
    Day1=20
    Day2=25
    ...
    Day40=240

    There can be 'overrides' for specific dates and days.
    Valid from 04/2004 to 05/2004
    Day20=60
    Day21=60

    Any ideas? instead of running dynamic SQL?
    / jorge







    Originally posted by jzapp
    rats...

    How about
    SELECT @price AS Price

  9. #9
    Join Date
    Dec 2003
    Posts
    7
    Just off the top of my head you could do something like this....
    (I haven't tested this code)

    SET @str = '......
    set @price = 0
    set @price = (SELECT ' + @daycolumn + ' FROM pasPriceTable WHERE ID =1)

    CREATE TABLE #temptable (
    ColA [int] IDENTITY (1, 1) NOT NULL,
    ColB varchar(50) NOT NULL
    )

    INSERT INTO #temptable (ColB) VALUES ( @price )
    '

    EXEC(@str)

    declare @p varchar(20)
    SELECT @p = ColB FROM #temptable WHERE ColA = @@identity
    return @p

  10. #10
    Join Date
    Mar 2004
    Posts
    6
    This code will run A LOT. I want it to be simple, and there must be a way of querying the 'last ran statement'. NO?

    Thanks for you help.
    / jorge

    Originally posted by jzapp
    Just off the top of my head you could do something like this....
    (I haven't tested this code)

    SET @str = '......
    set @price = 0
    set @price = (SELECT ' + @daycolumn + ' FROM pasPriceTable WHERE ID =1)

    CREATE TABLE #temptable (
    ColA [int] IDENTITY (1, 1) NOT NULL,
    ColB varchar(50) NOT NULL
    )

    INSERT INTO #temptable (ColB) VALUES ( @price )
    '

    EXEC(@str)

    declare @p varchar(20)
    SELECT @p = ColB FROM #temptable WHERE ColA = @@identity
    return @p

Posting Permissions

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