Saving result from sp_executesql into a variable and using dynamic column name - getting error "Error converting data type varchar to numeric"

Advertisement
October 11

Im getting an error when running a procedure that includes this code.
I need to select from a dynamic column name and save the result in a variable, but seem to be having trouble with the values being fed to sp_executesql
DECLARE @retval AS DECIMAL(12,2)
DECLARE @MonthVal VARCHAR(20), @SpreadKeyVal INT
DECLARE @sqlcmd AS NVARCHAR(150)
DECLARE @paramdef NVARCHAR(150)
SET @MonthVal = 'Month' + CAST(@MonthNumber AS VARCHAR(2) );
SET @SpreadKeyVal = @SpreadKey; --CAST(@SpreadKey AS VARCHAR(10) );
SET @sqlcmd = N' SELECT @retvalout = @MonthVal FROM dbo.CourseSpread WHERE CourseSpreadId = @SpreadKeyVal';
SET @paramdef = N'@MonthVal VARCHAR(20), @SpreadKeyVal INT, @retvalout DECIMAL(12,2) OUTPUT'
--default
SET @retval = 0.0;
EXECUTE sys.sp_executesql @sqlcmd,@paramdef, @MonthVal = 'Month4',@SpreadKeyVal = 1, @retvalout = @retval OUTPUT;
SELECT @retval
DECLARE @return_value DECIMAL(12,2)
EXEC @return_value = [dbo].[GetSpreadValueByMonthNumber]
@SpreadKey = 1,
@MonthNumber = 4
SELECT 'Return Value' = @return_value
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Advertisement

Replay

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need
to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you need to read and download the PDF for: 
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/
>> I need to select from a dynamic column name and save the result in a variable, but seem to be having trouble with the values being fed to sp_executesql <<
This is so very, very wrong! A column is an attribute of an entity. The idea that you are so screwed up that you have no idea if you want
the shoe size, the phone number or something else at run time of this entity. 
In Software Engineering we have a principle called cohesion that says a model should do one and only one task, have one and only one entry point, and one and only one exit point. 
Hey, on a scale from 1 to 10, what color is your favorite letter of the alphabet? Yes, your mindset is that level of sillyity and absurdity. 
Do you know that SQL is a declarative language? This family of languages does not use local variables! 
Now think about “month_val” and what it means. A month is a temporal unit of measurement, so this is as silly as saying “liter_val” in your code. Why did you use “sp_” on a procedure? It has special meaning in T-SQL.  
Think about how silly this is: 
 SET @month_val = 'Month' + CAST(@month_nbr AS VARCHAR(2));
We do not do display formatting in a query. This is a violation of at the tiered architecture principle. We have a presentation layer. But more than that, the INTERVAL temporal data type is a {year-month} and never just a month. This is fundamental. 
We need to see the DDL so we can re-write this mess. Want to fix it or not?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking
in Sets / Trees and Hierarchies in SQL

View 6 Replies

HT4623 When I plug my new iphone 5 into my computer and click on itunes, I get a message that Itunes 10.7 is required on my phone. How do I update my iphone5 with itunes 10.7?

November 30

When I plug my new iphone 5 into my computer and click on itunes, I get a message that Itunes 10.7 is required on my phone. How do I update my iphone5 with itunes 10.7?Thanks.  My computer is updated.  When I plug my iphone in, it does not pick up my

View All Replies

My MacBook Pro gets very slow while doing  work like watching movies and using net .. video gets lag very often

October 11

My MacBook Pro gets very slow while doing  work like watching movies and using net .. video gets lag very often and while surffing on net it gets very slow ...First, back up all data immediately, as your boot drive might be failing. There are a few o

View All Replies

I have a Canon MX882 and more often than note get an error message saying the printer is not connected. I check the printer and the get the message that the access point is accessed. I have reloaded the software and it worked for awhile. Any ideas?

October 11

I have a Canon MX882 printer and more often than note get an error message that says "Printer not Connected." When I check the printer itself, I get confirmation that the access point is connected. I tried reloading the software and that seemed

View All Replies

How do I export my iMovie '11 video and burn it to a DVD? I've tried 'Share' - 'Media Browser', 'iDVD', and 'Export Movie' but always get an error message indicating that my movie is too long to publish. Thoughts?

November 30

How do I export my iMovie '11 video and burn it to a DVD? I've tried 'Share' - 'Media Browser', 'iDVD', and 'Export Movie' but always get an error message indicating that my movie is too long to publish. Thoughts? Additionally, what does 'File' 'Fina

View All Replies

I would like to update my Adobe ID and main email - I'm getting an error when trying to do this in my account settings. Any tips?

November 30

I would like to update my Adobe ID and main email - I'm getting an error when trying to do this in my account settings. Any tips?Change/Verify Account https://forums.adobe.com/thread/1465499 may help -http://helpx.adobe.com/x-productkb/policy-pricing

View All Replies

