Using a function on SELECT error

Question

Post reply

Using a function on SELECT error

luissantos

SSCertifiable

Points: 7391
More actions
January 13, 2013 at 10:02 am

#266975

Hello comunity
I have create this function to return total pairs:
CREATE FUNCTION [dbo].[fnQtRefCor]
(@bostamp varchar(25), @ref varchar(20), @cor varchar(50))
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = SUM(qtt) FROM bi
WHERE @bostamp = bostamp AND @ref = ref AND @cor = cor
and bi.ref <> ''
SET @retval = ISNULL(@retval, 0)
RETURN @retval
END
If i run this query:
SELECT SUM(qtt) FROM bi
WHERE bostamp = 'jm13010767476,323000001' AND ref = '2120910'
and (bi.ref <> '')
group by bi.bostamp,ref,cor
I obtain the correct value of : 24
if i run this select to test the result :
select [dbo].[fnQtRefCor] from bi where bi.bostamp = 'jm13010767476,323000001 '
This error appear :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.fnQtRefCor" could not be bound.
I can run this SELECT without error ?
Many thanks,
Luis Santos

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

luissantos SSCertifiable Points: 7391 More actions · Answer 1

Sorry, i forget to mencioned that the compatibility level is :

SQL SERVER 2000 (80)

Maybe the problem is here ??

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

luissantos (1/13/2013)
Hello comunity
I have create this function to return total pairs:
CREATE FUNCTION [dbo].[fnQtRefCor]
(@bostamp varchar(25), @ref varchar(20), @cor varchar(50))
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = SUM(qtt) FROM bi
WHERE @bostamp = bostamp AND @ref = ref AND @cor = cor
and bi.ref <> ''
SET @retval = ISNULL(@retval, 0)
RETURN @retval
END
If i run this query:
SELECT SUM(qtt) FROM bi
WHERE bostamp = 'jm13010767476,323000001' AND ref = '2120910'
and (bi.ref <> '')
group by bi.bostamp,ref,cor
I obtain the correct value of : 24
if i run this select to test the result :
select [dbo].[fnQtRefCor] from bi where bi.bostamp = 'jm13010767476,323000001 '
This error appear :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.fnQtRefCor" could not be bound.
I can run this SELECT without error ?
Many thanks,
Luis Santos

One thing I see is that you aren't passing in any parameters to the function.

Try the following with the proper values for the parameters.

select [dbo].[fnQtRefCor](@bostamp = ??, @ref = ??, @cor= ??);

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello Lynn Pettis

Yes, you are right i forget my parameters (bostamp,ref,cor)

Thanks for your reply

Luis Santos