.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Passing using parameter in store procedure get the top3 record from the table

Posted By: narasiman     Posted Date: February 28, 2014    Points:5   Category :Sql Server


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 2 Hotelname,Phoneno,Roomrate from Tb_Accommodation where Active <> 'D' and Sno = @Keyword

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.com for Accommodation'
end

else
begin
set @final = 'Invalid Keyword. Sorry try again with valid keyword or visit www.marineinstitute.com.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


But i want when i pass the parameter 1 in the store procedure i want the top3 record from the table. i want output 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


similarily when i pass the parameter 2 in the store procedure i want the Next 3 record from the table. i want 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 getting the above output. in my store procedure what changes i have to made.

please help me.

regards,
narasiman

Responses

No response found. Be the first to respond this post
Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend