Function with local parameters

I am trying to write a function that returns different values regdaring to a
input value.
Why can't I write like this. What shoudl I do instead.
I want to be able to write a select Item from GetPermission ('user', 're')
Regards
/Magnus
alter function [dbo].[GetPermissions3]
(
@.UserName varchar(50),
@.ItemName varchar(50),
)
RETURNS TABLE
AS
declare @.objecttype int
if @.ItemType='RE' set @.objecttype=1
RETURN
(
If
select i.Item, o.[name] from ItemGroups i
full outer join Groups g
on g.ItemGroup = i.ItemGroup
full outer join Users u
on u.GroupName = g.GroupName
join Control.dbo.Object o on i.item = o.object and o.no=@.objecttype
where (UserName = @.UserName or i.ItemGroup = @.UserName) and ItemName =
@.ItemName
)Please have a look at the syntax used to create functions. You are
attempting to create an inline function which must not have a function body
and which must contain a single select query within the returns clause. If
you want to include logic that cannot be handled within a single select
statement, then you will need to use the more complex table-valued function
syntax.|||Thanks!
After some searching for complex table-valued function I found the syntax
necessary!
My simple test (if someone wants to know) became:
alter FUNCTION MBTest
( @.FirstColNumber int )
RETURNS @.MyTable TABLE
( FirstCol varchar(50),
SecondCol varchar(50) )
AS BEGIN
declare @.mynum varchar(50)
select @.mynum = case @.FirstColNumber
when 0 then 'zero'
when 1 then 'one' end
insert @.MyTable (FirstCol,SecondCol) select @.mynum,FirstName from
BookingsApril2006
RETURN END
Best Regards
/Magnus
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZhVjdhbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Please have a look at the syntax used to create functions. You are
> attempting to create an inline function which must not have a function
> body and which must contain a single select query within the returns
> clause. If you want to include logic that cannot be handled within a
> single select statement, then you will need to use the more complex
> table-valued function syntax.
>

Fun of INSTEAD OF UPDATE trigger

Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:

> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan