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: Today @ 9:19 AM
Points: 227, Visits: 720
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: Today @ 6:26 PM
Points: 5,618, Visits: 25,233
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 2,095, Visits: 3,146
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
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 @ 3:39 PM
Points: 37,101, Visits: 31,653
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
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: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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