Archive

Archive for July, 2015

Pass an array of multiple values with a single Parameter in a Stored Procedure – SQL Server

July 10, 2015 3 comments

This post comes from an old discussion on MSDN T-SQL Forum[link], where someone asked about the same topic.

Check my reply there and here with this post I’m going to collate all the different methods I’ve blog previously that can be used for the same purpose.
 

Stored Procedures accept only a fixed and pre-defined number of parameters thus there is a limitation you cannot provide dynamic number of params, like you can do with other languages, like C, C#, Java, etc. In these languages there is concept of Arrays which you can pass in a method/function, but in SQL there are no array variables and it does not have any datatype that support arrays. Thus if you have to provide multiple values to any parameter you cannot do it directly, though there are some workarounds.
 

–> Here are some of the methods or workarounds by which we can pass multiple values as a single Parameter in a Stored Procedure or a Function:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using user-defined table types. These TVPs can then be used to send multiple rows of data to SPs or UDFs, without creating a temp table or multiple parameters, check here.

Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.
 

Thanks a lot, please provide your valuable comments and suggestions on this topic.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check & Like my FB Page


Bug with ISNUMERIC() – MSDN TSQL forum

July 8, 2015 2 comments

–> Question:

Has anyone seen this SQL bug before?

If you use the IsNumeric function with 12345 and replace number 3 with an “e” and no other letter, SQL still thinks it’s numeric.

If ISNUMERIC('12e45') = 1
print 'Is Numeric'
else 
Print 'No'

 

–> My Answer:

Yes, “12e45” is still numeric, notice the small ‘e’ which is a symbol for exponent for representing a big number.

But yes, NUMERIC() function does results incorrect results for some values, like:

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('.') as '.' --Period
    ,ISNUMERIC(',') as ',' --Comma

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('-') as '-'
    ,ISNUMERIC('+') as '+'
    ,ISNUMERIC('$') as '$'
    ,ISNUMERIC('\') as '\'

… gives you 1 for all these non-numeric values.

After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

Check my blog post on how to use this – Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
 

–> Answer by CELKO:

The use of E or e for floating point numbers started in the 1950’s with FORTRAN and later in Algol. You wrote “twelve times ten to the forty-fifth power” in the standard notation. The only difference is that some languages have to start with a digit and some can start with the E.

Please look up how floating numbers are displayed. This has been true for over 60 years! The E or e is for “exponent” in the notation that goes back to FORTRAN I.
 

–> Answer by Erland Sommarskog:

In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

SELECT 
CASE WHEN try_convert(int, @str) IS NOT NULL 
       THEN 'Converts' 
       ELSE 'Not convertible' 
END

 

–> Answer by Dan Guzman:

ISNUMERIC isn’t particularly useful for the common use case of checking for a string containing only the digits 0 through 9. Consider a CASE expression instead to get the desired behavior:

CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END

 

Ref link.