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

General SQL Server Forums

New to SQL Server Programming

store procedure

Author

Topic

skaswani
Starting Member

24 Posts

Posted - 2008-11-05 : 01:58:13

Dear All

how are you all,

i want to know what is there any need of using OUT PARAMETER in SP i.e

if i create procedure

create proc search_emp

@empno int

as

select ename,sal from emp where empno = @empno

or

i may create it like this

create proc search_emp
@empno , @ename output, @sal output
as

select ename, sal from emp where empno = @empno

(some thing like this, i have not tested this code as i am in office!)

advise please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:00:19

you can assign values to @sal variable inside procedure and get that value returned from procedure by specifying it as OUT.

something like

DECLARE @retval datatype
EXEC search_emp val1,val2,@retval OUT

SELECT @retval

make sure @retval and @sal are of same type

skaswani
Starting Member

24 Posts

Posted - 2008-11-05 : 02:41:24

Dear
visakh16

actully i want to ask that what is diff. b/w both procedures

with OUT para and with no para

regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:44:53

As i said earlier if you specify OUT parameter you can get values returned through it outside procedure. If you're not declaring it as OUT, you can still pass value through it to procedure but wont be able to return values from procedure through it.

skaswani
Starting Member

24 Posts

Posted - 2008-11-05 : 03:25:29

i am bit confuse yet!

my Sir told me that

IF I use OUT Para it will display a Record

and

if I dont use Out para i can use it for more rows i.e for Data Grid,

which means if i Not Use OUT para even my values can be shown on form

am i right and clear on my concept!

i need your input on this

thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 03:46:12

check this out

http://msdn.microsoft.com/en-us/library/ms187926.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources