我有这个查询:
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 contestantNow 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:
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 ENDand 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.
更多推荐
我的第一个表值函数和游标
发布评论