Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem creating function

Posted on 2014-02-05
10
Medium Priority
?
221 Views
Last Modified: 2014-02-05
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
0
Comment
Question by:bruno_boccara
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 39836003
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
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 total points
ID: 39836004
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39836006
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39836009
Ahh, yes I forgot you can't use dynamic SQL in a function either.
0
 

Author Comment

by:bruno_boccara
ID: 39836015
that's my principal problem, I can't use EXEC in a function.

so what should I do to go around this problem ?
0
 

Author Comment

by:bruno_boccara
ID: 39836023
I need a function, not a procedure ....
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39836076
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
 

Author Comment

by:bruno_boccara
ID: 39836093
yes it's something like that....

anyway thanks for your help
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39836098
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
 

Author Comment

by:bruno_boccara
ID: 39836117
I'm on MSSQL 2008
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question