CODEWITHSUNDEEP
sql-serverpython-3.xpyodbc
Caleb Njiiri
Caleb Njiiri

Reputation: 1821

Fix Error converting data type varchar to numeric. (8114) (SQLExecDirectW)')

I am trying to run the following stored procedure using pyodbc. 

USE [CompanyMC]
GO
/****** Object:  StoredProcedure [dbo].[marketprice]    Script Date: 15/10/2019 23:02:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[marketprice](@marketvalue AS VARCHAR(30))
  AS
  BEGIN
  SELECT TRY_CAST (CompanyMarketCap AS numeric) from CompanyMC.dbo.Companies
  where CompanyName= @marketvalue
  END

Here's my python code

    # Call the procedure and pass rhe arguments as a list
    sql=f"""\
        EXEC {proc_name} @marketvalue=?;
        """
    parameters = (company_name)
    cursor.execute(sql, parameters)

    # get the result set and iterate over it to get the returned value
    result = cursor.fetchval()

I keep running into the error.

('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type varchar to numeric. (8114) (SQLExecDirectW)')

Can't seem to figure out what am doing wrong any hints would be appreciated.

Upvotes: 2

Views: 9910

Answers (1)

paulsm4
paulsm4

Reputation: 121649

Ensure you've got a legal numeric string stored in your varchar (for example, NOT "xyz", and NOT NULL). Then try CONVERT():

SELECT CONVERT(numeric, CompanyMarketCap) FROM CompanyMC.dbo.Companies WHERE CompanyName= @marketvalue

If NULLS are possible, then add a COALSCE() to your statement.

Upvotes: 1

Related Questions