Setting a SQL function parameter default to all

Question

Ok so I have a large query that has 4 parameters in it..

CREATE FUNCTION [dbo].[Doorsdoorsdoors]
(
@start datetime, 
@end datetime, 
@day varchar,
@doornumber int
)
RETURNS TABLE AS RETURN (

SELECT bla bla bla....

What I want to do is set their default parameters to all (*)

So when I use the select statement I can use default so it returns all results like below:

SELECT * FROM Doorsdoorsdoors (default,default,default,default)

I would imagine it is something like this:

CREATE FUNCTION [dbo].[Doorsdoorsdoors]
(
@start datetime LIKE '%', 
@end datetime LIKE '%', 
@day varchar LIKE '%',
@doornumber int LIKE '%'
)
RETURNS TABLE AS RETURN (

SELECT bla bla bla....

Or possibly done in the SELECT * FROM Doorsdoorsdoors (default,default,default,default) with something like this..

SELECT * FROM Doorsdoorsdoors ('%','%','%','%')

You mean you want to retrieve without filtering on any of the 4 at all? — shree.pat18
– shree.pat18, Commented Mar 19, 2014 at 9:15
Yes so i want the default value in the function to be all(*) so when i create that function and then call it using a select statement i can use defaults to return all the values — Matt
– Matt, Commented Mar 19, 2014 at 9:20
Yes, but the actual where clausewill be important, as you might want to look at passing NULLs as default — Adriaan Stander
– Adriaan Stander, Commented Mar 19, 2014 at 9:34
Have a look at this SQL Fiddle Is this what you have in mind? — Adriaan Stander
– Adriaan Stander, Commented Mar 19, 2014 at 9:38

MikkaRin · Accepted Answer · 2014-03-19 09:33:53Z

1

CREATE FUNCTION [dbo].[Doorsdoorsdoors]
    (
    @start datetime = NULL --default value, 
    @end datetime = NULL --default value, 
    @day varchar =NULL --default value,
    @doornumber int = NULL --default value
    )
    RETURNS TABLE AS RETURN (

select * from Doorsdoorsdoors where ((@start is NULL) OR (start=@start))
AND ((@end is NULL) OR (start=@end)) AND....
)

Call procedure with all defaults:

SELECT * FROM Doorsdoorsdoors (default,default,default,default)

edited Mar 19, 2014 at 9:33

answered Mar 19, 2014 at 9:17

MikkaRin

3,08422 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Matt Over a year ago

I dont want the function parameters to have values, i want the default values within the parameter to be all (*) then when i use call the procedure with all defaults it returns all the values.

Matt Over a year ago

get error Must declare the scalar variable "@start". when i run the select part

MikkaRin Over a year ago

Why you run select part if it's part of the function?

MikkaRin Over a year ago

Did you fill WHERE with all of parameters? Or you just copy paste without even reading?

MikkaRin Over a year ago

There is no END statement in my sample of query. Error is on your side. Find place where you use END

|

Matt · Accepted Answer · 2014-03-19 13:48:17Z

0

Try the following

CREATE FUNCTION [dbo].[Doorsdoorsdoors]
      (
      @start datetime = NULL,
      @end datetime = NULL, 
      @day varchar = NULL,
      @doornumber int = NULL
      )
      RETURNS TABLE AS RETURN (

      SELECT bla bla bla....

      WHERE ((@start is NULL) OR (default=@start))
      AND ((@end is NULL) OR (default=@end))
      AND ((@day is NULL) OR (default=@day))
      AND ((@doornumber is NULL) OR (default=@doornumber))

Then try calling it with

      SELECT * FROM Doorsdoorsdoors (default,default,default,default)

or

      SELECT * FROM Doorsdoorsdoors (default,default,default,'123')

edited Mar 19, 2014 at 13:48

Matt

15.1k28 gold badges109 silver badges161 bronze badges

answered Mar 19, 2014 at 12:15

LaLa

3012 silver badges18 bronze badges

1 Comment

Matt Over a year ago

i get the error **Incorrect syntax near the keyword 'default'.** when running this.

Collectives™ on Stack Overflow

Setting a SQL function parameter default to all

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related