Classic examples of custom functions and cursors applied in SQL Server

server| Function | Cursor This is the Netizen's question, I at that time gave out own solution, but did not think the middle has a little problem, discovered after oneself careful debugging, completely obtains the correct result, that Netizen already has the knot post. My code became chicken ribs, food tasteless, discard. But I think my code is really quite classic, so tidy up a bit, for everyone to appreciate. Problem:


Suppose the environment is as follows:

Table 1:id, NAME, QQ, PHONE,

Table data: 1 Qin Yun 10102800 13500000

2 on the road 10378 13600000

3 LEO 10000 13900000



Table 2:id, NAME, Machine time, admin,

Table data: 1 Qin Yun 2004-1-1 Li Dawei

2 Qin Yun 2005-1-1 Ma Teng

3 on the way to 2005-1-1 Ma Teng

4 Qin Yun 2005-1-1 Li Dawei

5 on the way 2005-1-1 Li Dawei



Implementation objective: Take a list of all people from table 1, take the number of machines and administrators from table 2.

On-Machine personnel list the number of computer administrators (each administrator on these several machines are listed)

Qin Yun 3 Li Dawei, MA, Li Dawei

On the way 2 Ma, Li Dawei

LEO 0

If it's not the manager's list, that's what I wrote.

SELECT table 1.NAME as name, COUNT (table 2.ID) as number of machines

From table 1 left OUTER JOIN

Table 2 on table 1.NAME = Table 2.NAME

GROUP by table 1. Name


Answer:
Test Cases



CREATE table 1 (--drop table 1

ID int,

NAME varchar (10),

QQ varchar (10),

PHONE varchar (20)

)



Insert INTO Table 1 values (1, ' Qin Yun ', ' 10102800 ', ' 13500000 ')

Insert INTO Table 1 values (2, ' On the road ', ' 10378 ', ' 13600000 ')

Insert INTO Table 1 values (3, ' LEO ', ' 10000 ', ' 13900000 ')



CREATE table 2 (--drop table 2

ID int,

NAME varchar (10),

Time datetime on the machine,

Administrator varchar (10)

)



Insert INTO Table 2 values (1, ' Qin Yun ', cast (' 2004-1-1 ' as datetime), ' Li Dawei '

Insert INTO Table 2 values (2, ' Qin Yun ', cast (' 2005-1-1 ' as datetime), ' Ma Teng ')

Insert INTO Table 2 values (3, ' On the road ', cast (' 2005-1-1 ' as datetime), ' Ma Teng ')

Insert INTO Table 2 values (4, ' Qin Yun ', cast (' 2005-1-1 ' as datetime), ' Li Dawei '

Insert INTO Table 2 values (5, ' on the road ', cast (' 2005-1-1 ' as datetime), ' Li Dawei ')



Program part



Create function Getnamestr (@name nvarchar (10))

Returns nvarchar (800)

As

Begin

declare @nameStr nvarchar (800)

declare @tempStr nvarchar (800)

DECLARE @flag int

DECLARE mycur cursor for (select Administrator from table 2 where table 2.NAME = @name)

Open Mycur

FETCH NEXT from Mycur into @tempStr

Set @flag = 0

While @ @fetch_status = 0

Begin

If @flag = 0

Begin

Set @nameStr = @tempStr

End

Else

Begin

Set @nameStr = @nameStr + ', ' + @tempStr

End

Set @flag = @flag + 1

FETCH NEXT from Mycur into @tempStr

End

Close Mycur

Deallocate mycur

Return @nameStr

End



Select Table 2.NAME as name, Count (ID) as on machine times, dbo. GETNAMESTR (table 2.NAME) as Administrator

From table 2

where table 2.NAME in (select Table 1.NAME from table 1)

Group BY Table 2.NAME





Test results:



Name on machine count administrator

--------------------------------------------------------------

Qin Yun 3 Li Dawei, MA, Li Dawei

On the way 2 Ma, Li Dawei