Classic examples of custom functions and cursors applied in SQL Server

Source: Internet
Author: User
Tags count datetime end functions insert sql server sql
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




Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.