stored procedures

[mzim]

i have this problem in declaring parameter in stored proc.
btw i'm using mssql 2000

the usual thing to do is

@name varchar(50)

insert into sampletable values(@name)

but what i want is to declare a parameter inside a condition or declaring a parameter that are not global

@ident int

if @ident=1
[color=red]@names varchar(50)[/color]
begin
insert into table1 values(@names)
end return

if @ident=2
[color=red]@add varchar(50)[/color]
begin
insert into table2 values(@add)
end

how can i do that? bec. it throws an error if you will not provide a value to the parameter eventhough you will not using it. I just want to pass a value in a parameter if it will be needed.

thanks in advance for the help.

cheers
marzim

 

[TechGnome]

If you want to have a parameter w/o having to specify a value for it, you can set a default value for the parameter.

To do this, just add = def_Value after the parameter's type... like this:

@name varchar(50)  = '', -- will default to empty string if not supplied
@myID int = 0, -- defaults to 0
@someText varchar(500) = NULL -- defaultsto NULL

Tg

 

[mzim]

ok thanks tg.

is there some other way that i could declare some of my parameters inside a condition?

if @ident=2
@bday datetime
begin
insert into table2 values(@bday)
end