SQL Stored Procedure varchar to numeric issue

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

Select allOpen in new window

what exactly is the issue/error?

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.

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.

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

@Shaun_Kline I would love to say I understand that but my SQL knowledge is very limited.

is the s.Zip is a numeric field ?

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

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...

if you use the operator + then you have to cast all the fields to varchar

@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

@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 :)

We do need to see the CREATE TABLE statements so we know the data types.  HainKurt is right!

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

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

Select allOpen in new window

CvCases.jpg
CvSales.jpg

@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'.