我如何...我希望以消息格式输出存储过程 [英] How do I...i want the store procedure output in message format

问题描述

我的商店程序如下

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 

当我按如下方式执行上述存储过程输出时

When i execute the above store procedure output as follows

exec [Shortcode_Accom]

输出如下;



1 Satarlodge 24745734/9840175805 SingleNonAC 500，双AC 1000 A

2 Sarvanalodge 24151212/9790578502 SingleNonAC 600双AC 1200 A



i希望以上句子格式输出如下



亲爱的学生，1.Satarlodge，24745734/9840175805，SingleNonAC 500，Double AC 1000 2。 Sarvanalodge 24151212/97905785600，Double AC 1200 By Marine





获得高于输出的句子格式化我必须在上面的商店程序中进行的更改以获得句子格式的输出。



请帮助我。

< br $> b $ b问候，

narasiman P

Ouput as follows;

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

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


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

推荐答案

您看到的数据似乎是执行查询的结果在你的sp中间。

The Data you see there seems to be the result of the query executed in the middle of your sp.

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

你应该评论或删除此行。

，就在

You should comment or delete this line.
and just before the

commit tran

add

add

select @final

但这不会显示结果，因为你的变量声明之后从未使用过。

但也许你想要的是

But this won't show result because your variables are never used after declare.
But maybe what you wanted to was

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

但是，如果我正确理解结果，看起来查询返回 2 行，那么您将无法将这些值分配到 1 变量。



实际上，您的变量都没有分配值，您正在检查：

But, if I understand the result correctly, it looks like the query returns 2 rows, then you won't be able to assign these values into 1 variable.

Actually, none of your variable are assigned a value, you're checking:

<pre lang="SQL">if @Accommodation= ''

，但你之前只宣布过，这个条件永远是真的..



你应该先查看你的流程，因为你发送的内容没有逻辑。

, but you only declared it before, this condition will always be true..

You should review your process first, because there is no logic in what you sent.

这篇关于我如何...我希望以消息格式输出存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！