我的输出需要使用我的bleow存储过程在一行中 [英] My output need to be in a single line using my bleow store procedure
问题描述
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]。[Shortcode_Accommodation]( @ Keyword varchar ( 50 ))
as
声明 @ Hotel varchar ( 25 ),
@ Phone varchar ( 25 ),
@ final varchar (max)
create table #TempTable(res varchar (max))
开始
insert into #Temptable(res) values ( ' 亲爱的学生,酒店住宿是');
insert into #Temptable(res)
选择 CONVERT ( varchar ,ROW_NUMBER() over ( order by Hotel_Name))+ ' 。 ' + Hotel_Name + ' ,' + Phoneno + ' '
来自 Tb_Accommodation 其中有效<> ' D' 和描述= @ Keyword
insert into #Temptable (res) values (' by marine');
选择 * 来自 #Temptable
结束
当我按如下方式执行上述存储过程输出时,
exec [Shortcode_Accommodation] ' 住宿'
引用:亲爱的学生,酒店住宿是
1. HOTEL RAAJ BHAAVAN,26404621/22
2.酒店SHRI VALLI RESIDENCY(新RAJ BHAVAN),26422297/42668009
3. SATKAR LODGE,9380197278/26425881 / 33570477
海运
但我希望单行需要输出如下
引用:亲爱的学生们,酒店住宿是1.HOTEL RAAJ BHAAVAN,26404621/22 2.HOTEL SHRI VALLI RESIDENCY(新RAJBHAVAN),26422297/42668009 3.SATKAR LODGE,9380197278/26 425881/33570477海运
在我上面的商店程序中我犯了什么错误。
请帮帮我。
问候,
narasiman P.
你可以用光标:
声明 @ m1 as varchar (max);
声明 @ m2 as varchar (max);
set @ m1 = ' ';
set @ m2 = ' ';
声明 dircursor cursor for 选择 * 来自 #Temptable;
open dircursor;
fetch next 从 dosprsor 进入 @ m2 ;
while @@ FETCH_STATUS = 0
begin
set @ m1 = @ m1 + ' ' + @ m2 ;
fetch next 从 dosprsor 进入 @ m2 ;
end
close dircursor;
deallocate dircursor
print @ M1 跨度>;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Shortcode_Accommodation] (@Keyword varchar(50))
as
declare @Hotel varchar(25),
@Phone varchar(25),
@final varchar(max)
create table #TempTable(res varchar(max))
begin
insert into #Temptable(res) values('Dear Students, Hotel Accommodation is');
insert into #Temptable(res)
select CONVERT(varchar, ROW_NUMBER() over(order by Hotel_Name))+'. '+Hotel_Name+','+Phoneno+''
from Tb_Accommodation where Active <> 'D' and Description = @Keyword
insert into #Temptable(res) values('By marine');
select * from #Temptable
end
when I execute the above store procedure output as follows
exec [Shortcode_Accommodation] 'Accommodation'
Quote:Dear Students, Hotel Accommodation is
1. HOTEL RAAJ BHAAVAN,26404621/22
2. HOTEL SHRI VALLI RESIDENCY(NEW RAJ BHAVAN),26422297/42668009
3. SATKAR LODGE,9380197278/26425881/33570477
By marine
But i want the output to be needed in single line as follows
Quote:Dear Students,Hotel Accommodation is 1.HOTEL RAAJ BHAAVAN,26404621/22 2.HOTEL SHRI VALLI RESIDENCY(NEW RAJBHAVAN),26422297/42668009 3.SATKAR LODGE,9380197278/26425881/33570477 By marine
for that in my above store procedure what is the mistake I made.
please help me.
regards,
narasiman P.
you can use cursor:
declare @m1 as varchar(max); declare @m2 as varchar(max); set @m1 = ''; set @m2 = ''; declare dircursor cursor for select * from #Temptable; open dircursor; fetch next from dircursor into @m2; while @@FETCH_STATUS=0 begin set @m1 = @m1 +' '+ @m2; fetch next from dircursor into @m2; end close dircursor; deallocate dircursor print @m1;
这篇关于我的输出需要使用我的bleow存储过程在一行中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!