我的第一个表值函数和光标(My first table valued function and cu

我有这个疑问：

SELECT name, lastname
FROM contestant 
WHERE  name= 'John'  AND lastname = 'Smith'

我从上面的查询几个结果，我需要将它们用于以下查询：

SELECT  name, lastname,prize, city 
FROM draw
WHERE  name= name from table contestant  AND lastname= name from table contestant  

现在，我要建一个表值函数与光标和WHILE这样我就可以有结果的表。

这里是我的尝试，可以请你帮我完成了吗？ 这将是对我很大的帮助，以了解这个TSQL章。 谢谢！

CREATE FUNCTION [dbo].[myFunction]
(
    @name varchar (44),
    @lastname varchar (44) 
)
RETURNS 
@tmpTable TABLE 
(   
    name char(44),
    lastname char(44),
    prize varchar(44),
    city char(44)

)
AS
BEGIN

    DECLARE 
/* what do I have to input here */

    DECLARE myCursor CURSOR FOR

SELECT name, lastname
FROM contestant 
WHERE  name= @name  AND lastname = @lastname

    OPEN myCursor

    FETCH NEXT FROM myCursor INTO  /* what goes here?*/



    WHILE (@@FETCH_STATUS = 0) 
    BEGIN

 -- and here? 

        FETCH NEXT FROM myCursor INTO /* what goes here?*/


    END /*WHILE*/

    CLOSE myCursor
    DEALLOCATE myCursor


    INSERT INTO @tmpTable (name, lastname,prize, city)
    SELECT name, lastname,prize, city 
        FROM prize
        WHERE name = @name AND lastname = @lastname

    RETURN
END

Answer 1:

OK，只要你明白：

1. 该表的设计是不正确的 - 你应该有两个表的选手关键。
2. 一个连接是解决这个，不是一个游标
3. 我所提供这里是解决这一最坏的可能方式，你需要从该学习什么，你不应该以此作为解决这个问题！

但在回答你的问题我怎么使用游标 ，这里是一些未经测试的代码，希望给你的概念。

CREATE FUNCTION [dbo].[myFunction]
(
@name varchar (44),
@lastname varchar (44) 
)
RETURNS 
@tmpTable TABLE 
(   
name char(44),
lastname char(44),
prize varchar(44),
city char(44)
)
AS
BEGIN

DECLARE @c_name varchar (44)
DECLARE @c_lastname varchar (44) 


DECLARE myCursor CURSOR FOR

SELECT name, lastname
FROM contestant 
WHERE  name= @name  AND lastname = @lastname

OPEN myCursor

FETCH NEXT FROM myCursor INTO  @c_name, @c_lastname

WHILE (@@FETCH_STATUS = 0) 
BEGIN

    -- we've found a row. Name look for the matching row in prize
    INSERT INTO @tmpTable (name, lastname,prize, city)
    SELECT name, lastname,prize, city 
    FROM prize
    WHERE name = @c_name AND lastname = @c_lastname

    FETCH NEXT FROM myCursor INTO @c_name, @c_lastname

END /*WHILE*/

CLOSE myCursor
DEALLOCATE myCursor

RETURN
END

而作为对比，这里是妥善的解决办法：

SELECT draw.name, draw.lastname, draw.prize, draw.city
FROM 
draw
INNER JOIN
contestant 
ON draw.name = contestant.name
AND draw.lastname = contestant.lastname
WHERE  contestant.name= 'John'  
AND contestant.lastname = 'Smith'

它的小，更简单，更快速。

文章来源: My first table valued function and cursor