Inline table valued function, translation from book

Question

Inline table valued function, translation from book

rho_pooka

Ten Centuries

Points: 1385
More actions
January 11, 2014 at 9:19 am

#287204

Greetings! On chapter four looking at inline table valued functions. Can someone translate this code into English? I see this at the beginning of a lot of code posted in the forum, and now in the 70-461 book, but have a hard time deciphering it. I don't understand the 'IF' in the parentheses nor the "IS NOT NULL DROP"
IF OBJECT_ID('HR.GetManagers', 'IF') IS NOT NULL DROP FUNCTION HR.GetManagers;
GO
CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE
AS
RETURN
.....--goes on to create CTE
As I read it, it looks like:
If HR.GetManagers, or IF (why if?) exists, delete them, and create a new HR.Getmanagers function that requires an input of @empid that is an INT. Once putting in the INT for @empid, the function will use it help create a CTE, then I can query the CTE. Is that close?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

rho_pooka (1/11/2014)
Greetings! On chapter four looking at inline table valued functions. Can someone translate this code into English? I see this at the beginning of a lot of code posted in the forum, and now in the 70-461 book, but have a hard time deciphering it. I don't understand the 'IF' in the parentheses nor the "IS NOT NULL DROP"
IF OBJECT_ID('HR.GetManagers', 'IF') IS NOT NULL DROP FUNCTION HR.GetManagers;
GO
CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE
AS
RETURN
.....--goes on to create CTE
As I read it, it looks like:
If HR.GetManagers, or IF (why if?) exists, delete them, and create a new HR.Getmanagers function that requires an input of @empid that is an INT. Once putting in the INT for @empid, the function will use it help create a CTE, then I can query the CTE. Is that close?

First question, have you taken the time to look up the function OBJECT_ID in Books Online? Here is a link to the function OBJECT_ID: http://msdn.microsoft.com/en-us/library/foof89286db-440f-4218-a828-30881ce3077a.aspx

The 'IF' in this function call referes to the type for inline function. The IF statement is determining if the function already exists in the current database and if it does to drop the function.

The function is then created (or if dropped it is recreated).

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

rho_pooka (1/11/2014)
Greetings! On chapter four looking at inline table valued functions. Can someone translate this code into English? I see this at the beginning of a lot of code posted in the forum, and now in the 70-461 book, but have a hard time deciphering it. I don't understand the 'IF' in the parentheses nor the "IS NOT NULL DROP"
IF OBJECT_ID('HR.GetManagers', 'IF') IS NOT NULL DROP FUNCTION HR.GetManagers;
GO
CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE
AS
RETURN
.....--goes on to create CTE
As I read it, it looks like:
If HR.GetManagers, or IF (why if?) exists, delete them, and create a new HR.Getmanagers function that requires an input of @empid that is an INT. Once putting in the INT for @empid, the function will use it help create a CTE, then I can query the CTE. Is that close?

Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.

--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)

rho_pooka Ten Centuries Points: 1385 More actions · Answer 3

Jeff Moden (1/11/2014)
Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.

I am unfamiliar with books online I use google a ton, usually get referred to microsoft's technet site, w3, or stack. I will do more research before I post, it's just hard to understand the technical vernacular sometimes, hence my translation request.

I've bookmarked the page, thanks for the reference!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

rho_pooka (1/11/2014)
Jeff Moden (1/11/2014)
Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.
I am unfamiliar with books online I use google a ton, usually get referred to microsoft's technet site, w3, or stack. I will do more research before I post, it's just hard to understand the technical vernacular sometimes, hence my translation request.
I've bookmarked the page, thanks for the reference!

Getting to Books Online is as easy as pressing Shift F1 in SSMS (SQL Server Management Studio).

rho_pooka Ten Centuries Points: 1385 More actions · Answer 5

rho_pooka

Ten Centuries

Points: 1385

January 12, 2014 at 7:22 am

#1679419

