我的第一个表值函数和游标 [英] My first table valued function and cursor

问题描述

我有这个查询：

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

我从上面的查询中得到几个结果，它们用于以下查询：

  SELECT name，lastname，prize，city 
 FROM draw 
 WHERE name =表格参赛者的姓名和姓氏=表格参赛者的姓名
  

现在我建立一个表值函数有光标和WHILE，所以我可以有一个表的结果。

这是我的尝试，你能帮助我完成它吗？这对我来说是非常有帮助的，为了理解这个TSQL章节。谢谢！

  CREATE FUNCTION [dbo]。[myFunction] 
（
 @name varchar（44） b $ b @lastname varchar（44）
）
 RETURNS 
 @tmpTable TABLE 
（
 name char（44），
 lastname char ，
 prize varchar（44），
 city char（44）
 
）
 AS 
 BEGIN 
 
 DECLARE 
 / *我必须在这里输入* / 
 
 DECLARE myCursor CURSOR FOR 
 
 SELECT name，lastname 
 FROM contestant 
 WHERE name = @name AND lastname = @lastname 
 
 OPEN myCursor 
 
 FETCH NEXT FROM myCursor INTO / *这里是什么？* / 
 
 
 
 WHILE（@@ FETCH_STATUS = 0）
 BEGIN 
 
  - 这里？ 
 
 FETCH NEXT FROM myCursor INTO / *这里是什么？* / 
 
 
 END / * WHILE * / 
 
关闭myCursor 
 DEALLOCATE myCursor 
 
 
 INSERT INTO @tmpTable（姓名，姓氏，奖品，城市）
 SELECT名称，姓氏，奖品，城市
 FROM $ b WHERE name = @name AND lastname = @lastname 
 
 RETURN 
 END 
  

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 
 
 - 我们找到了一行。名称查找奖励中匹配的行
 INSERT INTO @tmpTable（姓名，姓氏，奖品，城市）
 SELECT名称，姓氏，奖金，城市
 FROM奖品
 WHERE name = @c_name AND lastname = @c_lastname 
 
 FETCH NEXT FROM myCursor INTO @c_name，@c_lastname 
 
 END / * WHILE * / 
 
关闭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 .name ='John'
 AND contestant.lastname ='Smith'
  

更简单和更快。

I have this query:

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

I get several results from the query above and I need to use them for the following query:

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

Now I’m building a table valued function with a cursor and a WHILE so I can have a table with the results.

Here’s my try, can you please help me complete it? it will be very helpful to me in order to understand this TSQL chapter. Thanks!

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

解决方案

OK as long as you understand that:

1. The table designs are incorrect - you should have a contestant key in both tables.
2. A join is the solution to this, not a cursor
3. What I am providing here is the worst possible way to solve this and what you need to learn from this is that you should never use this as a solution to this problem!

But in answer to your question how do I use a cursor, here is some untested code that hopefully gives you the concept.

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

and as a comparison, here is the proper solution:

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'

Its smaller, simpler and faster.

这篇关于我的第一个表值函数和游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！