Ahmad Masykur

Share your knowledge although one function!

About the author

Ahmad Masykur is a Software Architecture Engineer at PT. Freeport Indonesia Jakarta Indonesia.
In this blog, I share things of interest to me. Most topics are likely to be related to software development, but don't hold me to it.

Certificates



Awards


Powered by

Widget Prayer Time not found.

There is an error in XML document (4, 16278).X

Page List

Validators


Ahmad Masykur

Parsing Array Parameter in SQL Server Stored Procedure

If we develop application to select multiple data in grid and selected data to be process in stored procedure at same time, we need to pass the values of keys to stored procedure parameters. The problem is SQL Server has not array parameter. Here is some ways to resolve the problem.

  1. Use EXEC to execute dynamic query and passing the stored procedure parameter in WHERE clause.
  2. 2. Use XML as parameter and using OPENXML keyword to convert it to table.

First option is vary simple but it bad practice to use dynamic query. SQL Server can’t save statistic of dynamic query. Second option is better way but more complicated. We need simplest way to passing an array to stored procedure parameter.

Other option we can create table function to parse delimited string to table. Here is sample function to do that.

CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
     RowId int Not Null IDENTITY(1,1) PRIMARY KEY
    ,Value nvarchar(255) Not Null
)
AS
BEGIN
    DECLARE  @startPos smallint
            ,@endPos smallint

    IF (RIGHT(@String, 1) != @Delimiter)
        SET @String = @String + @Delimiter
    SET @startPos = 1
    SET @endPos = CharIndex(@Delimiter, @String)
    WHILE @endPos > 0
        BEGIN
            INSERT @Values(Value)
            SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
            -- remove the delimiter just used
            SET @String = STUFF(@String, @endPos, 1, '')
            -- move string pointer to next delimiter
            SET @startPos = @endPos
            SET @endPos = CHARINDEX(@Delimiter, @String)
    END
    RETURN
END

This function can be used in sample stored procedure below.

CREATE PROCEDURE GetCustomers 
    @customerIDs AS nvarchar
AS
BEGIN
    DECLARE @custIDs AS table (Value nvarchar(255) Not Null)
    INSERT INTO @custIDs
        SELECT Value FROM [fn_ParseDelimitedStrings](@customerIDs, ',') 
    SELECT * FROM Orders WHERE CustomerID IN (SELECT Value FROM @custIDs)
END

It very simple query. Just declare memory table and parse the delimited string into table to be used in select criteria.

I hope this tips is helpful for you.


Categories: SQL Server | Tips
Permalink | Comments (7) | Post RSSRSS comment feed

Updating Prayer Widget due to Previous IP to Geo Service was Unavailable

I was developed Prayer Time widget a month ago. I was used IP to geo service that not available anymore. This update using hostip.info service instead. The hostip.info service is not reliable enough for all IP world wide. Some IPs are not covered yet but more stable than previous service.

Here’s core of my code to retrieve geographic information from hostip.info.

WebClient webClient = new WebClient();
string responseString = null;
try
{
    responseString =
        webClient.DownloadString(string.Format("http://api.hostip.info/get_html.php?ip={0}&position=true",
                                               ipAddress));
}
catch (WebException)
{
    responseString = string.Empty;
}
catch (Exception)
{
    responseString = string.Empty;
}
Regex regex = new Regex("Country\\:\\s(?<Country>[A-Za-z0-9]+)\\s\\((?<CountryId>[A-Z]{2})\\)\\nCity\\:\\s(?<City>[A-Za-z0-9]+)\\nLatitude\\:\\s(?<Latitude>\\-?[0-9\\.]+)\\nLongitude\\:\\s(?<Longitude>\\-?[A-Za-z0-9\\.]+)");
if (!string.IsNullOrEmpty(responseString))
{
    Match m = regex.Match(responseString);
    if (m.Success)
    {
        city = m.Groups["City"].Value;
        country = m.Groups["Country"].Value;
        countryId = m.Groups["CountryId"].Value;
        longitude = m.Groups["Longitude"].Value;
        latitude = m.Groups["Latitude"].Value;
    }
}

The complate source code can be downloaded here.

I hope this update will be more reliable that previous version.


Categories: BlogEngine
Permalink | Comments (3) | Post RSSRSS comment feed