在表格记录中如下
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 Afor我上面的商店程序有什么错误。 请帮帮我 问候, 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/ [ ^ ]
更多推荐
使用传递存储过程中的参数获取输出
发布评论