Jagadish Pulakhandam's: December, 2007

Sending multiple values in the form of array to a SQL Server stored procedure is very common among database developers. We can send the values in either of the two methods as following:

· Comma separated values (in a single string)

· XML document (in a single string)

This post focuses on comma separated values.

The following converts a parameter containing a string (array) of values (separated by commas) to a table:

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))

returns @result TABLE (Value varchar(30))

AS

begin

DECLARE @TempList table

(

Value varchar(30)

)

DECLARE @Value varchar(30), @Pos int

SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','

SET @Pos = CHARINDEX(',', @Parameters, 1)

IF REPLACE(@Parameters, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))

IF @Value <> ''

BEGIN

INSERT INTO @TempList (Value) VALUES (@Value)

END

SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)

SET @Pos = CHARINDEX(',', @Parameters, 1)

END

INSERT @result

SELECT value

FROM @TempList

RETURN

END

The above function can be tested with the following statement:

select * from dbo.ParmsToList ('1001,2323,jagadish,pulakhandam,3232')

We can utilize the above function and scale out to more functions according to our requirements. For example, I would like to have an “IN” parameter list to be returned for a given string of comma separated values. I can implement a new function (which in-turn invokes the above function) as following:

create function dbo.ParmsToINValues (@Parameters varchar(500))

returns varchar(1000)

AS

begin

DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!

item varchar(200)

)

insert into @item_table select * from dbo.ParmsToList (@Parameters)

DECLARE @item varchar(200)

DECLARE @item_counter INT

DECLARE @loop_counter INT

DECLARE @result varchar(1000)

SELECT @loop_counter = COUNT(*) FROM @item_table

SET @item_counter = 1

SET @result = ''

WHILE @loop_counter > 0 AND @item_counter <= @loop_counter

BEGIN

SELECT @item = item

FROM @item_table

WHERE primary_key = @item_counter

SET @result = @result + '''' + @item + ''','

SET @item_counter = @item_counter + 1

END

SET @result = LEFT(@result,LEN(@result)-1) -- trim last comma

return @result

END

To test the above function, we can use the following script:

declare @result varchar(1000)

set @result = dbo.ParmsToINValues('1001,2323,jag,chat,3232')

print 'select * from emp where empid in (' + @result + ')'

The resulting output would be as follows:

select * from emp where empid in ('1001','2323','jag','chat','3232')

Hope the post is useful….

Thanks

Jag

December 16

Part 4: Learning LINQ from SQL using Visual Basic 2008 - Sub Queries

The source code for all of the following LINQ queries can be downloaded here:

A simple sub-query representation using LINQ (with aggregation in sub-query):

The following SQL SELECT

SELECT ename FROM emp

WHERE sal = (SELECT MAX(sal) FROM emp)

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() _

Where (p.Sal) = (From q In GetEmployeeList() _

Select q.Sal).Max() _

Select p.Ename

A simple sub-query representation using LINQ (without aggregation in sub-query):

The following SQL SELECT

SELECT dname FROM dept

WHERE deptno = (SELECT deptno FROM emp

WHERE empno = 1002)

can be coded in LINQ as following:

Dim d As String = (From q In GetEmployeeList() _

Where q.Empno = 1002 _

Select q.Deptno).First.ToString

Dim query = From p In GetDeptList() _

Where (p.Deptno) = d _

Select p.Dname

or directly like the following:

Dim query = From p In GetDeptList() _

Where (p.Deptno) = (From q In GetEmployeeList() _

Where q.Empno = 1002 _

Select q.Deptno).First.ToString _

Select p.Dname

A nested sub-query representation using LINQ:

The following SQL SELECT

SELECT ename FROM emp

WHERE sal = (SELECT MAX(sal) FROM emp

WHERE sal < (SELECT MAX(sal) FROM emp))

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() _

Where (p.Sal) = (From r In GetEmployeeList() _

Where r.Sal < (From q In GetEmployeeList() _

Select q.Sal).Max() _

Select r.Sal).Max() _

Select p.Ename

Parallel sub-query representation using LINQ:

The following SQL SELECT

SELECT ename, sal FROM emp

WHERE sal >= (SELECT losal FROM salgrade

WHERE grade = 1)

AND sal <= (SELECT hisal FROM salgrade

WHERE grade = 1)

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() _

Where (p.Sal) >= (From q In GetSalGradeList() _

Where q.Grade = 1 _

Select q.Losal).First.ToString _

And (p.Sal) <= (From r In GetSalGradeList() _

Where r.Grade = 1 _

Select r.Hisal).First.ToString _

Select p.Ename, p.Sal

Representing sub-query as part of HAVING clause using LINQ:

The following SQL SELECT

SELECT deptno, MAX(sal) FROM emp

GROUP BY deptno

HAVING MAX(sal) > (SELECT AVG(sal) + 2000 FROM emp)

can be coded in LINQ as following:

Dim query = From p In GetEmployeeList() _

Group p By p.Deptno _

Into Group _

Where Group.Max(Function(p) p.Sal) > (From q In GetEmployeeList() _

Select q.Sal).Average() + 2000 _

Select _

Deptno, _

MaxSal = Group.Max(Function(p) p.Sal)