Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify string or integer Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2013 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 5, 2015 5:08 AM
Points: 269, Visits: 830
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
Post #1484235
Posted Wednesday, August 14, 2013 7:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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

Before posting a performance problem please read
Post #1484266
Posted Wednesday, August 14, 2013 11:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 3,035, Visits: 4,652
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1484424
Posted Wednesday, August 14, 2013 10:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 38,341, Visits: 35,263
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1484589
Posted Sunday, August 18, 2013 9:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 28, 2015 2:20 AM
Points: 202, Visits: 765
Why do you need that at all?
Post #1485596
Posted Monday, August 19, 2013 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 7,284, Visits: 13,119
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:

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);
        
}
    }
}



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
Post #1485676
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse