T-SQL: COUNT does not count properly

I need to perform the following query inside of a SQL function, in SQL Server 2008.

Note: @ncuota, @ccod, @cfecha and @rut are the arguments of the function, set to the proper variable types.

DECLARE @cpagos int
SELECT @cpagos = COUNT(Pago_NPago)
FROM Pago_Costa AS p INNER JOIN
(
SELECT Cancela_NPago, Cancela_TipoPago
FROM Cancela_Costa AS cc INNER JOIN Cuota_Costa AS cuota
ON Cancela_ConcCod = Cuota_ConcCod AND Cancela_ConcFecha = Cuota_ConcFecha
WHERE Cuota_NCuota = @ncuota AND Cancela_ConcCod = @ccod AND Cancela_ConcFecha = @cfecha
) AS c
ON c.Cancela_NPago=p.Pago_NPago AND c.Cancela_TipoPago = p.Pago_TipoPago
WHERE Pago_AlumnoRut = @rut
GROUP BY Pago_AlumnoRut

However, @cpagos gets either a NULL value (if I use GROUP BY) or 0 (if I don't), but if I execute the query as a regular query and not inside the function, it works properly and returns the value 2. What is wrong here? Thanks in advance.

TABLE DEFINITIONS:

Pago_Costa:
- Pago_NPago PK bigint
- Pago_TipoPago PK bit
- Pago_AlumnoRut FK nvarchar(9)
- Pago_Monto bigint
- Pago_FPago date
- Pago_TipoDoc smallint
- Pago_RefBoleta FK bigint
- Pago_Re开发者_C百科fTipoPago FK bit
- Pago_Obs nvarchar(200)

Cuota_Costa
- Cuota_NCuota PK smallint
- Cuota_ConcCod FK PK smallint
- Cuota_ConcFecha PK FK int
- Cuota_Valor biging

Cancela_Costa
- Cancela_NPago PK FK bigint
- Cancela_TipoPago PK FK bit
- Cancela_NCuota PK FK smallint
- Cancela_ConcCod PK FK smallint
- Cancela_ConcFecha PK FK int
- Cancela_Monto bigint
- Cancela_Cheque FK nvarchar(20)

FUNCTION. I'll be using a modified version of the function that I'm using to test the query.

FUNCTION verificaPagoCuotas
(
@rut nvarchar,
@ncuota smallint,
@ccod smallint,
@cfecha int
)
RETURNS int
AS
BEGIN
DECLARE @cpagos int, @valor bigint
-- @valor is the real variable to be returned, but right now we're testing so it'll be @cpagos
SET @cpagos = (SELECT COUNT(Pago_NPago)
FROM Pago_Costa AS p INNER JOIN
(
SELECT Cancela_NPago, Cancela_TipoPago
FROM Cancela_Costa AS cc INNER JOIN Cuota_Costa AS cuota
ON Cancela_ConcCod = Cuota_ConcCod AND Cancela_ConcFecha = Cuota_ConcFecha AND Cancela_NCuota = Cuota_NCuota
WHERE Cancela_NCuota = @ncuota AND Cancela_ConcCod = @ccod AND Cancela_ConcFecha = @cfecha
) AS c
ON c.Cancela_NPago=p.Pago_NPago AND c.Cancela_TipoPago = p.Pago_TipoPago
WHERE Pago_AlumnoRut = @rut
RETURN @cpagos
... -- More irrelevant code
END

You need to narrow down the problem. First, you should use Set instead of Select. Set will throw an error if more than one row is returned whereas Select will not. This is why including the Group By did not throw an error when it was added even though it should. I would exclude the Group By.

Now, iterations to try:

1. What is the count when use Count(*) instead of Count(Pago_Npago)?
2. What is the count when you only use the inner join?
3. What is the count when you exclude Where Pago_Alumnorut = @Rut
4. What is the count when you change the Inner Join to a Left Join and change the count to Count(Cancela_NPago)?

When you eliminate the impossible, whatever remains, however improbable, must be the truth.

If you run the query directly in SSMS and using the function using identical parameters, and the results are different, then query in the function must be different than the one you ran in SSMS.

The answer is too simple. My problem is that I declared just "nvarchar", but I didn't specify the length. When I changed to nvarchar(8), the problem fixed automatically. Thank you for your enormous help!