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

Resolving SQL BPA Command Line Problem when Running SQL Server 2005 Upgrade Advisor

Currently, I want to upgrade our SQL Server 2000 databases to SQL Server 2005. The first step is running Microsoft SQL Server Upgrade Advisor. I was installed this tool on my computer and no error in installation. When I running this tool and try to analyze database engine, it returning error message:

"SQL BPA command line has encountered a problem and needs to close. We are sorry for the inconvenience."

I confused about this error because I was use this tool a few year ago without any problems. What is the problem, my database, my account or the tool problem? I'm forget that used SQL Server 2005 Upgrade Advisor with Service Pack 1 that different with previous version that was used. I think it the tool problem. I'm try to look at the forum that any suggestions. One of the suggestions is to registering BPAClient.dll to the GAC. It resolve the problem for me.

To resolve this problem, follow the instructions below.

  1. Open Visual Studio 2005/2008 Command Prompt
  2. Change directory to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin or based on your software installation path.
  3. Type following command:
    C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin>gacutil /i BPAClient.dll

I hope this information is helpful.


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

SQL Server 2008 and Visual Studio 2008 Service Pack 1 Available for Download

I was got mail alert from MSDN Flash tonight. In the top news, Microsoft was announced that SQL Server 2008 has been released. MSDN subscribers can find RTM versions now available for download. Visual Studio 2008 Service Pack 1 offers VS 2008 and .NET Framework 3.5 users improvements in responsiveness, stability, and performance. Visual Studio 2008 Service Pack 1 and .NET Framework 3.5 Service Pack 1 downloads are now available to MSDN subscribers. SQL Server 2008 evaluation versions are available for free download as are free downloads of SQL Server 2008 Express and SQL Server Compact editions.

Permalink | Comments (12) | Post RSSRSS comment feed