SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inline table valued function, translation from book


Inline table valued function, translation from book

Author
Message
rho_pooka
rho_pooka
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 205
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?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39239 Visits: 38529
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85745 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rho_pooka
rho_pooka
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 205
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39239 Visits: 38529
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
rho_pooka
rho_pooka
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 205
Even better, thnx again!!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85745 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85745 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rho_pooka
rho_pooka
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 205
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search