Dinamyc Function

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

Re: Dinamyc Function

aCe (acerahmat@gmail.com) writes:
> i want to make a FN that can check record exist or not.
> this is my FN:
> CREATE FUNCTION ufnCheckExists(
>...
> 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.

Simply forget it. This is T-SQL, this is not C#. While code reuse is a
virtue in T-SQL, it is not as big virtue as in an traditional programming
langauge. Specifically, it's very rarely a good idea to parameterise on
the table name.

For a longer discussion on dynamic SQL, see this article on my web
site: http://www.sommarskog.se/dynamic_sql.html.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dinamyc Function

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

Re: Dinamyc Function

On Aug 20, 3:27 am, aCe wrote:
>.
> my purpose to create this, is i can reuse my function without creating
> ambiguous function.
>>

You are looking to employ modern concepts of programming in
in sql. It is like expecting to find a copy of the New York Times
among the dinosaurs in the Jurassic period. This is a totally
unrealistic expectation. Sql is fine for plodding thru mountains
of data, that is what it was designed for. It is not for things
that require agility and specificity, in other words it is a
poor language for application development. It is quite foolish
to request a dinasour when what you really want is a bird.
You will find an example of the type of function your looking
for within a database system here:

http://beyondsql.blogspot.com/2007/08/dataphor-creating-supe r-function.html

Sql mavens can take heart that IT will treat then better than
history treated the dinosaur :)

www.beyondsql.blogspot.com

Re: Dinamyc Function

steve wrote:

> Sql is fine for plodding thru mountains
> of data, that is what it was designed for. It is not for things
> that require agility and specificity, in other words it is a
> poor language for application development.

Yes, but so what? SQL is the database layer. It is, as you say, good
at being a database layer. It is not so good at being an application
layer, which is why you generally use something else (Crystal, Access,
VB, or what have you) for the application layer instead (though certain
portions of the business logic may be pushed down into stored procedures
for efficiency; SQL is good enough that this is reasonable to do).

http://www.penny-arcade.com/comic/2006/09/05

And then your own product seems to be schizophrenic: do you intend it
to be a database layer, application layer, both? Many of your articles
pertain to abstract operations that are equally applicable to any table,
such as "dense rank when sorted by ". Now if it were an
application-specific thing, such as "hook up to
in this front-end screen I've laid out", then that would make sense;
and in fact Crystal, Access, VB, etc. have long offered such features.

But you seem to be suggesting data-centric abstractions, the sort of
things that the next version of SQL Server is liable to offer in a
simple fashion. And I want it to offer them. I don't want common
data-centric abstractions reduced from 20 lines to 10 lines; I want
them reduced from 20 lines to 1 line. And, in fact, for the "dense
rank" example, SQL Server 2005 has DENSE_RANK() which does just that.

You also mention scaling, here and there, and generally seem to wave
it off with "we'll address that last". If you intend to hawk your
product as an improved database layer, that won't fly very well.