MS SQL Function issue
Posted on 2013-01-17
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)
DECLARE @FBCount INT
SELECT @FBCount = COUNT(DISTINCT fbuser) FROM dbo.FBtable WHERE fbcode = TeamID
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?