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
k_hoffhinesAsked:
Who is Participating?
 
Daniel WilsonCommented:
"AppraisedValue" sounds like it should be numeric.

Perhaps
ISNULL(s.AppraisedValue, '') AS AppraisedValue

Open in new window

should say
ISNULL(s.AppraisedValue, 0) AS AppraisedValue

Open in new window

0
 
k_hoffhinesAuthor Commented:

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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what exactly is the issue/error?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
k_hoffhinesAuthor Commented:
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.

0
 
Shaun KlineLead Software EngineerCommented:
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.
0
 
HainKurtSr. System AnalystCommented:
post the data definition for dbo.CvSales & dbo.CvCases
0
 
k_hoffhinesAuthor Commented:
@Shaun_Kline I would love to say I understand that but my SQL knowledge is very limited.
0
 
Jini JoseSenior .Net DeveloperCommented:
is the s.Zip is a numeric field ?

if so try this ISNULL(cast(s.Zip as varchar), '')
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
Jini JoseSenior .Net DeveloperCommented:
if you use the operator + then you have to cast all the fields to varchar
0
 
k_hoffhinesAuthor Commented:
@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
0
 
k_hoffhinesAuthor Commented:
@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 :)
0
 
Daniel WilsonCommented:
We do need to see the CREATE TABLE statements so we know the data types.  HainKurt is right!
0
 
k_hoffhinesAuthor Commented:
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
0
 
Jini JoseSenior .Net DeveloperCommented:
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

0
 
k_hoffhinesAuthor Commented:
0
 
k_hoffhinesAuthor Commented:
@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'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.