Identify string or integer

Question

Post reply

Identify string or integer

Minnu

SSCertifiable

Points: 6451
More actions
August 14, 2013 at 6:20 am

#395301

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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)

WolfgangE SSCertifiable Points: 6328 More actions · Answer 4

WolfgangE

SSCertifiable

Points: 6328

August 18, 2013 at 9:56 am

#1642501

Why do you need that at all?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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