Avatar of k_hoffhines
k_hoffhines
Flag for United States of America asked on

SQL Stored Procedure varchar to numeric issue

I am having an issue with a stored procedure i created for a site not sure where the issue is at as I am not that best with SQL server
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
k_hoffhines

8/22/2022 - Mon
k_hoffhines

ASKER

USE [ETIDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[MCSO_Sheriff_Sale_Details]     
		@CurrentSale int
AS

SELECT DISTINCT 

ISNULL(c.DFirstName, '') + ' ' + ISNULL(c.DMidName, '') + ' ' + ISNULL(c.DefendantName, '') AS SaleName, 
			
ISNULL(s.AppraisedValue, '') AS AppraisedValue, 
				
ISNULL(s.PropStNo, '') + ' ' + ISNULL(s.PropStName, '') + ' ' + ISNULL(s.PropStSfx, '') + ' ' + 

ISNULL(s.City, '') + ' ' + ISNULL(s.StatePrefix, '') + ' ' + ISNULL(s.Zip, '') AS SaleAddress, 
			
ISNULL(c.SaleDT, '') AS SaleDT, 

ISNULL(c.PlaintiffName, '') AS PlaintiffName, 

ISNULL(c.CaseNumber, '') AS CaseNumber, 

ISNULL(c.PAttorneyName, '') AS PAttorneyName	


FROM ETIDB.dbo.CvSales s
LEFT OUTER JOIN ETIDB.dbo.CvCases c
ON (s.YearOf = c.YearOf AND s.Docket = c.Docket)



WHERE c.Docket = @CurrentSale

Open in new window

Guy Hengel [angelIII / a3]

what exactly is the issue/error?
k_hoffhines

ASKER
sorry forgot the MAIN PART :) thanks

Msg 8114, Level 16, State 5, Procedure MCSO_Sheriff_Sale_Details, Line 5
Error converting data type varchar to numeric.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Shaun Kline

I would guess that one of the fields in your query is defined as numeric and allows nulls. When you use the ISNULL function, the result would need to be in the same format as the non-null values. If you are using the ISNULL function on dates, your best option is to force the date value into a string using CAST or CONVERT. The same can be said for numerics.
ASKER CERTIFIED SOLUTION
Daniel Wilson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
HainKurt

post the data definition for dbo.CvSales & dbo.CvCases
k_hoffhines

ASKER
@Shaun_Kline I would love to say I understand that but my SQL knowledge is very limited.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jini Jose

is the s.Zip is a numeric field ?

if so try this ISNULL(cast(s.Zip as varchar), '')
HainKurt

why do you convertt all nulls to empty strings? any reason?

ISNULL(c.SaleDT, '') AS SaleDT,
ISNULL(c.CaseNumber, '') AS CaseNumber,

-->

c.SaleDT,
c.CaseNumber,

your app should handle NULL appropriately... otherwise you shoukld cast all values to string

ISNULL(cast(c.SaleDT as varchar), '') AS SaleDT,
ISNULL(cast(c.CaseNumber as varchar), '') AS CaseNumber,

which something I do not recommend...
Jini Jose

if you use the operator + then you have to cast all the fields to varchar
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
k_hoffhines

ASKER
@DanielWilson that fixed my sql query!! thanks a lot for the help on that!! Now I am getting an issue with the website side of things that I am running the event log states the following error

Error converting data type nvarchar to int.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Mercer_County_Sheriff_Sales.SaleDetails.Page_Load(Object sender, EventArgs e) in C:\Websites\Sheriff Sale Search\SaleDetails.aspx.cs:line 41
k_hoffhines

ASKER
@HainKurt - that is how we did our inmate search and warrants search so just creating is the same way if that makes sense I can't honestly tell you why we did it that way. Because I forget it was about 3-4 years ago ;) I am willing to change things that part don't bother me at all :)
Daniel Wilson

We do need to see the CREATE TABLE statements so we know the data types.  HainKurt is right!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
k_hoffhines

ASKER
Also, sorry for all the questions!!

but, how would I combine in my query to column's of data for the @currentsale part that would resolve one of my issues with this site a lot to figure out how to separate thing a little bit better. that could be part's of my errors
Jini Jose

try this

 
USE [ETIDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[MCSO_Sheriff_Sale_Details]     
                @CurrentSale int
AS

SELECT DISTINCT 

ISNULL(cast(c.DFirstName as varchar), '') + ' ' + ISNULL(cast(c.DMidName as varchar), '') + ' ' + ISNULL(cast(c.DefendantName as varchar), '') AS SaleName, 
                        
ISNULL(cast(s.AppraisedValue as varchar), '') AS AppraisedValue, 
                                
ISNULL(cast(s.PropStNo as varchar), '') + ' ' + ISNULL(s.PropStName as varchar), '') + ' ' + ISNULL(s.PropStSfx as varchar), '') + ' ' + 

ISNULL(cast(s.City as varchar), '') + ' ' + ISNULL(cast(s.StatePrefix as varchar), '') + ' ' + ISNULL(cast(s.Zip as varchar), '') AS SaleAddress, 
                        
ISNULL(cast(c.SaleDT as varchar), '') AS SaleDT, 

ISNULL(cast(c.PlaintiffName as varchar), '') AS PlaintiffName, 

ISNULL(cast(c.CaseNumber as varchar), '') AS CaseNumber, 

ISNULL(cast(c.PAttorneyName as varchar), '') AS PAttorneyName    


FROM ETIDB.dbo.CvSales s
LEFT OUTER JOIN ETIDB.dbo.CvCases c
ON (s.YearOf = c.YearOf AND s.Docket = c.Docket)



WHERE c.Docket = @CurrentSale

Open in new window

k_hoffhines

ASKER
Your help has saved me hundreds of hours of internet surfing.
fblack61
k_hoffhines

ASKER
@gmailjini   when running your;s this is what i get

Msg 156, Level 15, State 10, Procedure MCSO_Sheriff_Sale_Details, Line 11
Incorrect syntax near the keyword 'AS'.