UDF execution through a CLR

Question

UDF execution through a CLR

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

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)

Muhammad Furqan Default port Points: 1461 More actions · Answer 3

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?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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)