stored procedure parameters - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure parameters

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-16 : 11:42:36

I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 12:29:33

Out parameters are also in parameters. They are in fact normal in parameter which can carry a value out of the procedure as well.

CREATE PROC MyProc
(
@a OUT
)
AS

SET NOCOUNT ON

E 12°55'05.25"
N 56°04'39.16"

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-16 : 12:37:59

But an out parameter is not required to be passed to the stored procedure call for it to work. I guess I was wondering if there were any other types of parameters besides IN parameters that must be passed to s stored procedure call...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 12:41:04

What makes you write that? Of course an OUT parameter needs to be supplied, unless you provide a default value for the parameter.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 12:42:40

[code]if exists (select * from information_schema.routines where routine_name = 'testa')
drop proc testa
go

create proc testa
(
@a int out
)
as
select getdate()
go

exec testa

go

exec testa 1

drop proc testa[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-16 : 12:43:20

[code]if exists (select * from information_schema.routines where routine_name = 'testb')
drop proc testb
go

create proc testa
(
@a int = null out
)
as
select @a, getdate()
go

exec testa

go

exec testa 1

drop proc testb[/code]

E 12°55'05.25"
N 56°04'39.16"

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-18 : 08:27:50

I guess I am misunderstanding the requirements for parameters. So any parameter, regardless of whether it is an in or out parameter, must be passed to the stored procedure if no default value is defined. Is this correct?

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 09:05:55

Yes.

E 12°55'05.25"
N 56°04'39.16"

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-18 : 09:09:02

Thank you, Peso. You have been very helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources