question

Sanfooor avatar image
Sanfooor asked

Using 'if exists' in function

normally we can use 'if exists' statement in storedprocedure, for example, we can write the following in the storedprocedure: Create Test_schm.USP_TEST1 ( @O_No numeric(18,0) ) As SET NOCOUNT ON; If exists (Select O_No from Test_schm.TEST2 Where O_No=@O_No) Begin Select O_No,O_Type,O_Field from Test_schm.TEST2 Where O_No=@O_No End Else Begin Select O_No,O_Name from Test_schm.TEST1 Where O_No=@O_No End but how can we use 'if exists' statement in function?
tsqlfunctions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

grammy avatar image grammy commented ·
Hi I'm trying to do something similar and getting errors - any chance you could post up your solution?
0 Likes 0 ·
Beandon10 avatar image
Beandon10 answered
It looks like your statement doesn't have a return type. You should be able to find information for correcting the syntax at http://msdn.microsoft.com/en-us/library/ms186755.aspx From your statement, it looks like you are trying to create a multi-statement table-valued function, so that would be a good place to start.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sanfooor avatar image Sanfooor commented ·
ok.. I read your link, but I couldn't solve my problem. how can I write that using multi-statement table-valued function.
0 Likes 0 ·
Sanfooor avatar image Sanfooor commented ·
it's working now, Thank you
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
You use it in the same way. When you want to test for the existence of a row in a record set the use IF EXISTS. check the recommendations in Books Online - http://msdn.microsoft.com/en-us/library/ms188336.aspx. Whether you put it into a procedure or a function is your choice..
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sanfooor avatar image Sanfooor commented ·
I tried that many times but it gives me an errors, Can you write example for that
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
what error do you get? You need to provide more information about your problem so we can be more specific
0 Likes 0 ·
Sanfooor avatar image
Sanfooor answered
I wrote it like this: Create FUNCTION Test_schm.USP_TEST1 ( @O_No numeric(18,0) ) As Begin If exists (Select O_No from Test_schm.TEST2 Where O_No=@O_No) Select O_No,O_Type,O_Field from Test_schm.TEST2 Where O_No=@O_No Else Select O_No,O_Name from Test_schm.TEST1 Where O_No=@O_No End I'm getting this error: Incorrect syntax near 'AS'. Expecting ID
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.