Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error 257 Implicit conversion from data type varchar to money

Posted on 2006-05-09
10
Medium Priority
?
560 Views
Last Modified: 2008-02-01
when I run try to run te procedure below, I get this error:
"Error 257: Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query."

===CODE START==
CREATE Procedure web_SaveBill_TEST1
@TableName varchar(100)
AS
DECLARE @locFixed varchar(200)
DECLARE @locTransit varchar(200)
DECLARE @local_transit money
DECLARE @local_fixed money

SET @locTransit = 'Select '+ @local_transit + ' =  SUM(CallCharge)) FROM ' +@TableName+ ' WHERE CalledNumber Like ''7%'''
SET @locFixed = 'Select '+ @local_fixed + ' =  SUM(CallCharge)) FROM ' +@TableName+ ' WHERE CalledNumber Not Like ''7%'''

Exec(@locTransit)
Exec(@locFixed)

INSERT INTO tb_GMCL_MonthlyBills(      
      billing_Month,
      local_transit,
      local_fixed)
VALUES(
'0707', @local_transit, @local_fixed
)
GO
===CODE END====
0
Comment
Question by:matida
  • 5
  • 5
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16640157
matida,
> SET @locTransit = 'Select '+ @local_transit + ' =  SUM(CallCharge))
> FROM ' +@TableName+ ' WHERE CalledNumber Like ''7%'''
> SET @locFixed = 'Select '+ @local_fixed + ' =  SUM(CallCharge)) FROM
> ' +@TableName+ ' WHERE CalledNumber Not Like ''7%'''



SET @locTransit = 'Select '+ @local_transit + ' = CAST( SUM(CallCharge)) as Varchar(32)) FROM ' +@TableName+ ' WHERE CalledNumber Like ''7%'''

SET @locFixed = 'Select '+ @local_fixed + ' =  CAST(SUM(CallCharge)) as Varchar(32)) FROM ' +@TableName+ ' WHERE CalledNumber Not Like ''7%'''
0
 
LVL 1

Author Comment

by:matida
ID: 16640182
the datatype in colums in SQl table is:
CallCharge = money
local_transit = money
local_fixed = money
0
 
LVL 1

Author Comment

by:matida
ID: 16640222
Aneesh..

I run it with the change sugested, but still getting this error:
Server: Msg 257, Level 16, State 3, Procedure web_SaveBill_TEST1, Line 12
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16640294
Is this what you are going to achieve

SET @locTransit = 'Select  @local_transit  =  SUM(CallCharge) FROM  ' +@TableName+ ' WHERE CalledNumber Like ''7%'''
SET @locFixed =   'Select  @local_fixed    =  SUM(CallCharge) FROM  ' +@TableName+ ' WHERE CalledNumber Not Like ''7%'''
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 800 total points
ID: 16640358
I think the following code is sufficient

DECLARE @locFixed varchar(200)
DECLARE @locTransit varchar(2000)
DECLARE @local_transit money
DECLARE @local_fixed money

SET @locTransit = 'DECLARE @local_transit money,@local_fixed money   Select  @local_transit  =  SUM(CallCharge) FROM  ' +@TableName+ ' WHERE CalledNumber Like ''7%'''
SET @locTransit = @locTransit+ ' Select  @local_fixed    =  SUM(CallCharge) FROM  ' +@TableName+ ' WHERE CalledNumber Not Like ''7%'''
SET @locTransit = @locTransit+ ' INSERT INTO tb_GMCL_MonthlyBills( billing_Month, local_transit, local_fixed) VALUES(''0707'', @local_transit, @local_fixed )'

EXEC(@locTransit)

0
 
LVL 1

Author Comment

by:matida
ID: 16640364
I tried this method beofre, and tried also just now, but error is generated :

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@local_transit'.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@local_fixed'.

My only concern here, to use a dynamic table name. All other values constant except table that.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16640455
did u try my last post
0
 
LVL 1

Author Comment

by:matida
ID: 16640511
already close connections to server, seems so far ok...stepping out of office, will try it  in few hours time, let u know the results....
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16640537
ok, i am also planning to leave office
0
 
LVL 1

Author Comment

by:matida
ID: 16647009
hi Aneesh!

It works fine...thanks

ps/
if using MSN my screenname: <kassama  {remove space } @hotmail.com>
           Skype: <gassamaba>
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question