SQL Query ISNULL with convert

Posted on 2012-09-03
Last Modified: 2012-09-03

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.


Question by:J3D1-KN1G1-1t
    LVL 65

    Expert Comment

    by:Jim Horn
    Give this a whirl..

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

    Accepted Solution

    Both values in the isnull function need to evaluate to the same type.

    ISNULL(convert(varchar,itemID), 'Generic')
    LVL 65

    Assisted Solution

    by:Jim Horn
    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')
    LVL 12

    Expert Comment

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

    Open in new window


    Author Closing Comment

    thanks Dale and Jim for the answer and thorough explanation

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    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.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now