SQLTeam.com | Weblogs | Forums

How to create a stored procedure that requires input?

#1

Hello!

How can I create a stored procedure that requires an input from the user to be used?

Thanks!

#2

A stored procedure with a defined parameter and no default means that parameter is required.

 Create Procedure dbo.SomeProcedure
        @parm1 int
      , @parm2 int = 0
     As
...

In the above - @parm1 is required (no default) but @parm2 is optional because it has a default value.

If you try to call the above procedure:

Execute dbo.SomeProcedure;   --this fails, because @parm1 is required
Execute dbo.SomeProcedure 1;  --this works because @parm1 is provided
Execute dbo.SomeProcedure @parm1 = 1;  --this works
Execute dbo.SomeProcedure @parm2 = 1;  --this fails, @parm1 is required
Execute dbo.SomeProcedure @parm1 = 1, @parm2 = 1;  --this works
#3

Thanks but I was looking for something like MYPROCESS(). How can I design a stored procedure like that so that I run MYPROCESS(1)?

Thanks!

#4

you cant, it's no vb.net or c#. @jeffw8713 has given you everything short of the Microsoft documentation on how to run a stored procedure. Where are you running stored procedures from?