传递参数但输出未正确 [英] passing the parameter but output is not coming correctly

问题描述

我在我的商店程序中尝试了很多次我的所需输出不正确。



我的商店程序如下;

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

My store procedure as follows;

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

表记录如下

In table record as follows

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

< br $>


我的商店程序代码如下

My store procedure code as follows

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

exec [Shortcode_Accom] 'Accom2'

输出如下



亲爱的学生HIMT酒店住宿是kalpanlodge，24221222/9844121252，AC 1200 by marine



但我希望输出如下



亲爱的学生HIMT酒店住宿是kalpanlodge，24221222 / 9844121252，AC 1200，Suryalodge 24851524/9852012312，SingleNonAC 1000 DoubleAC 1600 by marine



因为在表中对于Accom2有两个记录。但是当我在商店程序中执行时只显示一条记录。



请帮我解决上述商店程序中的问题。



i多次试过请帮助我。

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.

推荐答案

我认为你在执行Sp时传递了错误的参数（exec [Shortcode_Accom] 'Accom2'）



因为在SP中您将其与Serialno属性进行比较



其他方式周围是如果你想传递相同的值作为输入那么在这种情况下你必须改变你的程序如下：





选择@ Hotel = Hotelname，@ Phone = Phoneno，@ Room = Roomrate from Tb_Accommodation，其中Active<> 'D'和 Serialno = @ keyword



应替换为



选择@ Hotel = Hotelname，@ Phone = Phoneno，@ Room = Roomrate from Tb_Accommodation，其中Active<> 'D'和 Desc = @ keyword

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

您要做的是在一行中返回一组结果 - 为此，你需要一个游标：

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

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.

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

这篇关于传递参数但输出未正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！