I'm trying to extract audio files from my OLYMPUS Digital Voice Recorder VN-6200PC I am getting this error whenever I try to play its file type on my MacBook Pro,"The document "VN622195.WMA" could not be opened. The movie's file format isn't recognized."

November 30

I'm trying to extract audio files from my OLYMPUS Digital Voice Recorder VN-6200PC I am getting this error whenever I try to play its file type on my MacBook Pro,"The document "VN622195.WMA" could not be opened. The movie's file format isn'

View All Replies

I cannot verify my Apple ID. I keep getting the error that the email is in use by another user. I can log into the store and use Itunes without issue. But I cannot manage my Id. Any suggestions?

October 11

When I try to log into Apple ID I get this message. I have not recieved a verifcation email and nothing is recieved when I resend it. The email address is correct and I use it daily. After I get this error message I can proceed no further with managi

View All Replies

Use variable in SQL for column name

October 11

Hi All, We want to use a user input as a column name in APEX. For e.g user will enter "ALLOWABLE_AMOUNT" then the query will be as follows  : select Rule,rule_name,rule_desc,"User Input" from rule_dim where "User_input" >

View All Replies

Ever since I downloaded Firefox 4 (and now Firefox 5), I get two error messages when the auto-update tries to run.

November 30

The two messages are as follows: "Something is preventing Firefox from updating securely. Please make sure that you have the latest version of Firefox from: http://www.firefox.com " and the second message, "Something is trying to trick Fire

View All Replies

After itunes and q/time upgrade I get an error #63440

November 30

after the itunes and q/time upgrade whenever I try to acsess anything via q/time i get an error #63440 quick time was not found even though it is on my machine and I have removed and re-installed.OK, here's something else to try (with thanks to B Noi

View All Replies

Error While creating Data type from a XSD

October 11

Hi, I am trying to import a XSD into my Data type. I have changed the namespace in the XSD to match that of the target namespace. I am getting the following error. Global definition Element: http://ls.sbc.com/OMS/ProcessSalesOrderEFE004,   orderReque

View All Replies

Deleting from multiple tables where few tables have same column name

October 11

Hi, I am new to PL/SQL and need some help. I need to delete data older then X years from some 35 odd tables in my schema and out of those tables 25 tables have same column name on which i can have my "where" clause and rest 10 table have differe

View All Replies

How to Select data using same column name from 3 remote database

November 30

Hi, Can anyone help me on how to get data with same column names from 3 remote database and a single alias. Ex. SELECT * a.name, b.status, SUM(b.qty) qantity, MAX(b.date) date_as_of FROM *(([email protected], [email protected], [email protected]

View All Replies

I used to be able to print stuff like coupons or patterns from websites but now for the last few months I get an error code 30 message. I have added nothing new, same printer, etc but I have let Firefox update. How do I fix this so I can print again?

October 11

I used to be able to go to a website and click their print button and it would print just fine. The only programs I will always update when they are available are Firefox, Avast Free Anti virus, and Malwarebytes. I think I may have updated Java too b

View All Replies

Need help on read CLOB and use in query to get the values from it

October 11

Hi Gurus, Hope you all are doing great!. I have a small doubt on CLOB reading and not able to do it in the query because it has very large strings in it. {code} create table TEST   col1 VARCHAR2(100),   col2 VARCHAR2(100),   col3 VARCHAR2(100),   col

View All Replies

When i click wi-fi i only have my neighbours network to chose from if i click other and put in network name/security ect it says unable to connect,i have ipod  touches that go on-line no problems

November 30

i have an ipad 2,it never stays connected most of the time in chose a network only my neighbours is shown,if i chose other and put my network in plus security ect it keeps saying not available,i have 2 ipod touches and they can get on-line so my rout

View All Replies

Import of docs into new pages and iCloud-pages header / footer get lost

October 11

Hi, after updating to pages 5.0 I cannot use my old documents: these are not importes / opened witheir respective headers / footers. The same happens when importing "old" docs into icloud-pages. AlexanderHi, after updating to pages 5.0 I cannot

View All Replies

Someone hacked into my account and used up my credit of $50

November 30

I received an email that someone made a purchase using a device that was not associated with my account.  Then I see that someone bought a game for $49.99, using up my $50  credit from a gift card.  I changed my password. Do I need to do something mo

View All Replies

Replication of masterdata from CRM 2005 to ECC and using filter

October 11

Hi all. My client have a certain account group configurated in crm.  business partners for this account are automatically entered in the CRM system via upload. This business partners should NOT be replicated to ECC. I thought this was a peace of cake

View All Replies

Tags:

  1. sims resource wont download pdf files
  2. Free youtube settings sa nokia503
  3. illustrator cs3 initialization crash
  4. breakpoint reached on Lenovo G 50
  5. iphoto 8.1.2 hanging delete plist
  6. nokia n81 stuck on nokia logo
  7. me22n save enhancement
  8. ME_PROCESS_PO_CUST BAPI_PO_CREATE1
  9. pr to po rounding
  10. SAP fm view maintian
Copyrights 2019 Fcffair BigData Resource, All rights reserved