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
1 Like
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?

5

From a SQL database.

There is stored procedure that I call with:
FROM [X].[UDF_Table1](CONVERT(date, '2020-01-01'))

How can that be? That is not the format of:
Execute dbo.SomeProcedure @date = '2020-01-01'

Any idea?

6

UDF_...

Maybe time for some abductive reasoning?!

1 Like
7

This is a user-defined function call - not a stored procedure. You can create functions in SQL Server as either:

  1. Scalar function
  2. Inline table-valued function
  3. Multi-statement table-valued function

Lookup functions in BOL and review - there is a lot of information available.

8

One more thing - functions in SQL Server must be passed all parameters. You can define 'optional' parameters but to use the optional parameter you must specify 'default' for that parameter. For example:

Select * From dbo.MyFunction(parm1, default);

10

Thank You!