Create function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create function

Author

Topic

tasha0228
Starting Member

5 Posts

Posted - 2011-06-20 : 06:47:09

Hi :)

My problem is i tried to create this create function, but i get these following errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "b.stoelnummer" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.stoelnummer" could not be bound.
Msg 1767, Level 16, State 0, Line 2

so obviously something is wrong, but I am not really good in create function so I am not shure what is wrong.

create function dbo.Checkstoel(@sstoelnummer numeric(3), @bstoelnummer numeric(3)) returns bit AS
BEGIN
declare @founder bit
set @founder=case when exists (select S.Stoelnummer
from Bezetting B right outer join Uitvoering U on B.Voorstellingsnummer=u.Uitvoeringsnummer
and B.Voorstellingsnummer=U.Uitvoeringsnummer
right outer join Stoel S on B.Stoelnummer=S.Stoelnummer
where S.Zaal=U.Zaal)
then 1 else 0 end
return @founder
END
go
alter table Uitvoering add constraint CHK_Checkstoel CHECK(dbo.Checkstoel(b.stoelnummer , s.stoelnummer)=0)

What I am trying to this, i have this table Bezetting, and i want to check, if the seat exists in table stoel (it means seat) The table Bezetting are like the reservations, so that is why you need to check if the seat is available, or if it even exists, before you try to make a reservation on it,

Any help that can solve this problem, or any other ideas of how it can be solved, is appreciated.
thanx in advance :)

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-06-20 : 07:44:47

Well for starters (no pun intended), you're passing in the parameters @sstoelnummer and @bstoelnummer but you don't appear to be using them. Perhaps you're referring to them incorectly and this could be causing your problem later on.

The error specifically refers to the query not recognizing the items b.stoelnummer and s.stoelnummer - this could be in your ALTER TABLE command where you're referring to these fields. Your aliases b and s are in a different query and therefore outside of the scope of the ALTER TABLE statement.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-20 : 07:48:29

You are trying to pass b.stoelnummer and s.stoelnummer to the function. s and b are defined in the function.
You can only pass values from the table you are checking so I guess one stoelnummer.
Why the right outer joins in the function?

In fact you aren't even using the values passed in the function.

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-20 : 07:54:35

Maybe pass @stoelnummer and @Zaal

create function dbo.Checkstoel(@stoelnummer numeric(3), @Zaal numeric(3)) returns bit AS
BEGIN
declare @founder bit
select @founder = 0
select @founder=1 where exists
(select *
from Bezetting B
join Stoel S
on B.Stoelnummer=S.Stoelnummer
where S.Zaal=@Zaal)
and b.bstoelnummer = @stoelnummer
return @founder
END
go

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

tasha0228
Starting Member

5 Posts

Posted - 2011-06-20 : 09:39:34

So if i cant pass those values on, which values can i pass on?

The right outer join is because I need to check if the seat already has a reservation or not, the seat table, is going to have seats that does not have a reservation, so i'll be getting the seats with a reservation, while i also want those that doesn't have a reservation.

I need to make this project using sql and access for school, and this is one of the business rules, but i cant figure it out.

the business rule is: the stoelnummer(seat) from a bezetting(reservations) has to be in the table stoel

so its like a check for another table, but i have no idea how this is possible D:

tell me if the function thingy is a good idea, before i start correcting it, and it still wont work, or if you have any ideas at all, it would mean allot

thank you

quote:
Originally posted by nigelrivett

You are trying to pass b.stoelnummer and s.stoelnummer to the function. s and b are defined in the function.
You can only pass values from the table you are checking so I guess one stoelnummer.
Why the right outer joins in the function?

In fact you aren't even using the values passed in the function.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources