使用store procdure以句子格式获取输出 [英] Get the output in sentence format using store procdure

问题描述

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER proc [dbo].[Shortcode_Accom]
as
 
declare @Hotelname varchar(25),
@Phoneno varchar(25),
@Roomrate varchar(25),
@CHK int,
@MSG varchar(max),
@final varchar(max),
@Accommodation varchar(20)

create table #TempTable(Hotelname varchar(25),Phoneno varchar(25),Roomrate varchar(25)) 
begin tran

IF @CHK=0
begin
SET @MSG= 'Invalid keyword'
end

select Hotelname,Phoneno,Roomrate from Tb_Accommodation where Active <> 'D'

if @MSG = ''
begin
if @Accommodation= ''
set @final = 'Dear Student, Thanks for contacting us. Please Check us'
else
Set @final = 'Dear Student, ' + @Hotelname+ +@Phoneno+ +@Roomrate+ 'By Marine'
end

else
begin
set @final = 'Invalid Keyword. Sorry try again with valid keyword or visit www.marineinstitute.com.SMS marine xxx to 56100. Eg marine Accommodation1 to 56100'
end
 
commit tran 
  
exec [Shortcode_Accom]

输出如下;

Ouput as follows;

1	Satarlodge	24745734/9840175805	SingleNonAC 500,Double AC 1000	A
2	Sarvanalodge	24151212/9790578502	SingleNonAC 600 Double AC 1200	A

i想要以上句子格式的输出如下

i want the above output in sentence format as follows

Dear Student, 1.Satarlodge,24745734/9840175805,SingleNonAC 500,Double AC 1000 2.Sarvanalodge 24151212/97905785600,Double AC 1200 By Marine

以句子格式获得以上输出我必须在我的以上商店程序以句子格式获得输出。



请帮帮我。



问候，

narasiman P

for getting a above output in sentence format what change i have to be made in my above store procedure to get the output in sentence format.

please help me.

regards,
narasiman P

推荐答案

使用它：

use this:

declare @id bigint
declare @firstname nvarchar(max)
declare @lastname nvarchar(max)
declare @final nvarchar(max)
declare @endid bigint
declare @last nvarchar(max)
create table #temp2 (details nvarchar(max),id bigint)
select @endid =count(employeeid) from MyEmployees
select @id=1
while(@id<@endid)
begin
select @firstname=firstname,@lastname=LastName from MyEmployees where EmployeeID=@id

set @final = @firstname+','+@lastname
insert into #temp2(details,id) select @final,0
set @id=@id+1
end

select @last='Dear Students ' +STUFF((select ',' +CAST(details as nvarchar ) from #temp2 where id=0 for xml path ('')),1,1,'') +' by marine'
select @last

这篇关于使用store procdure以句子格式获取输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！