CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Low Chee Mun
Low Chee Mun

Reputation: 610

my query doesnt work with my function

what i want to do is to perform simple query to exclude unnecessary data, due to some requirement i have to use function to perform it, lets take a look on my sample data and query

Sample data in my table

ID    |    TITLE    |
 1    |     A       |
 2    |     B       |
 3    |     C       |

my function

CREATE FUNCTION ufnTitleValue()
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @Val NVARCHAR(MAX)

SELECT @Val = STUFF((SELECT ''',''' + TITLE FROM [MyTable]
WHERE TITLE IN('A','B')
FOR XML PATH('')),1,2,' ')
SET @Val = @Val + ''''

IF @Val IS NULL
RETURN ''

RETURN @Val
END

result that returned by my function

 'A','B'

my problems is here, when i trying to exclude the data, it doesnt work

SELECT TITLE FROM [MYTable] WHERE TITLE NOT IN (ufnTitleValue())

Original Result

TITLE
A
B
C

Expected Result

TITLE
C

i am so confused that why my function is not work, what is my mistake ? where did i goes wrong?

Upvotes: 0

Views: 49

Answers (2)

Deepshikha
Deepshikha

Reputation: 10274

As pointed by @podiluska you can do it as:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnTitleValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnTitleValue]
GO

CREATE FUNCTION ufnTitleValue()
RETURNS TABLE
AS
return 
select TITLE  
FROM [MyTable]
WHERE TITLE IN('A','B')

Go

SELECT TITLE FROM [MYTable] WHERE TITLE NOT IN (select * from dbo.ufnTitleValue())

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Your function doesn't work with your table, because the function returns a string "'A','B'"

IN takes a list of values, either declared in code, or as a single column result from a query. It does not take a string.

So you are searching for entries in your table where the Title is not "'A','B'". And none of them are.

Change your function to return a table with a single column.

Upvotes: 1

Related Questions