Is it possible to have a function without a parameter in t-sql?

5
votes

I am being forced to put a @noparameter varchar(1)=null in this function? How do I create a function to return a table which has no parameters!


ALTER FUNCTION [dbo].[DropDownIndividuals](@noparameter varchar(1)=null)
RETURNS
    @IndividualsList TABLE(
          Case_Number VARCHAR(11)
         ,LastName    VARCHAR(100)
         ,FirstName   VARCHAR(100)
         ,Midlename   VARCHAR(100)
         ,FullName    VARCHAR(100) 
        )
AS  
BEGIN
       INSERT INTO @IndividualsList
       SELECT DISTINCT
       cast(Case_Number as varchar(10))as Case_Number
      ,[Lastname]
      ,[Firstname]
      ,[Middlename]
      ,rtrim([Lastname]+ ' '+ [Firstname]) as FullName
      FROM [MHMODSSQL1P].[ODS].[dbo].[Contact]
      WHERE [LastName] is not null and [FirstName] is not null
      UNION ALL SELECT null,null,null,null,null
      ORDER BY [LastName]

      RETURN 

END;
3
tsql
There is no reason to insert the data into a temp table just to retun it. Return it directly or even better write a View. – Magnus
@Mangus: Strictly speaking, it doesn't look like he's returning anything. – Adam Robinson
Friendly note... it isn't necessary to prepend your title with tags. – user1228
@Adam actually it is the correct syntax for a Table-Valued UDF. But it wuld be better to write an inline UDF msdn.microsoft.com/en-us/library/ms189294.aspx – Magnus
@Mangus: My mistake; you're right. In any case, it actually seems like a view would be better than an inline-udf in this case, since there are no parameters. – Adam Robinson

3 Answers

9
votes
FUNCTION [dbo].[DropDownIndividuals]()

You can just use open and close brackets to define function without parameters, Assuming you are using SQL server.

4
votes

You should be a able to do it with something like this:

ALTER FUNCTION [dbo].[DropDownIndividuals]()

But since a table-valued function is essentially a parameterised view, you might as well just use a view rather than a TVF with no parameters:

CREATE VIEW [dbo].[DropDownIndividuals]
AS
SELECT -- etc
1
votes

As Sachin quite rightly gave the answer:

FUNCTION [dbo].[DropDownIndividuals]()

It's important to note that even though the function doesn't have parameters, you should still call the function with empty parentheses, else you'll get an error such as [The multi-part identifier "dbo.DropDownIndividuals" could not be found].

SELECT dbo.DropDownIndividuals()