我如何...我希望以消息格式输出存储过程 [英] 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屋!