passing parameters to FUNCTION

To pass parameters to functions:

dbo.YourFunction ( 'Parameter1', 1000 )

So in your SQL statement:

SELECT dbo.YourFunction (Field1, Field2) as FunctionOut, name, Address
FROM YourTable

SELECT y.*, dbo.udfSomeFunction(y.id, ...)
FROM dbo.yourtable y

-Paul.

i did tried passing it but im getting this error:

Server: Msg 155, Level 15, State 1, Line 8
'con_id' is not a recognized OPTIMIZER LOCK HINTS option.

Don't you hate it when that happens.  

Now can you post something useful, like the function and how you are callling it.

SELECT    
dbo.view1.id1, dbo.view1.sub_id, dbo.view2.name,
dbo.view1.holdername, dbo.view1.status,
dbo.view1.service_type, myfunction2.admin_name
FROM        
dbo.myfunction2() myfunction2
INNER JOIN
dbo.myfunction1(id1,sub_id)
myfunction1 ON myfunction2.admin_id = myfunction1.admin_id
INNER JOIN
dbo.view1
INNER JOIN
dbo.view2 ON
dbo.view1.id1 = dbo.view2.id1
AND
dbo.view1.sub_id = dbo.view2.sub_id ON
myfunction1.id1 = dbo.view2.id1
AND
myfunction1.sub_id = dbo.view2.sub_id

dbo.myfunction1(id1,sub_id)   <<<<< function passing values

Can we have the whole function (CREATE FUNCTION dbo.myfunction1 etc.) including the parameters?

This will also tell us if you are using a scalar function or whether it is returning a table.

as long as its fast and work i don't mind :)

it will return only 1 row which is what im looking

Ok, let me explain to you where I am coming from.  

You post an error message that reads:
Server: Msg 155, Level 15, State 1, Line 8
'con_id' is not a recognized OPTIMIZER LOCK HINTS option.

Now I cannot find any trace of con_id in your code, nor have you explained what it is and since it is pretty late in the day here, my mind reading capabilities are somewhat diminished.  That is why I need to see the function "myfunction1" (it also would not hurt if you also posted the function myfunction2)

Does that help you understand what I need and why?

You cannot join to a function that yields a result based on the value of a column in some table.  Think about it: each value fed into the function could return a different result set, and there could be (theoretically) millions of input rows, so you could be trying to do millions of joins.  For example:

SELECT a.col1
FROM tableA a
INNER JOIN dbo.someTableFunction(a.col2) AS b ON a.col2 = b.col2

For *every* value in a.col2, SQL would generate a new result set.


If it returns only a single result, you can use a subquery in the main SELECT:

SELECT a.col1, (SELECT TOP 1 col3 FROM dbo.someTableFunction(a.col2)) AS funcValue
FROM tableA a

--"TOP 1" is just to make sure that only 1 value is returned, and that SQL "knows" that,
--so it can end the function as quickly as possible.