problem creating function

Hello,
I want to create a very "simple" function, but I'm getting an error message:
La variable de table "@TABLE_NAME" doit être déclarée.

in english : the table data "TABLE_NAME" must be declare.

I don't understand, this data is declared in the fonction arguments ...

any idea ??

best regards.

CREATE FUNCTION FN_GET_LIBELLE (@TABLE_NAME VARCHAR(50),@PK_COLUMN_NAME VARCHAR(50),@PK_DECIMAL_VALUE INT,      @PK_STRING_VALUE VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
      -- Declare the return variable here
      DECLARE @LIBELLE VARCHAR(100)
      
      IF (@PK_DECIMAL_VALUE IS NOT NULL and @PK_DECIMAL_VALUE > 0 )
      BEGIN
            SELECT @LIBELLE = LIBELLE FROM @TABLE_NAME WHERE @PK_COLUMN_NAME = @PK_DECIMAL_VALUE
      END
      ELSE
      IF (@PK_STRING_VALUE IS NOT NULL AND @PK_STRING_VALUE NOT LIKE '')
      BEGIN
            SELECT @LIBELLE = LIBELLE FROM @TABLE_NAME WHERE @PK_COLUMN_NAME = ''+@PK_STRING_VALUE+''
      END
      
      RETURN @LIBELLE

END
GO
bruno_boccaraAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Seems like you are passing the table name, in that case you need to use dynamic sql , but unfortunately you SQL functions wont support dynamic sql, you need to change this to a stored procedure
0
 
Lee SavidgeCommented:
You cannot use a table name as a variable.

Also, consider changing this:
 IF (@PK_DECIMAL_VALUE IS NOT NULL and @PK_DECIMAL_VALUE > 0 )

to

 IF (isnull(@PK_DECIMAL_VALUE, 0) > 0)

Similarly:

IF (@PK_STRING_VALUE IS NOT NULL AND @PK_STRING_VALUE NOT LIKE '')

to

IF (isnull(@PK_STRING_VALUE, '') <> '')

Don't use NOT LIKE without the wildchar % as it won't work.


If you try and use dynamic SQL to build your query as a string you'll find that you won't be able to properly return the value as it will not be in scope.

What are you hoping to achieve?
0
 
Brian CroweDatabase AdministratorCommented:
You can't use a variable as a table name in this fashion.  You would need to use dynamic sql and form the sql query as a string before execution.  Unfortunately dynamic sql can't be used inside a function since they are intended to be deterministic.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lee SavidgeCommented:
Ahh, yes I forgot you can't use dynamic SQL in a function either.
0
 
bruno_boccaraAuthor Commented:
that's my principal problem, I can't use EXEC in a function.

so what should I do to go around this problem ?
0
 
bruno_boccaraAuthor Commented:
I need a function, not a procedure ....
0
 
Lee SavidgeCommented:
You are coming up against the limitations of the product I'm afraid. You need dynamic SQL which you can't do in a function. As there isn't any other way of doing the dynamic SQL, then you must use a stored procedure.

Why does this need to be a function? I am assuming it is part of another select statement and you're trying to retrieve an arbitrary value, from an arbitrary column in an arbitrary table.
0
 
bruno_boccaraAuthor Commented:
yes it's something like that....

anyway thanks for your help
0
 
Brian CroweDatabase AdministratorCommented:
One alternative is using an extended stored procedure from within your function...

http://technet.microsoft.com/en-us/library/aa214418(v=sql.80).aspx
0
 
bruno_boccaraAuthor Commented:
I'm on MSSQL 2008
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.