Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    12

    Unanswered: Error converting data type varchar to numeric in Stored Procedure

    Here's the SP

    Code:
    USE [byrndb]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber]    Script Date: 08/28/2012 15:17:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_GetLikeJobsByJobNumber] 
    	-- Add the parameters for the stored procedure here
    	@number varchar = null
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT jobs.JobID,
    	       clients.ClientIndividualID,
    	       clients.ClientCompanyID,
    	       employees.EmployeeID,
    	       jobs.JobNumber,
    	       jobs.JobDescription AS Description,
    	       jobs.JobLocation AS Location,
    	       clients.ClientFull_name As Client,
    	       jobs.JobMemo_info As Memo,
    	       areas.Area + units.UnitName AS JobArea,
    	       employees.EmployeeLast_name + ', ' + employees.EmployeeFirst_name AS Employee
        FROM jobs
        INNER JOIN jobs2clients ON jobs.JobID = jobs2clients.JobID
        INNER JOIN clients ON jobs2clients.ClientID = clients.ClientID
        INNER JOIN clientIndividuals ON clients.ClientIndividualID = clientIndividuals.ClientIndividualID
        INNER JOIN clientCompanies ON clients.ClientCompanyID = clientCompanies.ClientCompanyID
        INNER JOIN employees ON jobs.EmployeeID = employees.EmployeeID
        LEFT OUTER JOIN areas ON jobs.JobID = areas.JobID
        LEFT OUTER JOIN units ON areas.UnitID = units.UnitID
        WHERE jobs.JobNumber LIKE '%' + @number + '%'
        ORDER BY jobs.JobNumber
    END
    Why when I run
    Code:
    USE [byrndb]
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
    		@number = N'23913'
    
    SELECT	'Return Value' = @return_value
    
    GO
    am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
    Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you take the query from the procedure, and you run it with a where clause of
    Code:
    WHERE jobs.JobNumber LIKE '%' + '23913' + '%'
    what do you get? I am suspecting that the problem is in one of the joins. You may have an ID column defined as a varchar with some bad data mixed in.

  3. #3
    Join Date
    Aug 2012
    Posts
    12
    Thanks for the reply. I tried it and the query runs fine.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In this line, are the dataypes alike, or is one numeric, and one varchar?
    Code:
    areas.Area + units.UnitName AS JobArea,

  5. #5
    Join Date
    Aug 2012
    Posts
    12
    AH HA, Area is decimal and UnitName is varchar. How can I combine them together into a string??

  6. #6
    Join Date
    Aug 2012
    Posts
    12
    I just tried cast(areas.Area AS Varchar(9)) + units.UnitName AS JobArea and the SP will run. However if I run the SP I get way more results then if I run it as a query. Any thoughts?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •