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,
/ j0rgeYou 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|||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|||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...|||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...|||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|||rats...

How about
SELECT @.price AS Price|||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|||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|||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