Solved

Error Message in Pass-Thru Query

Posted on 2016-11-02
15
32 Views
Last Modified: 2016-11-02
I have a pass thru query that is passing parameter to a stored procedure in SQL. When I try to run the query I am getting an error that says:

"ODBC - Call Failed. Error converting data type varchar to numeric. (#8114)

This is what my query looks like with the parameters manually entered into it. Normally they are put in the query from form controls but to make it simple to post here I just put the parameters in manually. Once I fire that query I get that error message.

Execute upMySprocHistory '11/1/2016', '11/1/2016', '100987','','',''

Open in new window


Below is my stored procedure in SQL. Not sure if I have the data types set properly and which one is causing that error.

ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = '',
@FieldDateTo Varchar(50) = '',
@FieldCustNo numeric(6,0) = '',
@FieldGroupCode Varchar(50) = '',
@FieldQtyMin Varchar(50) = '',
@FieldQtyMax Varchar(50) = ''
)
AS
SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom = '') Or (v.DateCreated)>= @FieldDateFrom) AND
      ((@FieldDateTo = '') Or (v.DateCreated)<= @FieldDateTo) AND
      ((@FieldCustNo = '') Or Convert(numeric(6,0),(v.CustNo))= @FieldCustNo) AND
	  ((@FieldGroupCode = '') Or (v.GroupCode)= @FieldGroupCode) AND
	  ((@FieldQtyMin = '') Or (v.Quantity)>= @FieldQtyMin) AND
	  ((@FieldQtyMax = '') Or (v.Quantity)<= @FieldQtyMax)

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41870102
You'll need to pass the dates as 'YYYYMMDD':
Execute upMySprocHistory '20161101', '20161101', '100987','','',''

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870128
I changed it to the correct format for the dates but I am still getting the same error message.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41870133
Just for sanity check, if you provide values for FieldQtyMin and FieldQtyMax the error still occur?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870136
Yes. I just put values in for the final 3 parameters and I still get the error message.

Execute upMySprocHistory '20161101', '20161001', '100987','465','50','500'

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41870144
Ok. It might be a problem with my environment because I don't have any error.
Anyway, why do you have quantities as varchar and not as numeric?
Is v.Quantity varchar as well?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870148
I just set them all up as varchar. They should actually be numeric. I will change those to numeric. let me change those to numeric and try it again. Did it work for you with all the data types as varchar?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41870152
Yes but I'm working only with variables and not with tables or views so this may justify the different behavior.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870155
I changed those to numeric and I am still getting that error message. Is there a way to see which parameter is failing?


ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = '',
@FieldDateTo Varchar(50) = '',
@FieldCustNo numeric(6,0) = '',
@FieldGroupCode Varchar(50) = '',
@FieldQtyMin numeric = '',
@FieldQtyMax numeric = ''
)
AS
SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom = '') Or (v.DateCreated)>= @FieldDateFrom) AND
      ((@FieldDateTo = '') Or (v.DateCreated)<= @FieldDateTo) AND
      ((@FieldCustNo = '') Or Convert(numeric(6,0),(v.CustNo))= @FieldCustNo) AND
	  ((@FieldGroupCode = '') Or (v.GroupCode)= @FieldGroupCode) AND
	  ((@FieldQtyMin = '') Or Convert(numeric(18,0),(v.Quantity))>= @FieldQtyMin) AND
	  ((@FieldQtyMax = '') Or Convert(numeric(18,0),(v.Quantity))<= @FieldQtyMax)

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41870159
Numeric can't be ''. Try NULL instead. And do you allow decimal values for quantities?

FieldQtyMin numeric(6,0) = NULL
FieldQtyMax numeric(6,0) = NULL
(...)
SELECT
(...)
((@FieldQtyMin IS NULL) Or Convert(numeric(18,0),(v.Quantity))>= @FieldQtyMin) AND
  ((@FieldQtyMax IS NULL) Or Convert(numeric(18,0),(v.Quantity))<= @FieldQtyMax)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870176
Yes we do allow for decimals. I forgot about that since the majority of the time it's whole numbers.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870179
It's working now. I guess changing those to NULL did the trick.
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41870180
Yes, just realized now that you had the same mistake for the FieldCustNo:
@FieldCustNo numeric(6,0) = ''

Did you changed it also to NULL? @FieldCustNo numeric(6,0) = NULL
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870195
Yes I changed that one as well. That field doesn't have any decimals. If I change the other 2 parameters to numeric(18,2) it will always have to have 2 decimals in the value, correct?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41870200
Correct.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41870208
Thanks for all the help! I greatly appreciate it!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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