Function doesn't accept expression as param 1

[Janoh]

Hi,

I made a user defined function that takes a varchar as a parameter.
fn_split('1,4,6')
where '1,4,6' is the varchar
It returns a table with the items splitted.

It works great, but when I try to use it in a select passing a field name it gives an error:

I have something like this

select st.*
from Store_Dept sd,
Store_Items si,
Store_Transactions st
where sd.Department_ID in (select Value from fn_split_list(si.Additional_Departments))

si.Additional_Departments contains a varchar as '1,2,3,4'
it gives me the error: Incorrect syntax near '.'
It drives me crazy. If I transmit a hardcoded list or a variable it works. If anything else, even the hardcoded list used in another function like CAST or Convert it gives me the error.
Did someone meet this
Thanks

Janoh

 

[nigelrivett]

Look at bol under create function

TABLE

Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed to table-valued functions.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Fuzemmo]

Do you get a syntax error if you just try to run your sub-select?

ie,
select Value from fn_split_list(si.Additional_Departments)

This is minor, but something that happens with my own functions and I'm not sure if its just a setting, or a convention that I need to get used to, but when I call a udf I need to pass the owner as part of the call. So for me to run your sub-select, I'd have to run...

select Value from dbo.fn_split_list(si.Additional_Departments)

... or something similiar.

Jim

 

[iker3000]

Could you give a short explanation what your selct is trying to do? dos si has only one row?
Why are your tables not joined in any way?

Your where clause could also be stated putting this into the from part of the sellect:

Store_Dept sd
INNER JOIN Store_Items si
ON si.Additional_Departments LIKE '%,' +
CAST(sd.Department_ID AS nvarchar) + ',%'

Iker

 

[Janoh]

If nigelrivett is right there's not much left to do.

If I run the subquery with hardcoded value (constant or @local_variable as nigelrivett said it executes fine). When I try to pass a table column as a parameter it gives me the error.

It also gives an error if I want to use another function that returns a type that my function accepts.

Is the same thing with stored procedures ? I mean can I use instead something like
where sd.Department_ID in (exec sp_split_list si.Additional_Departments)

?
thanks

 

[Janoh]

select st.Item_Id,
st.Item_Name,
st.Quantity,
st.Sale_Price,
st.Quantity * st.Sale_Price as Value,
cast(sd.Department_Name as varchar(5)) + ' %' as Comission,
st.Quantity * st.Sale_Price * (cast(sd.Department_Name as int)) / 100 as Comission_Value,
st.Purchase_Date,
-- @reportRowNr,
-- @startInterval,
-- @endInterval,
st.OID
from admin.Store_Dept sd,
Store_Items si,
admin.Store_Transactions st
where sd.Show = 0
and sd.Store_ID = 1
and st.Item_Id = si.Item_Id
and st.Store_ID = 1
and st.Store_ID = si.Store_Id
and st.Item_Id = si.Item_Id
and (sd.Department_ID in (select Value from fn_split_list(si.Additional_Departments))
or si.Sub_Department_ID = sd.Department_ID)
-- and st.Purchase_Date >= @startInterval and st.Purchase_Date <= @endInterval



here is the whole select if it helps.
I think I can't do this way.
Does anyone know, how to do a select on a stored proc's return ?

something like select * from (exec sp_vals @param)

?
thank you
Janoh

 

[iker3000]

Would this give the desired result?

SELECT
st.Item_Id,
st.Item_Name,
st.Quantity,
st.Sale_Price,
st.Quantity * st.Sale_Price as Value,
cast(sd.Department_Name as varchar(5)) + ' %' as Comission,
st.Quantity * st.Sale_Price * (cast(sd.Department_Name as int)) / 100 as Comission_Value,
st.Purchase_Date,
-- @reportRowNr,
-- @startInterval,
-- @endInterval,
st.OID
FROM
admin.Store_Transactions st
INNER JOIN Store_Items si
ON st.Item_Id = si.Item_Id
AND st.Store_ID = si.Store_Id
INNER JOIN admin.Store_Dept sd
ON ',' + si.Additional_Departments + ',' LIKE '%,' +
CAST(sd.Department_ID AS nvarchar) + ',%'
OR si.Sub_Department_ID = sd.Department_ID)
WHERE
sd.Show = 0
AND sd.Store_ID = 1
AND st.Store_ID = 1
--and st.Purchase_Date >= @startInterval and st.Purchase_Date <= @endInterval

Iker

 

[Janoh]

Thank you all the help.
I did a workaround. Firs I extracted the pairs si.Additional_Departments with the si.Item_Id into a temprorary table and then did this:

and sd.Department_ID in (select D_ID from #tmp_Dep_Id where Item_Id = st.Item_Id)

this solved my problem.


As for you Iker, thanks for the tip, but that won't work, because if I do it with LIKE %@ID% it may give me erroneous result.
for ex: i have a list of '1234,12345' and if my id is '12' then it will find me, but 12 is not equal 1234. That was the thing I tried to solve with the splitter function.

Thanks again for your help.

nigelrivett: you are right there. thanks

 

[iker3000]

That's why I wrot LIKE '%,'+ + ',%' (because the commas make it unique)

 

[Janoh]

Yes, but it still has the flaw if it is the first or the last in the list, and if you trick it with another 2 clauses, it can happen, that the list will contain just one element and that must be treated too.

Thanks for the tip, didn't see the commas.

 

[nigelrivett]

',' + @str + ',' like '%,' + @ID + ',%'

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Janoh]

yup nigelrivett, that's it. it works great this way.
Thank you all for the help