SQL Function and SQL Query (1 Viewer)

[neoklis]

Hi guys,

All I am trying to do is to execute an SQL Query from SQL Server Management Studio. In one column I’m calling a simple SQL Function. When I execute the function from visual studio to test it, everything works fine. When I execute it from the query returns null . I ‘ll post the function and the Sql Statement in case I am missing anything.

Thank you

ALTER FUNCTION dbo.Is_A_FinalF(@xWorkGroup char(8),@xWorkCenter char(8),@xMrp char(3))
returns bit

as

begin

declare @return as bit

set @return=0

if (@xWorkGroup is not null) and @xWorkGroup<>''
select @return=AfinalCombo
from Work_Groups
where (WorkGroup=@xWorkGroup)
GROUP BY Work_Groups.WorkGroup, Work_Groups.AFinalCombo

if (@xWorkCenter is not null) and @xWorkCenter<>''
select @return=AfinalCombo
from Work_Groups
where (Workcenter=@xWorkCenter) and (Mrp_Controller=@xMrp)

return (@return)

end



The SQL Query Statement

select workcenter,WorkGroup,Mrp_Controller,AfinalCombo, dbo.is_a_finalf(workcenter,'','')
from Work_Groups

 

[SQL_Hell]

Hi there

I cant answer why it works from visual studio, but it looks like your input parameter on your function isn't big enough.

ALTER FUNCTION dbo.Is_A_FinalF[COLOR="Red"](@xWorkGroup char(8),@[/COLOR]xWorkCenter char(8),@xMrp char(3))
returns bit

@xWorkGroup is declared as char(8), but when you call the function:

select workcenter,WorkGroup,Mrp_Controller,AfinalCombo, dbo.is_a_finalf(workcenter,'','')

You are using Workcentre as the parameter value, 'workcentre' is a word with 9 chars.

 

[neoklis]

Thank for your reply, Actually the workcenter is the name of the field that i want to pass to the function as value. I dont want to pass to the function the word 'workcenter' but the value of the field named workcenter.

 

[MSAccessRookie]

ALTER FUNCTION dbo.Is_A_FinalF(@xWorkGroup char(8),@xWorkCenter char(8),@xMrp char(3))
returns bit

begin

...


end

The SQL Query Statement

select workcenter,WorkGroup,Mrp_Controller,AfinalCombo, dbo.is_a_finalf(workcenter,'','')
from Work_Groups

I am not sure that this is related to your problem, but I wonder if you have your parameters in the right order in the fuinction call.


The definition of the Function says that:

• P1 is called @xWorkGroup and is char(8)
• P2 is called @xWorkCenter and is char(8)
• p3 is called @xMrp and is char(3)

The call to the Function says that:

• P1 is set to the value of a field called WorkCenter
• P2 is Null
• p3 is Null

Shouldn't P1 be Null, and P2 be set to the value of the field called WorkCenter?

 

[neoklis]

You maybe right... I'll check it and i let you know tomorrow.. Thanks for your reply

 

[neoklis]

Yes my friend... thanks to your perceptiveness my function is working! I was passing the wrong parameter to the function..!

thanks..

 

[MSAccessRookie]

Yes my friend... thanks to your perceptiveness my function is working! I was passing the wrong parameter to the function..!

thanks..

Glad that we worked that out