Passing more parameters to stored procedure

Siva Myneni

Hi All, I have a question for you? I have to pass 7 parameters to the procedure. for some parameters i am not passing a value. how to do this?

sivamyneni

andyharman

You need to supply defaults for each of your optional parameters:

create procedure MyProc
@Param1 varchar(10) = null,
@Param2 int = null,
@Param3 date = null
as begin
   set nocount on
   --do stuff
   return(0)
end
go

exec MyProc
exec MyProc 'hello', 123, '1 feb 2007'
exec MyProc 'world'
exec MyProc @Param3 = '1 feb 2007'

Hope this helps. Andy

Siva Myneni

Hi I tried that one i am not getting proper result because none of my field contaains null value.so i am not getting the desired.

sivamyneni

Elina Blank

Try this: If Your procedure is like following: ALTER PROCEDURE myProcedure @Param1 int = 0, @Param2 int = 0, @Param3 int, @Param4 int = 0, @Param5 int = 0, @Param6 int, @Param7 int AS -- Procedure code In order to call it (say, from another sp): exec myProcedure @Param3 = @PassedParam1, @Param6 = @PassedParam2, @Param7 = @PassedParam3 Note: Initialize parameters in the original procedure if you are not sure, the procedure will get those parameters passed to it. (In the case above, if you will not pass one of the @Param3, @Param6, or @Param7) the error will happen. Hope that helps.

Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O