Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inline table valued function, translation from book Expand / Collapse
Author
Message
Posted Saturday, January 11, 2014 9:19 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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?
Post #1530042
Posted Saturday, January 11, 2014 12:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 23,243, Visits: 31,938
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).



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)
Post #1530052
Posted Saturday, January 11, 2014 4:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530068
Posted Saturday, January 11, 2014 7:36 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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!
Post #1530074
Posted Sunday, January 12, 2014 6:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 23,243, Visits: 31,938
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).



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)
Post #1530092
Posted Sunday, January 12, 2014 7:22 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
Even better, thnx again!!
Post #1530097
Posted Sunday, January 12, 2014 10:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530120
Posted Sunday, January 12, 2014 10:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
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 , 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}.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530121
Posted Monday, January 13, 2014 8:33 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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.
Post #1530522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse