passing the parameter but output is not coming correctly

Question

0.00/5 (No votes)

See more:

i tried lot of times in my store procedure my required output is not coming correctly.

My store procedure as follows;

SQL

Field           Datatype
Sno	        int
Desc     	varchar(50)	
Hotelname	varchar(MAX)	
Phoneno	        varchar(50)	
Roomrate	varchar(50)	
Active	        char(1)

In table record as follows

SQL

Sno     Desc    Hotelname        Phoneno                   Roomrate         Active

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

3	Accom2	Suryalodge	24851524/9852012312	SingleNonAC 1000 DoubleAC 1600	A
4	Accom2	kalpanlodge	24221222/9844121252	SingleNonAC 1000 DoubleAC 1600 	A

My store procedure code as follows

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[Shortcode_Accom]  (@Keyword varchar(10))
as

 declare @Hotel varchar(25),
 @Phone varchar(25),
 @Room varchar(25),
 @final varchar(max)

 
select @Hotel=Hotelname,@Phone=Phoneno,@Room =Roomrate from Tb_Accommodation where Active <> 'D' and Serialno =@keyword
if(@Hotel!='')
begin
Set @final = 'Dear Students  HIMT Hotel Accommodation is ' +@Hotel+',' +@Phone+ ','+@Room+ ' by marine'
select @final
end

When i execute the above store procedure output as follows

SQL

exec [Shortcode_Accom] 'Accom2'

output as follows

Dear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200 by marine

But i want the output as follows

Dear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200,Suryalodge 24851524/9852012312,SingleNonAC 1000 DoubleAC 1600 by marine

because in the table For Accom2 two records are there. but when i execute in the store procedure only one record is showing.

please help me what is the problem in my above store procedure.

i tried lot of times please help me.

Posted 28-Feb-14 1:56am

Member 8054539

Updated 28-Feb-14 2:03am

Tom Marvolo Riddle

v2

Add a Solution

Comments

ZurdoDev 28-Feb-14 8:00am

I don't quite follow but when you do SELECT @var = whatever if there is more than one record you'll only get the last one.

King Fisher 28-Feb-14 8:34am

your parameter is 'Accom2',so the output will be
'Dear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200 by marine'
you have to alter this.Dont worry you will get it.just wait

3 solutions

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)

ashok rathod · Answer 1 · 2014-02-28T03:04:00

I think you have passed wrong parameter when executing your Sp(exec [Shortcode_Accom] 'Accom2')

because in SP you have compared that with "Serialno" attribute

Other way around is if you want to pass same value as input then in that case you have to made change in your procedure as follows :

select @Hotel=Hotelname,@Phone=Phoneno,@Room =Roomrate from Tb_Accommodation where Active <> 'D' and Serialno =@keyword

should be replaced with

select @Hotel=Hotelname,@Phone=Phoneno,@Room =Roomrate from Tb_Accommodation where Active <> 'D' and Desc=@keyword

OriginalGriff · Answer 2 · 2014-02-28T03:10:00

What you are trying to do is return a set of results in a single row - and for that, you need a cursor:

SQL

DECLARE @STR NVARCHAR(MAX)
SET @STR = ''
DECLARE @SEP NVARCHAR(1)
SET @SEP = ''
DECLARE @V NVARCHAR(MAX)
DECLARE MYCURSOR CURSOR FOR
   SELECT Data FROM MyTable
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @V
WHILE @@FETCH_STATUS=0
   BEGIN
   SET @STR = @STR + @SEP + @V
   SET @SEP = ';'
   FETCH NEXT FROM MYCURSOR INTO @V
   END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
SELECT @STR

You'll need to tweak it to your data, but it's deliberately kept simple so you can see what is going on.

King Fisher · Answer 3 · 2014-02-28T18:20:00

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

passing the parameter but output is not coming correctly

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0