Even better, thnx again!!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

rho_pooka (1/11/2014)
Jeff Moden (1/11/2014)
Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.
I am unfamiliar with books online I use google a ton, usually get referred to microsoft's technet site, w3, or stack. I will do more research before I post, it's just hard to understand the technical vernacular sometimes, hence my translation request.
I've bookmarked the page, thanks for the reference!

Google is good as are the web sites you posted. One of the "problems" with certs (except the old MCM series) is that they don't necessarily teach you technique. They say that you should have X amount of experience before trying for the cert and they don't explain all the code that they might use that is common for people with the required experience. If you don't already know a particular bit of code used in the book, they expect you to study the T-SQL objects that they've used on your own. The explanation for the use of and operands of OBJECT_ID is pretty clearly spelled out in the MS TechNet entry for OBJECT_ID (the web version of "Books Online") and I recommend that you take the opportunity to study the function in depth, which will cause you to remember it much better than getting an answer to your question on a forum.

Again, I'm not saying such a thing to chastise or make fun of you. It's a recommendation to help turn you into an SQL Server Ninja instead of someone that just memorized enough to pass a test by rote (which is my other problem with certs other than the MCM series, which requires you to pass a practical exam).

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Lynn Pettis (1/12/2014)
rho_pooka (1/11/2014)
Jeff Moden (1/11/2014)
Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.
I am unfamiliar with books online I use google a ton, usually get referred to microsoft's technet site, w3, or stack. I will do more research before I post, it's just hard to understand the technical vernacular sometimes, hence my translation request.
I've bookmarked the page, thanks for the reference!
Getting to Books Online is as easy as pressing Shift F1 in SSMS (SQL Server Management Studio).

Being the lazy sot that I am :-P, you can get away with just pressing the {f1} key. Pressing and holding the {Shift} keys requires burning about 8 times as many calories as just pressing {f1}. :hehe:

--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)

rho_pooka Ten Centuries Points: 1385 More actions · Answer 8

Jeff Moden (1/12/2014)
rho_pooka (1/11/2014)
Jeff Moden (1/11/2014)
Although it may seem so, I'm not being sarcastic at all when I ask you, do you know what "Books Online" is? People who ask the kinds of questions that you've just asked usually have no clue what it is and it's a huge help even for an old salt like me.
I am unfamiliar with books online I use google a ton, usually get referred to microsoft's technet site, w3, or stack. I will do more research before I post, it's just hard to understand the technical vernacular sometimes, hence my translation request.
I've bookmarked the page, thanks for the reference!
Google is good as are the web sites you posted. One of the "problems" with certs (except the old MCM series) is that they don't necessarily teach you technique. They say that you should have X amount of experience before trying for the cert and they don't explain all the code that they might use that is common for people with the required experience. If you don't already know a particular bit of code used in the book, they expect you to study the T-SQL objects that they've used on your own. The explanation for the use of and operands of OBJECT_ID is pretty clearly spelled out in the MS TechNet entry for OBJECT_ID (the web version of "Books Online") and I recommend that you take the opportunity to study the function in depth, which will cause you to remember it much better than getting an answer to your question on a forum.
Again, I'm not saying such a thing to chastise or make fun of you. It's a recommendation to help turn you into an SQL Server Ninja instead of someone that just memorized enough to pass a test by rote (which is my other problem with certs other than the MCM series, which requires you to pass a practical exam).

I appreciate your explanation as to "the way" in becoming a SQL Server Ninja, and your comment on technique intrigued me. I think technique is directly correlated to efficiency. So, what would be your "best practice" for becoming fluent in TSQL, and SQL in general? Say for someone that has limited access to SSMS on a daily basis?

Thankfully I enjoy my time learning all things SQL because it challenges me and seems to have infinite depth (one of these days I'll even understand what the guests on SQL Down Under are talking about). Hands down I dig it...

Thanks again for the thoughtful response, and the SQL community in general.