• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

MS SQL Function issue

Hi guys,

I'm new to SQL functions, and have been using them create a few "computed column" in a table for an online "competition" generating points score.

I have an issue in that the function is constantly returning a count of 0, when it should be responding back with a number...

I have used the following script to create the function..

CREATE FUNCTION [dbo].[FBCount] (@TeamID NVARCHAR)
RETURNS NVARCHAR
AS BEGIN
    DECLARE @FBCount INT

    SELECT @FBCount = COUNT(DISTINCT fbuser) FROM dbo.FBtable WHERE fbcode = TeamID

    RETURN @FBCount
END

And added ([dbo].[FBCount]([RandomCode])) to the computed column formula - where "RandomCode" is a 20 character random string - an example of this is OVU9ANYL0CBQ0NBVMFIL.  Randomcode is a column of NVARCHAR(255)

If I just try a sql "select" using SELECT COUNT(DISTINCT fbuser) FROM dbo.FBtable WHERE fbcode = 'OVU9ANYL0CBQ0NBVMFIL' it works fine.

So where am I going wrong??? is it something to do with quotes?

Thanks
0
BenjyAdams
Asked:
BenjyAdams
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
your input type is nvarchar, which is equivalent to nvarchar(1)  it should be nvarchar(255)

CREATE FUNCTION [dbo].[FBCount] (@TeamID NVARCHAR(255) )
RETURNS int
AS BEGIN
    DECLARE @FBCount INT

    SELECT @FBCount = COUNT(DISTINCT fbuser) FROM dbo.FBtable WHERE fbcode = TeamID

    RETURN @FBCount
END
0
 
BenjyAdamsAuthor Commented:
Bingo - thanks....so simple...but had stumped me :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now