Help for Using Sql server stored Proc Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help for Using Sql server stored Proc Variable

Author

Topic

joydeep1985
Starting Member

5 Posts

Posted - 2009-08-24 : 05:50:03

i am just explaining my problem for using sql server stored proc variable. i am new in stored proc programming

now namely i have an table called as airport

now i want to create a stored procedure where i am just passing the airport name as parameter to the stored procedure and then using that parameter.Then i fetch the Airport id using that Airport name and then using that airport id i m fetching all necessary details about airport using that airport id

the example goes like this

IF (OBJECT_ID('dbo.selectairport') IS NOT NULL)
DROP PROC dbo.selectairport
GO

create procedure selectairport
@airport_name varchar(20),
@airport_id varchar(20) output

as
select @airport_id=airport_id from airport where airport_name=@airport_name

select * from airport where airport_id=@airport_id
PRINT @airport_id

although this can be directly done as select * from airport where airport_id=@airport_id

but i m need to know how to use the variable @airport_id in this case so that i can use it in this same stored procedure itself as explained above.....

i need that exact way to put the airport_id into @airport_id variable and then use the @airport_id as a parameter to the next select statement

this is my for own purpose of learning how to do it ..

kindly help me out

With Regards

Joy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 06:17:23

Your example does what you want

Madhivanan

Failing to plan is Planning to fail

joydeep1985
Starting Member

5 Posts

Posted - 2009-08-24 : 06:20:22

but when i am giving the command

exec selectairport 'Calcutta'

it gives me the error

Msg 201, Level 16, State 4, Procedure selectairport, Line 0
Procedure or function 'selectairport' expects parameter '@airport_id', which was not supplied.

i am not sure that why this error is coming

dhjackal
Starting Member

42 Posts

Posted - 2009-08-24 : 06:28:22

I think you are looking for something like this ;

IF (OBJECT_ID('dbo.selectairport') IS NOT NULL)
DROP PROC dbo.selectairport
GO

create procedure selectairport
@airport_name varchar(20)
as
declare
@airport_id INT

select @airport_id=airport_id from airport where airport_name=@airport_name

select * from airport where airport_id=@airport_id
PRINT @airport_id

dhjackal
Starting Member

42 Posts

Posted - 2009-08-24 : 06:31:05

Your only passing the @airport_name variable into the proc. All other details are retreived from the airport table based on this value. The @airport_id doesn't need to be passed into the procedure but it does need to be declared.

Hope this makes sense.

dhjackal
Starting Member

42 Posts

Posted - 2009-08-24 : 06:32:54

And this is the correct way to create the proc.... (note the BEGIN - END)

IF (OBJECT_ID('dbo.selectairport') IS NOT NULL)
DROP PROC dbo.selectairport
GO

CREATE PROCEDURE selectairport
@airport_name VARCHAR(20)
AS
DECLARE
@airport_id INT

BEGIN
SELECT @airport_id=airport_id FROM airport WHERE airport_name=@airport_name

SELECT * FROM airport WHERE airport_id=@airport_id
PRINT @airport_id
END

joydeep1985
Starting Member

5 Posts

Posted - 2009-08-24 : 06:37:27

ya thanks that section solved my matter... i m new in this sql server programming ... so couldn't figure out the matter where to declare the variable....

i was declaring the variable outside the as block so i was getting error as it didnt needed to be passed on to the stored proc... but it needed to be declared....

so thats solved my matter thanks for helping out

dhjackal
Starting Member

42 Posts

Posted - 2009-08-24 : 06:38:34

No problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources