Post reply

Determine database dynamically inside a UDF

  • September 1, 2008 at 6:46 am

    #211925

    Can anybody help me to find a solution for this?

    I have different databases for each customers. Each database is having common table called 'JOB', which is having two common fields called 'Job_id' and 'rec_name'.

    Irrespective of database I will get a partcular job id and database name from my application. I need to find the Rec_name.

    I have to run the scripts for correponding database to find out it. The problem is, I am using a UDF. I have to use UDF only no store procedures. I am passing this job_id and Database name as parameters of this UDF.

    How can I use this database dynamically inside the UDF?

    ex:

    Select * from @database_name.dbo.job (NOLOCK)

    Please help.

    Thanks in advance

  • September 1, 2008 at 6:50 pm

    #865786

    iam777here4u (9/1/2008)

    The problem is, I am using a UDF. I have to use UDF only no store procedures.

    Why not?

    The problem is that this can't be done in a UDF because you can't use EXEC (except on xp's) in a UDF which means you can't use dynamic SQL in a UDF. A stored procedure is the only way to go.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply