我的第一个表值函数和游标

本文介绍了我的第一个表值函数和游标的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个查询：

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

表格设计不正确

加入是对此的解决方案，而不是游标

我在这里提供的是解决这个问题的最可能的方法是什么，你需要从中学习，你不应该使用这个解决方案来解决这个问题！

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

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:

The table designs are incorrect - you should have a contestant key in both tables.

A join is the solution to this, not a cursor

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.