Accept input parameter using function in T-SQL

Question

I have this script that executes, but I need it to take a user input instead of the setting the variable for @lastName.

CREATE FUNCTION displayCount() RETURNS INT
AS
BEGIN
    DECLARE @number INT, @lastName VARCHAR(40);
    SET @lastName = 'Ferrier';

    SELECT @number = COUNT(*) FROM Person.Person 
    WHERE LastName = @lastName;
    RETURN @number;
END   
GO

SELECT dbo.displayCount() AS howMany

I tried to mess with what I thought would work, but doesn't. I am getting and error

'Procedure or function dbo.diplayCount has too many arguments'

I'm sure it's something simple and with me being SO NEW, I can't see it. Thanks for the help!!

CREATE FUNCTION displayCount(@lastName VARCHAR(40)) RETURNS INT
AS
BEGIN
    DECLARE @number INT;   
    SELECT @number = COUNT(*) FROM Person.Person 
    WHERE LastName = @lastName;
    RETURN @number;
END   
GO

SELECT dbo.displayCount('Ferrier') as howMany

are you using SQL Server Management Studio?

CurseStacker
– CurseStacker

2018-09-12 00:06:58 +00:00
Commented Sep 12, 2018 at 0:06 — CurseStacker
– CurseStacker, Commented Sep 12, 2018 at 0:06
yes I am using SQL Server Management

Sam
– Sam

2018-09-12 00:10:19 +00:00
Commented Sep 12, 2018 at 0:10 — Sam
– Sam, Commented Sep 12, 2018 at 0:10

D-Shih · Accepted Answer · 2018-09-12 00:12:29Z

1

Your function looks so good, But I guess you need to use ALTER instead of CREATE because the function already exists in the DB.

you need to modify rather than create.

ALTER FUNCTION displayCount(@lastName VARCHAR(40)) RETURNS INT
AS
BEGIN
    DECLARE @number INT;   
    SELECT @number = COUNT(*) FROM Person.Person 
    WHERE LastName = @lastName;
    RETURN @number;
END  

GO

SELECT dbo.displayCount('Ferrier') as howMany

edited Sep 12, 2018 at 0:12

answered Sep 12, 2018 at 0:00

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

2 Comments

Sam Over a year ago

Sorry that was just a typo when typing the question

Sam Over a year ago

Yeah, I realized that at the same time. I hate being new, but it's a learning experience.

CurseStacker · Accepted Answer · 2018-09-12 00:11:31Z

0

In SSMS, expand [your database] > Programmability Folder > Functions folder > Table-valued Functions > Right click > New Inline Table-valued Function....

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
CREATE FUNCTION displayCount
(   
    @lastName   varchar(40)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT COUNT(*) AS howMany
    FROM Person.Person 
    WHERE LastName = @lastName
)
GO

Then call it by

SELECT howMany FROM dbo.displayCount('Ferrier')

answered Sep 12, 2018 at 0:11

CurseStacker

1,0898 silver badges19 bronze badges

2 Comments

HABO Over a year ago

Why have you chosen to change from the OP's scalar function to table-valued function? Either one can accept parameters.

CurseStacker Over a year ago

Thanks HABO, more like a hindsight on my part of not knowing the use of scalar functions. I've learned something new.

Collectives™ on Stack Overflow

Accept input parameter using function in T-SQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related