Get the output in sentence format using store procdure

Question

0.00/5 (No votes)

See more:

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 
  
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 19:41pm

Member 8054539

Updated 27-Feb-14 20:24pm

Maciej Los

v2

Add a Solution

Comments

King Fisher 28-Feb-14 8:28am

have you tried with solution1?

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)

King Fisher · Answer 1 · 2014-02-28T18:16:00

use this:

SQL

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