Solved

problem creating function

Posted on 2014-02-05
10
214 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 250 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 250 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now