使用传递存储过程中的参数获取输出

本文介绍了使用传递存储过程中的参数获取输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在表格记录中如下

In table record as follows

1 Satarlodge 24745734/9840175805 SingleNonAC 500,Double AC 1000 A 2 Sarvanalodge 24151212/9790578502 SingleNonAC 600 Double AC 1200 A 3 Suryalodge 24851524/9852012312 SingleNonAC 900 Double AC 1600 A 4 Ashokalodge 24851212/9912215420 SingleNonAC 800 Double AC 1800 A 5 Kalpnalodge 24851211/9945225120 SingleNonAC 500 Double AC 1300 A 6 Manorlodge 24854121/9855652122 SingleNonAC 200 Double AC 1400 A

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER proc [dbo].[Shortcode_Accom] (@Keyword varchar(10)) 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 TOP 3 * 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+ '- Visit www.marineinstitute for Accommodation' end else begin set @final = 'Invalid Keyword. Sorry try again with valid keyword or visit www.marineinstitute.SMS marine xxx to 56100. Eg marine Accommodation1 to 56100' end commit tran

当我执行上面的存储过程输出如下

when i execute the above store procedure output as follows

exec [Shortcode_Accom] '1'

1 Satarlodge 24745734/9840175805 SingleNonAC 500,Double AC 1000 A 2 Sarvanalodge 24151212/9790578502 SingleNonAC 600 Double AC 1200 A 3 Suryalodge 24851524/9852012312 SingleNonAC 900 Double AC 1600 A

我在执行时传递2作为参数。

similiarily when i pass the 2 as parameter in my execution.

exec [Shortcode_Accom] '2'

我希望从表中接下来的3条记录。输出如下

i want the next 3 records from the table. output as follows

4 Ashokalodge 24851212/9912215420 SingleNonAC 800 Double AC 1800 A 5 Kalpnalodge 24851211/9945225120 SingleNonAC 500 Double AC 1300 A 6 Manorlodge 24854121/9855652122 SingleNonAC 200 Double AC 1400 A

for我上面的商店程序有什么错误。 请帮帮我 问候， narasiman P 。 代码块添加 - OriginalGriff [/ edit]

for that what is the mistake in my above store procedure. please help me regards, narasiman P. [edit]Code block added - OriginalGriff[/edit]

推荐答案

看看示例： Have a look at example: --declare variables DECLARE @tmp TABLE (myID INT) DECLARE @counter smallint; DECLARE @id INT; --insert sample data SET @counter = 1; WHILE @counter < 50 BEGIN SET @id = RAND() *100 INSERT INTO @tmp (myID) SELECT @id AS myID WHERE @id >0 AND @id NOT IN (SELECT myID FROM @tmp) SELECT @counter = COUNT(myID) FROM @tmp END; --proper query SELECT TOP(3) RowNo, myID FROM ( SELECT ROW_NUMBER() OVER(ORDER BY myID) AS RowNo, myID FROM @tmp ) AS T WHERE RowNo>=3 ORDER BY RowNo

在我看来，你需要使用 ROW_NUMBER（） [ ^ ]功能改为 ID ，因为某些数据不存在（可由用户，管理员等删除）。 ）。 以下数据已经生成（在这种情况下）：

In my opinion, you need to use ROW_NUMBER()[^] function instead ID, because some data could not exists (could be deleted by user, admin, etc.). The following data has been produced (in this case) :

3 7 4 8 5 10

查看此链接 blog.sqlauthority/tag/sql-paging/ [ ^ ]