Post reply

UDF execution through a CLR

  • July 23, 2008 at 11:45 pm

    #847530

    Jeff,

    Perhaps in my attempt to demonstrate an overly simplistic TSQL Example, I made it far to simple. The OP wants to SELECT a value from any column in a table, which is passed as a variable into a output variable. I was simply using a universal example from sys.objects to show that you can use TSQL and not CLR to get the value out of any column dynamically with a Stored Procedure, which is the same as the implementation in CLR would be, only you don't use CLR.

    Obviously you are correct object_id() is much better, and object_name() would be better for getting a name from an id. That wasn't the intent of my example though. I intended to show that CLR isn't necessary to set a variable to the value of any column dynamically from a table. The sys.objects table will exist in any SQL 2005 database for any schema, and is just a simplified example maker for this.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • July 24, 2008 at 4:40 am

    #847620

    Thanks, Jonathan...

    --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)
    Intro to Tally Tables and Functions

  • July 25, 2008 at 12:06 am

    #848219

    may be i am not competent enough (oh! i am sure i am not)

    can you please tell me how can we use this stored procedure in some view? I don't think that i need to post my complete database schema here, but business requirement is such that i have to have value of that particular column as a "column" in a view and that view is part of a group of other views.

    how can we use sp in a view?

  • July 25, 2008 at 6:41 am

    #848336

    Muhammad Furqan (7/25/2008)

    can you please tell me how can we use this stored procedure in some view? I don't think that i need to post my complete database schema here, but business requirement is such that i have to have value of that particular column as a "column" in a view and that view is part of a group of other views.

    how can we use sp in a view?

    Muhammad, you are correct in that for practical purposes, a SQL UDF cannot use Dynamic SQL (there are a couple of ways, but they are not very practical, IMHO), but a CLR UDF effectively can. And yes, dynamic SQL would seem to be a requirement for a UDF to dynamically select a column based on a name passed to it. But,...

    I think that this misses how SQL is designed to be used. The way that this is usually done is to have the source view return all of the columns and then have the caller either do the dynamic SQL or (even better) simply specifiy the column(s) to return in the Select column list, rather than as a parameter.

    That is because in SQL, the true way to pass parameters from a query statement through a View is implicitly through the column list and the specifications of the Where clause.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 25, 2008 at 5:52 pm

    #848732

    Jonathan Kehayias (7/23/2008)

    Jeff,

    Perhaps in my attempt to demonstrate an overly simplistic TSQL Example, I made it far to simple. The OP wants to SELECT a value from any column in a table, which is passed as a variable into a output variable. I was simply using a universal example from sys.objects to show that you can use TSQL and not CLR to get the value out of any column dynamically with a Stored Procedure, which is the same as the implementation in CLR would be, only you don't use CLR.

    Obviously you are correct object_id() is much better, and object_name() would be better for getting a name from an id. That wasn't the intent of my example though. I intended to show that CLR isn't necessary to set a variable to the value of any column dynamically from a table. The sys.objects table will exist in any SQL 2005 database for any schema, and is just a simplified example maker for this.

    Yeah... sorry about that... I was in a hurry and skimmed... I gotta stop doing that. Thanks for the feedback.

    --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)
    Intro to Tally Tables and Functions

Viewing 5 posts - 16 through 19 (of 19 total)

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