December 2, 2009 at 2:30 am
Hi,
I am trying to get a udf to work, essentially i create a table variable which i then populate from a stored procedure. I would then like to use this table to filter some data in the next table variable. However when I try to run this I get:
Msg 137, Level 16, State 1, Procedure udf_abs2, Line 24
Must declare the scalar variable "@tblA".
But I have already declared the table variable at the top of he code!
ALTER function [dbo].[udf_abs2]
(@username int)
returns @abs TABLE
(abstract_id int, user_abstract_id varchar(50), region_id int)
as
begin
--declare @username varchar(50)
--set @username = '\alicek'
declare @tblA Table
(account varchar(50), type1 varchar(50), priv varchar(50), mapped varchar(50), perm varchar(50),[group1] AS (substring([perm],charindex('\',[perm])+(1),(2))))
insert into @tblA (account, type1, priv, mapped, perm) exec xp_logininfo @username , 'all'
--select * from @tblA
--where [group1] = '2'
insert @abs
(abstract_id, user_abstract_id, region_id)
SELECT ABSTRACT.ABSTRACT_ID, ABSTRACT.USER_ABSTRACT_ID, REL_ABSTRACT_REGION.REGION_ID
FROM ABSTRACT INNER JOIN
REL_ABSTRACT_REGION ON ABSTRACT.ABSTRACT_ID = REL_ABSTRACT_REGION.ABSTRACT_ID INNER JOIN
@tblA ON REL_ABSTRACT_REGION.REGION_ID = CAST(@tblA.group1 as INT)
return
end
Many thanks for your help I am quick stuck on this one.
Oliver
December 2, 2009 at 3:37 am
Hi,
OK I think I have tracke it down to refering to a table variable column as @tbl.column1
why is it not possible to do this
I would like to create an inner join between this table and an actual table inside the database
eg:
SELECT ABSTRACT.ABSTRACT_ID, ABSTRACT.USER_ABSTRACT_ID, REL_ABSTRACT_REGION.REGION_ID
FROM ABSTRACT INNER JOIN
REL_ABSTRACT_REGION ON ABSTRACT.ABSTRACT_ID = REL_ABSTRACT_REGION.ABSTRACT_ID
-- INNER JOIN @tblA ON REL_ABSTRACT_REGION.REGION_ID = CAST((@tblA.column1) as INT)
I have tried to do this, but I get the error that the subquery is not allowed to return more than 1 value
SELECT ABSTRACT.ABSTRACT_ID, ABSTRACT.USER_ABSTRACT_ID, REL_ABSTRACT_REGION.REGION_ID
FROM ABSTRACT INNER JOIN
REL_ABSTRACT_REGION ON ABSTRACT.ABSTRACT_ID = REL_ABSTRACT_REGION.ABSTRACT_ID
-- INNER JOIN @tblA ON REL_ABSTRACT_REGION.REGION_ID = CAST((select group1 from @tblA) as INT)
If anyone can help it would be great,
Oliver
December 2, 2009 at 5:41 am
oliver.morris (12/2/2009)
OK I think I have tracke it down to refering to a table variable column as @tbl.column1why is it not possible to do this
Just the way the language works. You can only refer to the actual name of a table variable at the point that you specify it in the from clause. It must then be aliased for all other references. See the bolded modifications
insert @abs (abstract_id, user_abstract_id, region_id)
SELECT ABSTRACT.ABSTRACT_ID,
ABSTRACT.USER_ABSTRACT_ID,
REL_ABSTRACT_REGION.REGION_ID
FROM ABSTRACT
INNER JOIN REL_ABSTRACT_REGION ON ABSTRACT.ABSTRACT_ID = REL_ABSTRACT_REGION.ABSTRACT_ID
INNER JOIN @tblA A ON REL_ABSTRACT_REGION.REGION_ID = CAST(A.group1 as INT)
Couple other comments:
Watch the performance. Multi-statement table valued functions can be problematic if they're joined in with other tables in later queries
Perhaps put the cast to int in the definition of the computed column, then you don't need it in the join. Not going to make much difference here as there's no index, but it is good practice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 6:31 am
Gail,
THANK YOU I understand what I did wrong now, many thanks for the help. I take your point over the length - however the variable table is going to be very small - max 15 rows and at this stages it is only to prove that it can work.
Again many thanks,
Oliver
December 2, 2009 at 7:51 am
Sorry,
I have now included this in a UDF and I get the error :
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
what can I do to resolve this - can I not have an SP inside a function- sorry if this is really basic stuff.
Thanks for the help its turning out to be a very long week!
Oliver
Code:
create function [dbo].[udf_abs3]
(@username varchar(100))
returns @abs TABLE
(abstract_id int, user_abstract_id varchar(50), region_id int)
as
begin
declare @tblA Table
(account varchar(50), type1 varchar(50), priv varchar(50), mapped varchar(50), perm varchar(50),[group1] AS CAST((substring([perm],charindex('\',[perm])+(1),(2)))as int))
insert into @tblA (account, type1, priv, mapped, perm) exec xp_logininfo @username , 'all'
insert @abs
(abstract_id, user_abstract_id, region_id)
SELECT ABSTRACT.ABSTRACT_ID, ABSTRACT.USER_ABSTRACT_ID, REL_ABSTRACT_REGION.REGION_ID
FROM ABSTRACT INNER JOIN
REL_ABSTRACT_REGION ON ABSTRACT.ABSTRACT_ID = REL_ABSTRACT_REGION.ABSTRACT_ID
INNER JOIN @tblA A ON REL_ABSTRACT_REGION.REGION_ID = A.group1
return
end
December 2, 2009 at 8:19 am
Turn it into a stored procedure?
UDFs are not allowed to have side-effects (ie they may not change the structure of the database). Since procedures can, it would seem that a proc can't be called from a function.
What are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 8:36 am
Thanks for the reply.
Basically the requirement is to dynamically filter a set of abstracts based on the permissions associated with the login. Basically the website will request these results and pass the current users login e.g. home\oliver
So a user is mapped to some groups in active directory which are mapped into groups in SQL login. For example home\oliver is mapped to groups europe, US, australia.
I use the xp_logininfo sp to return all the results of valid abstracts associated with the groups that the login is associated with. Then run this against all the abstract results that have been tagged with various regions.
Therefore to do this I create a table variable to store all the permission path's of login then join this to the permissions associated with abstracts to pull back all the permission abstracts for that login.
Do you think a stored procedure would be better for this?
Thanks once again,
Oliver
December 2, 2009 at 8:37 am
PS - I am not trying to change the stucture of the data but simply filter a UDF based on a sp.
Thanks!
December 2, 2009 at 8:45 am
oliver.morris (12/2/2009)
PS - I am not trying to change the stucture of the data but simply filter a UDF based on a sp.
Doesn't matter. For all SQL knows, that proc could do anything and could even be modified after the udf is created to have side effects. Hence the limitation.
I suggest just using a straight stored proc to do this, combined with whatever uses the udf did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2009 at 9:46 am
Many Thanks,
Got this working as a sp without issue.
I understand now why the UDF wont let you run a sp inside it.
Many Thanks, you are a star.
Oliver
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply