CODEWITHSUNDEEP
sqlsql-serverdatabasefunction
Ian K
Ian K

Reputation: 91

Parameter passing issue in SQL Server

I have written a function in T-SQL that is supposed to take a string value and return any row with a similar value in the 'Course Taken' column. Here is the function:

enter code here
USE [alumni_project1]
GO
/****** Object:  UserDefinedFunction [dbo].[Course_Search]    Script Date: 24-Mar-21 7:45:04 PM 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Course_Search]
(
@course varchar(255)
)
RETURNS TABLE
AS
RETURN
    SELECT *
    FROM Alumni
    WHERE Course_taken like '%@course%';

However, when I try to use the function, it produces no results. This is how I called the function:

DECLARE @c  VARCHAR(255)
SET @c = 'comp'
SELECT * FROM dbo.Course_Search(@c)

NOTE: If I use a regular SELECT statement, it returns the correct output, which is two rows where Course_taken= 'Computer Science'. How does one solve this issue?

Upvotes: 0

Views: 42

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

This line:

WHERE Course_taken like '%@course%';

Should be like this

WHERE Course_taken like '%'+@course+'%';

Upvotes: 1

Thom A
Thom A

Reputation: 95554

'%@course%' isn't doing what you think it is. It's looking for rows that contain the literal string '@course' not the value in the parameter @course. SQL isn't a scripting language, it doesn't take the value of a parameter/variable is inject it into a literal.

You need to concatenate '%' either side of the parameter:

CREATE FUNCTION [dbo].[Course_Search] (@course varchar(255))
RETURNS TABLE AS
RETURN
    SELECT *
    FROM Alumni
    WHERE Course_taken LIKE '%' + @course + '%';

Upvotes: 1

Related Questions