Is it possible to run a cast function on entire table possible?

[omar999]

is it possible to perform a cast function on an entire table via sql? im basically trying to select all table contents multiply by 1.1 and then display the converted data on a webpage using sql, classic asp.

i can do a cast function on a single table column like this

Select columnname = Cast(columnname * 1.1 as int)from tablename WHERE ID ='1'

how would I apply this to an entire table? there's quite a few columns so I dont wish to do this the long way by specifying each column with a cast function over and over again.

I tried this but no joy

Select * = Cast(* * 1.1 as int)from tablename WHERE ID ='1'

thanks in advance
Omar.

[Delerna]

Then you should have designed the table so that the fields are of the correct type so that casting is not needed.

There is no way to cast all fields with a single cast statement

[omar999]

hi delerna

the table is fine as it contains uk prices. the reason I want to convert the whole table is to display converted euro prices on a different page.

trying to kill 2 birds with 1 stone hehe

for now iv used the cast function on every column in the table. the sql statement is pretty long but it works.

omar.

[nbiswas]

In such a case I would rather go with a function then.

e.g.

select dbo.MyConvertFunc(col1), Dbo.MyConvertFunc(col2).. from tablename WHERE ID ='1' 

and the MyConvertFunc will have the following implementation

CREATE FUNCTION MyConvertFunc 
(@InputVals int)
RETURNS int
AS
BEGIN
declare @convertedVal varchar(30)
set @convertedVal =  Cast(@InputVals  * 1.1 as int)
return  @convertedVal 
end

Give a thought pls.

Hope this helps

[code green]

I do currency exchange rates in my system.

I don't understand why the OP is casting prices to INT.
And we surely do not wnat '1.1' hard coded anywhere, as the exhange rate has to be dynamic.

I would modify nbiswas' function to pass an extra parameter defining the currency converting from to provide future expansion

MyConvertFunc (@InputVals int @curr VARCHAR) 
--where @curr could be 'USD', 'EUR', 'GBP' etc

[omar999]

code green I agree with you the exchange rate has to be dynamic - I will tackle this at a later stage.

nbiswasB your solution seems ideal but I have never used a function within a select statement before so I'm still a little confused..

does the function have to be saved as a stored procedure on sql server or within a sql table?

thanks in advance
Omar.

[ck9663]

If you could post some sample data and how do you want it to look like, we might be able to give you a more efficient solution.

Good Luck!!!

~~ CK

[omar999]

hey CK :)

i can perform a cast on an entire table the long winded way like this - not a good solution in my opinion as I'm performing the cast function manually on approximately every single column.

RMT2DAYGOLD = "SELECT Date_Band, Redleaf_Tour, Goldleaf_Tour, Double_Accomodation, Triple_Accomodation, Single_Accomodation, Child_Accomodation, " & _
"Double_Redleaf_Price = Cast(Double_Redleaf_Price - (Double_Redleaf_Price * 0.10) as decimal (10,0)), " & _
"Double_Goldleaf_Price = Cast(Double_Goldleaf_Price - (Double_Goldleaf_Price * 0.10) as decimal (10,0)), " & _
"Triple_Redleaf_Price = Cast(Triple_Redleaf_Price - (Triple_Redleaf_Price * 0.10) as decimal (10,0)), " & _
"Triple_Goldleaf_Price = Cast(Triple_Goldleaf_Price - (Triple_Goldleaf_Price * 0.10) as decimal (10,0)), " & _
"Single_Redleaf_Price = Cast(Single_Redleaf_Price - (Single_Redleaf_Price * 0.10) as decimal (10,0)), " & _
"Single_Goldleaf_Price = Cast(Single_Goldleaf_Price - (Single_Goldleaf_Price * 0.10) as decimal (10,0)), " & _
"Child_Redleaf_Price = Cast(Child_Redleaf_Price - (Child_Redleaf_Price * 0.10) as decimal (10,0)), " & _
"Child_Goldleaf_Price = Cast(Child_Goldleaf_Price - (Child_Goldleaf_Price * 0.10) as decimal (10,0)) " & _
"FROM RMT_2DayTours WHERE ID IN (1,2,3)"
 

ideally im looking for a cast all method

'RMT2DAYGOLD = "CAST = formula here..(SELECT * FROM RMT_2DayTours WHERE ID IN (1,2,3)"
 

