using pass the parameter in store procedure get the output

Question

0.00/5 (No votes)

See more:

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

SQL

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.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

SQL

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

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

SQL

exec [Shortcode_Accom] '2'

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 that what is the mistake in my above store procedure.

please help me
regards,
narasiman P.

[edit]Code block added - OriginalGriff[/edit]

Posted 27-Feb-14 20:21pm

Member 8054539

Updated 27-Feb-14 20:31pm

OriginalGriff

v2

Add a Solution

Comments

Sibasisjena 28-Feb-14 2:40am

I think, you want paging in your procedure .

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-02-27T21:20:00

Have a look at example:

SQL

--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

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

george4986 · Answer 2 · 2014-02-27T21:24:00

Solution 2

check this link
http://blog.sqlauthority.com/tag/sql-paging/[^]

Posted 27-Feb-14 21:24pm

george4986