|
|
Posted by Gert-Jan Strik on 08/20/07 19:39
In addition to Erland's response: even if you could build such a
function, it would probably have very poor performance in comparison to
using the EXISTS clause inline in a query. My advice matches Erland's:
forget about it. Don't try to build queries with scalar UDF's for simple
logic that can easily be programmed inline (for example, using a view).
Gert-Jan
aCe wrote:
>
> Hi all,
> i wanna ask about dinamyc Function (FN).
> This is my table :
> CREATE TABLE [dbo].[unit](
> [unitid] [int] IDENTITY(1,1) NOT NULL,
> [unitname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [date_created] [datetime] NULL,
> [user_created] [int] NOT NULL,
> [date_modified] [datetime] NULL,
> [user_modified] [int] NOT NULL,
> CONSTRAINT [PK_unit] PRIMARY KEY CLUSTERED
> (
> [unitid] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> i want to make a FN that can check record exist or not.
> this is my FN:
> CREATE FUNCTION ufnCheckExists(
> @table varchar(255),
> @key varchar(255),
> @id int
> )
> RETURNS int
> AS
> BEGIN
> DECLARE @returnResult int
> IF( @id < 1 )
> BEGIN
> SET @returnResult = 0
> END
> ELSE
> BEGIN
> SET @returnResult =
> CASE
> WHEN EXISTS(
> SELECT * FROM @table WHERE @key = @id
> )
> THEN (1)
> ELSE (0)
> END
> END
>
> error msg :
> Msg 1087, Level 15, State 2, Procedure ufnCheckExists, Line 25
> Must declare the table variable "@table".
>
> is it possible to create it. or any suggestion.
> my purpose to create this, is i can reuse my function without creating
> ambiguous function.
>
> thx,
> ace
Navigation:
[Reply to this message]
|