nbiswas provided help but im still a bit confused if the cast all function is to be called from a stored procedure or?...

omar.

[ck9663]

I don't think I have seen a solution for what you are trying to achieve. You can, technically, cast an entire the table result set, just not the way you're thinking of...

Good Luck!!!

~~ CK

[NeoPa]

CK asked for sample data, which you didn't provide in your response. I wouldn't expect too much more clarification without first responding to the request.

[omar999]

sample data below

ID Date_Band      Redleaf_Tour  Goldleaf_Tour Double_Accomodation Triple_Accomodation Single_Accomodation Child_Accomodation Double_Redleaf_Price Double_Goldleaf_Price Triple_Redleaf_Price Triple_Goldleaf_Price Single_Redleaf_Price Single_Goldleaf_Price Child_Redleaf_Price Child_Goldleaf_Price
1  May<br />2011  Redleaf       Goldleaf      Double              Triple              Single              Child(2-11 years)  489                  989                   479                  979                   539                  1039                  419                 909
 

please advise..

[ck9663]

From the looks of it, you want to cast those numeric values and because it's too many, you don't want to type all the column names.

Analysis of your query shows that you are also performing subtraction and multiplication operation, not just conversion.

Either straight-forward conversion or mathematical operation, you cannot cast your entire table that way. Since each of those field needs to be returned separately, you need to perform the conversion operation individually.

If you're doing this for the purpose of displaying it on the front-end, why not perform the operation on the front-end instead.

Good Luck!!!

~~ CK

[omar999]

hey CK

thanks for the reply. that's correct I want to cast only the numeric values. I'm basically taking 10% off the price/value and am trying to learn a more lean sql query that what i have illustrated above.

could you please advise as to what you mean by performing the operation on the front end?

do you mean an asp function recordset?

thanks again
Omar.

[NeoPa]

Omar999:
could you please advise as to what you mean by performing the operation on the front end?

This is based on the assumption that you are using a Client/Server approach, where the Server would be the SQL Server - or Back End. Whatever software you're using on the client would be the Front End.

CK is saying just what most experienced system designers would say, which is to handle that part of your problem in the client and not burden the server with such matters that are better handled by the client.

[omar999]

hi Neopa

thanks for explaining - I understand more clearly now. I'm using classic asp on the front end.

I can only guess that I have to create some sort of classic asp function to utilize on the recordset? Am i right..?

[NeoPa]

Omar999:
Am i right..?

Yes :-D

[omar999]

ah cool. could you advise as to how this could be performed on recordset retrievel please..

would it be possible to use a function of some sort...

thanks

[NeoPa]

Omar, I'm sure if you think about it you'll realise that my response to that is logical - This is a Classic Asp question and needs to be posted in that forum. Most members that hang around in the SQL Server forum are there because they know about SQL Server. For members with Classic Asp expertise you'd need to look in that forum.

[omar999]

hi neopa

no worries I've posted in the classic asp forum. classic asp function to utilize on the recordset

thanks again
Omar.

[nbiswas]

Hi Omar,
Sorry for being too late in replying.. however, it is possible using dynamic query..

Use this program

Declare @listCol VARCHAR(2000)
Declare @tableName VARCHAR(100)
Set @tableName  = 'Table_Name' -- specify your table name here

--Step 1: Get the column names of the table dynamically , cast it to integer and --multiply by 1.1

SELECT  @listCol = (SELECT [name] +  ' = CAST([' + [name] + '] as int) * 1.1,' FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [NAME] = @tableName) FOR XML PATH(''))

--Strip out the last ,

SELECT @listCol = Left(@listCol, LEN(@listCol)-1)

--Step2: Execute the query

exec( 'SELECT ' + @listCol + ' FROM ' + @tableName)

This program assumes that the column values of the table can be converted to integer.. else you have to put a check for the same.

e.g.

Declare @t table(ID int identity, Col1 varchar(2)
insert into @t select '10'

will work as the value 10 can be converted to integer but if you have say 'xxx', it cannot be.

Hope this cleares your doubt...

Thanks

[kritika mehtani]

try this:---

select 'CAST(' + name + ' AS INT)' from sys.columns where object_id = object_id('table name')

customize acc to ur problem.