Identify string or integer

  • Hi Team,

    am passing a parameter to a stored procedure, i want to know whether the parameter is string or integer

    i want a print statement in stored procedure to print the parameter is string or int

    Eg :

    EXEC Stored_procedure1 ('abc')

    output : string

    EXEC Stored_procedure1 ('123')

    output : integer

    EXEC Stored_procedure1 ('abc112')

    output : string

  • I would suggest using ISNUMERIC, however it has several disadvantages that can lead one to a false conclusion.

    I would suggest that you read the following article by Jeff Moden to determine to see if it is helpful.

    http://www.sqlservercentral.com/articles/IsNumeric/71512/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • EXEC Stored_procedure1 ('abc')

    EXEC Stored_procedure1 ('123')

    EXEC Stored_procedure1 ('abc112')

    Yes. The core of the logic is this:

    CASE WHEN <string_value> LIKE '%[^0-9]%' THEN <string> ELSE <int> END

    For example:

    SELECT

    data,

    CASE WHEN data LIKE '%[^0-9]%' THEN 'string' ELSE 'int' END AS type

    FROM (

    SELECT 'abc' AS data UNION ALL

    SELECT '123' UNION ALL

    SELECT 'abc112'

    ) AS test_data

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Minnu (8/14/2013)


    Hi Team,

    am passing a parameter to a stored procedure, i want to know whether the parameter is string or integer

    i want a print statement in stored procedure to print the parameter is string or int

    Eg :

    EXEC Stored_procedure1 ('abc')

    output : string

    EXEC Stored_procedure1 ('123')

    output : integer

    EXEC Stored_procedure1 ('abc112')

    output : string

    Will you ever have negative integers?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Why do you need that at all?

  • In SQL 2008 and 2005 a simple SQLCLR function that leverages the TryParse method of the Int32 object is the most complete way to check for whether a string value can be cast to a SQL INTEGER. Here is a SQLCLR for you:

    [font="Courier New"]using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public partial class UserDefinedFunctions

    {

        [Microsoft.SqlServer.Server.SqlFunction]

        public static SqlBoolean IsInt32(SqlString Input)

        {

            if (Input.IsNull)

                return SqlBoolean.False;

            else

            {

                Int32 i;

                return Int32.TryParse(Input.Value, out i);

            }

        }

    }

    [/font]

    It is worth noting that in SQL 2012 the TRY_PARSE function was added to T-SQL language making this a trivial chore.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply