我在我的存储过程中输出dbut输出正确 [英] i trie dbut in my stored procedure output is coming correctly
本文介绍了我在我的存储过程中输出dbut输出正确的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在表格记录中如下
TTDate课程Sess主题人员
Aug2 Eng 1 Rev Mj
Aug2 Tam 2 Maj Mj
Aug2 Mat 3 Tom Mj
从我的上表我想要输出如下
亲爱的Mj,8月2日(Eng -Rev / 1,Tam -Maj / 2,Mat -Tom / 3)
为此,我写了一个存储过程如下>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]。[OH_TimeTable]
as
DECLARE @ MyString VARCHAR (MAX)
SET @ MyString = ' '
SELECT @ MyString = COALESCE ( @ MyString + ' ,',' ')+ [TTDate] + [课程] + [Sess] + [主题]
来自 Tb_Sch_Time_Table 其中有效<> ' D'
SELECT @ MyString as MyString
当我按如下方式执行存储过程输出时
,Aug2 Eng 1 Rev,Aug2 Tam 2 Maj,Aug2 Mat 3 Tom
但我希望存储过程输出如下
亲爱的Mj,Aug2(Eng -Rev / 1,Tam -Maj / 2,Mat - Tom / 3)
请帮我完成上面的存储过程。我犯了什么错误。
解决方案
SET @ MyString = ' 亲爱的'
SELECT @ MyString = COALESCE ( @ MyString + Staff,' ' )+ ' ,(' + [TTDate] + [课程] + [Sess] + [主题] + ' )'
来自 Tb_Sch_Time_Table 其中有效<> ' D'
尝试以下代码
声明 @ str varchar ( 100 ), @ staff < span class =code-keyword> varchar ( 800 ), @ finalstr varchar ( 1000 )
选择 < span class =code-sdkkeyword> @ staff = ltrim(rtrim(Staff)), @ str = ltrim(rtrim(转换( char ( 7 ),sch1。[TTDate], 100 )))+ ' (' + STUFF(
(选择 ' ,' + ltrim(rtrim([课程] ))+ ' - ' + LTRIM(rtrim(topic))+ ' /' + ltrim(rtrim([Sess]))来自 Tb_Sch_Time_Table sch
其中 sch.TTDate = sch1.TTDate
FOR XML PATH(< span class =code-string>' '))
, 1 , 1 ,' ')+ ' )' 来自 Tb_Sch_Time_Table sch1 其中有效<> ' D'
group by TTDate,Staff
order by 1
set @finalstr = ' 亲爱的' + @ staff + ' ,' + @ str
print @ finalstr 跨度>
In table records as follows
TTDate Course Sess Topic Staff
Aug2 Eng 1 Rev Mj
Aug2 Tam 2 Maj Mj
Aug2 Mat 3 Tom Mj
From my above table i want output as follows
Dear Mj, Aug2(Eng -Rev/1,Tam -Maj/2,Mat -Tom/3)
For that i written a stored procedure as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[OH_TimeTable]
as
DECLARE @MyString VARCHAR(MAX)
SET @MyString = ''
SELECT @MyString = COALESCE(@MyString + ',', ' ') +[TTDate] + [Course] + [Sess] +[Topic]
from Tb_Sch_Time_Table where Active <> 'D'
SELECT @MyString as MyString
When i execute the stored procedure output as follows
,Aug2 Eng 1 Rev,Aug2 Tam 2 Maj,Aug2 Mat 3 Tom
But i want the stored procedure output as follows
Dear Mj, Aug2(Eng -Rev/1,Tam -Maj/2,Mat -Tom/3)
please help me from my above stored procedure. what is the mistake i made.
解决方案
SET @MyString = 'Dear' SELECT @MyString = COALESCE(@MyString + Staff, ' ') + ',(' +[TTDate] + [Course] + [Sess] +[Topic] + ')' from Tb_Sch_Time_Table where Active <> 'D'
try the below code
declare @str varchar(100), @staff varchar(800), @finalstr varchar(1000) select @staff = ltrim(rtrim(Staff)), @str = ltrim(rtrim(Convert(char(7),sch1.[TTDate],100)))+'('+STUFF( (select ','+ ltrim(rtrim([Course])) +'-'+ LTRIM(rtrim(topic)) +'/'+ ltrim(rtrim([Sess])) from Tb_Sch_Time_Table sch where sch.TTDate = sch1.TTDate FOR XML PATH('')) ,1,1,'')+')' from Tb_Sch_Time_Table sch1 where Active <> 'D' group by TTDate, Staff order by 1 set @finalstr = 'Dear '+@staff+', '+@str print @finalstr
这篇关于我在我的存储过程中输出dbut输出正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文