BenjyAdams
asked on
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]([RandomCo de])) 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
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]([RandomCo
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER