How do I...i want the store procedure output in message format

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

My store procedure as follows

SQL

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

SQL

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 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

Posted 27-Feb-14 18:24pm

Member 8054539

Updated 6-Mar-14 17:50pm

george4986

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Xavier Sand · Answer 1 · 2014-03-06T11:19:00

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

SQL

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

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

SQL

commit tran

add

SQL

select @final

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

SQL

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

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.