[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Stored Procedure varchar to numeric issue

Posted on 2011-10-03
17
Medium Priority
?
260 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:k_hoffhines
  • 8
  • 3
  • 2
  • +3
17 Comments
 

Author Comment

by:k_hoffhines
ID: 36904875

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36904897
what exactly is the issue/error?
0
 

Author Comment

by:k_hoffhines
ID: 36904915
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:Shaun Kline
ID: 36904950
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 36904960
"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
 
LVL 61

Expert Comment

by:HainKurt
ID: 36904975
post the data definition for dbo.CvSales & dbo.CvCases
0
 

Author Comment

by:k_hoffhines
ID: 36904980
@Shaun_Kline I would love to say I understand that but my SQL knowledge is very limited.
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 36904985
is the s.Zip is a numeric field ?

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

Expert Comment

by:HainKurt
ID: 36905007
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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 36905008
if you use the operator + then you have to cast all the fields to varchar
0
 

Author Comment

by:k_hoffhines
ID: 36905009
@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
 

Author Comment

by:k_hoffhines
ID: 36905042
@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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36905058
We do need to see the CREATE TABLE statements so we know the data types.  HainKurt is right!
0
 

Author Comment

by:k_hoffhines
ID: 36905063
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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 36905090
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
 

Author Comment

by:k_hoffhines
ID: 36905118
@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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question