存储过程中的 SQL 游标用于填充字符串变量

发布于 2024-10-11 02:50:10 字数 960 浏览 2 评论 0原文

我有一个存储过程，其中包含一个游标，用于循环遍历 SQL 记录并填充稍后将用作电子邮件文本的字符串。我试图将其打印出来进行验证，然后才能继续操作，但它似乎没有填充该字符串。这是我在 SQL Server 2005 中的存储过程。

CREATE PROCEDURE [dbo].[spBody] 
AS
DECLARE @MyCursor CURSOR
DECLARE @emailBody nvarchar(max)
DECLARE @statusName nvarchar(max)
DECLARE @deptCode nvarchar(max)
DECLARE @instructors nvarchar(max)
DECLARE @meetingTime nvarchar(max)

SET @MyCursor = CURSOR FAST_FORWARD For
Select StatusName, DeptCode, Instructors, Description from MyTable where StatusID = (select CAST(value AS INT) from Table2 where ConfigOption = 'RequiredStatus')

Open @MyCursor
FETCH NEXT FROM @MyCursor INTO @statusName, @deptCode, @instructors, @meetingTime


WHILE @@FETCH_STATUS = 0

BEGIN
 SET @emailBody = @emailBody + @statusName + ' ' + @deptCode + ' ' + @instructors + ' ' + @meetingTime 
 FETCH NEXT FROM @MyCursor INTO @statusName, @deptCode, @instructors, @meetingTime

END
CLOSE @MyCursor
Print @emailBody

DEALLOCATE @MyCursor

原文

I have a stored procedure that contains a cursor to loop through SQL records and populates the string which I will use later as my email text. I'm trying to print it out to verify before I can proceed with it but it seems to not populate the string. Here is my stored procedure in SQL Server 2005.

CREATE PROCEDURE [dbo].[spBody] 
AS
DECLARE @MyCursor CURSOR
DECLARE @emailBody nvarchar(max)
DECLARE @statusName nvarchar(max)
DECLARE @deptCode nvarchar(max)
DECLARE @instructors nvarchar(max)
DECLARE @meetingTime nvarchar(max)

SET @MyCursor = CURSOR FAST_FORWARD For
Select StatusName, DeptCode, Instructors, Description from MyTable where StatusID = (select CAST(value AS INT) from Table2 where ConfigOption = 'RequiredStatus')

Open @MyCursor
FETCH NEXT FROM @MyCursor INTO @statusName, @deptCode, @instructors, @meetingTime


WHILE @@FETCH_STATUS = 0

BEGIN
 SET @emailBody = @emailBody + @statusName + ' ' + @deptCode + ' ' + @instructors + ' ' + @meetingTime 
 FETCH NEXT FROM @MyCursor INTO @statusName, @deptCode, @instructors, @meetingTime

END
CLOSE @MyCursor
Print @emailBody

DEALLOCATE @MyCursor

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

混浊又暗下来 2024-10-18 02:50:10

这是因为 @emailBody 开始时为 NULL，并且默认情况下任何与 NULL 的串联都会产生 NULL。执行 a

SET @emailBody = '';

在脚本的开头

。另外，强烈考虑在存储过程的顶部添加 SET NOCOUNT ON; 语句 - 没有 NOCOUNT ON 会大大减慢过程的执行速度。

It's because @emailBody starts out as NULL, and any concatenation with NULL yields NULL by default. Do a

SET @emailBody = '';

at the beginning of your script.

Also, strongly consider adding a SET NOCOUNT ON; statement at the top of your stored procedure -- not having NOCOUNT ON can greatly slow the execution of your proc.

回复收藏 0 原文

吃素的狼 2024-10-18 02:50:10

为什么这个字符串连接需要游标。不会出现以下查询后缀

DECLARE @emailBody nvarchar(max) 

Set @emailBody = ''

Select @emailBody = @emailBody + StatusName + ' ' + DeptCode + ' ' + Instructors + ' ' + [Description] from MyTable where StatusID = (select CAST(value AS INT) from Table2 where ConfigOption = 'RequiredStatus')

Print @emailBody

Why do you need a cursor for this string concat. Wont the following query suffix

DECLARE @emailBody nvarchar(max) 

Set @emailBody = ''

Select @emailBody = @emailBody + StatusName + ' ' + DeptCode + ' ' + Instructors + ' ' + [Description] from MyTable where StatusID = (select CAST(value AS INT) from Table2 where ConfigOption = 'RequiredStatus')

Print @emailBody

回复收藏 0 原文

~没有更多了~