本文介绍了传递参数但输出未正确的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 我在我的商店程序中尝试了很多次我的所需输出不正确。 我的商店程序如下; i tried lot of times in my store procedure my required output is not coming correctly. My store procedure as follows;Field DatatypeSno intDesc varchar(50)Hotelnamevarchar(MAX)Phoneno varchar(50)Roomratevarchar(50)Active char(1) 表记录如下 In table record as followsSno Desc Hotelname Phoneno Roomrate Active1Accom1Satarlodge24745734/9840175805SingleNonAC 500,Double AC 1000A2Accom1Sarvanalodge24151212/9790578502SingleNonAC 600 Double AC 1200A3Accom2Suryalodge24851524/9852012312SingleNonAC 1000 DoubleAC 1600A4Accom2kalpanlodge24221222/9844121252SingleNonAC 1000 DoubleAC 1600 A 我的商店程序代码如下 My store procedure code as followsset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER 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 =@keywordif(@Hotel!='')beginSet @final = 'Dear Students HIMT Hotel Accommodation is ' +@Hotel+',' +@Phone+ ','+@Room+ ' by marine'select @finalend 当我按如下方式执行上述存储过程输出时 When i execute the above store procedure output as followsexec [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 followsDear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200 by marineBut i want the output as followsDear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200,Suryalodge24851524/9852012312,SingleNonAC 1000 DoubleAC 1600 by marinebecause 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 =@keywordshould 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 MyTableOPEN MYCURSORFETCH NEXT FROM MYCURSOR INTO @VWHILE @@FETCH_STATUS=0 BEGIN SET @STR = @STR + @SEP + @V SET @SEP = ';' FETCH NEXT FROM MYCURSOR INTO @V ENDCLOSE MYCURSORDEALLOCATE MYCURSORSELECT @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 bigintdeclare @firstname nvarchar(max)declare @lastname nvarchar(max)declare @final nvarchar(max)declare @endid bigintdeclare @last nvarchar(max)create table #temp2 (details nvarchar(max),id bigint)select @endid =count(employeeid) from MyEmployeesselect @id=1while(@id<@endid)beginselect @firstname=firstname,@lastname=LastName from MyEmployees where EmployeeID=@idset @final = @firstname+','+@lastnameinsert into #temp2(details,id) select @final,0set @id=@id+1endselect @last='Dear Students ' +STUFF((select ',' +CAST(details as nvarchar ) from #temp2 where id=0 for xml path ('')),1,1,'') +' by marine'select @last 这篇关于传递参数但输出未正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 09-16 06:58