?
Solved

SQL Query ISNULL with convert

Posted on 2012-09-03
5
Medium Priority
?
669 Views
Last Modified: 2012-09-03
Hi ALL,

Hows it going?

I have a simple question, I have this this query

ISNULL(itemID, 'Generic')

Which basically gives me an error message saying that I cant convert integer to 'Generic' and I know there is a convert or something similar I can use to convert integer into varchar.

Can you please tell me how to correctly write this code.

Cheers,

J3di
0
Comment
Question by:J3D1-KN1G1-1t
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38362039
Give this a whirl..

CAST(ISNULL(itemID, 'Generic') as varchar(25))
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 1000 total points
ID: 38362041
Both values in the isnull function need to evaluate to the same type.

ISNULL(convert(varchar,itemID), 'Generic')
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38362049
dale's answer is correct.

Both return '5'

   Declare @i int = 5

   SELECT CAST(ISNULL(@i, 'Generic') as varchar(25))
   SELECT ISNULL(convert(varchar,@i), 'Generic')

The top line throws a conversion error, the bottom line works:

   Declare @i int = NULL

   SELECT CAST(ISNULL(@i, 'Generic') as varchar(25))
   SELECT ISNULL(convert(varchar,@i), 'Generic')
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38362324
SELECT ISNULL(CAST(itemID AS VARCHAR(8)), 'Generic')

Open in new window

0
 

Author Closing Comment

by:J3D1-KN1G1-1t
ID: 38362347
thanks Dale and Jim for the answer and thorough explanation
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 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