T-SQL Database Development Stored Procedures (3 of 4)

3. T-SQL Database Development Stored Procedures (3 of 4)

Hey Now,

This is the third T-SQL post of four to provide a stellar understanding of database development using T-SQL.
We'll discuss Stored Procedures. Stored Procedures are very common & have many strengths. There are many other ways to access data such as ORM (Object Relationship Models) however the overwhelming majority of my experience has been with Stored Producers. Stored Procedures are located in the Object Explorer file tree when the database node is expanded then the Programmability node is expanded we can expand the Stored Procedures node.

3. Stored Procedures
  a. Create
  b. Database Definition (Use)
  c. Comments
  d. Common Settings
  e. Begin End
  f. Alter / Modify
  g. Drop
  h. Rollback
  i. Grant Permission
  j. Execute SP
  k. Parameters

a. Creating SP's
Creating a SP is some nice code for us to continue with. Below is an example of how to create an SP named p_getNames which will select name from our table [newTablePeopleName]. Once the script is placed in the Query window and executed the sp will be created.
-- Following Script will Create sp named p_getNames to select all records from table newTablePeopleName
CREATE PROCEDURE p_getNames
AS
Select nameOfPerson
From [newTablePeopleName]
Additional items we can add to SP's can be: Database Definition, Comments, Check if Exists Drop, Common Settings, Begin End, Grant Permissions, Execute SP, GO, Rollbacks

b. Database Definition - At the top of the script SP's can include a USE command to define the database
Use [databaseName]
GO

c. Comments
---- Comments below are some examples
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 09:23:57 ******/
-- =============================================
-- Create p_getNames stored procedure
-- =============================================
-- ======================================================
-- Author: <Catto>
-- Create date: <2.13.10>
-- Description: <Select Names nameOfPerson from table newTablePeopleName>
-- =======================================================
-- ALTER p_StoredProcuresName

d. Common Settings (Three) Included in many scripts toward the top:
ANSI_NULLS - Good settings for select & null and retuning zero
--- Example of setting ANSI_NULLS
SET ANSI_NULLS ON
GO
QUOTED_IDENTIFIER - deals with single & double quotes.
-- example of setting QUOTED_IDENTIFIER
SET QUOTED_IDENTIFIER ON
GO
No Count - Option Used after 1st begin in SP Example below:
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

e. BEGIN END - Often used in SP's, after the AS & before the last GO
-- ex. of BEGIN & END in SP scrip
CREATE PROCEDURE p_getNames
AS
BEGIN
SELECT nameOfPerson
FROM [newTablePeopleName]
END
GO

f. Alter SP's - Once the proc is created if there are any changes we can alter the SP. Below is an example how we could alter a proc:
-- ex. Following Script will Alter the sp named p_getNames to select the tableid column not the name from table newTablePeopleName
ALTER PROCEDURE p_getNames
AS
BEGIN
Select newTableid
--Select nameOfPerson
From [newTablePeopleName]
END

g. DROP - Dropping a procedure is basically deleting the object from the db. In the following example we'll drop the sp we created:
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 11:06:34 ******/
DROP PROCEDURE [p_getNames}
GO

h. Rollback
Rollback - Script can be included at the bottom of the sp all commented out as an old stone chisel type of a version control to have a backup of a working copy of the SP.

i. Grant Permissions
Granting permissions is a less common task yet it’s still important. Once a procedure is created depending how the database is setup we may need to grant permissions to users. In sp’s we often will grant the ‘execution’ permission.
-- Grant Execute Permission for the proc to a user
GRANT EXECUTE on p_getNames to dbUserName

j. Execute Stored Procedures
Once the proc is created to use it we will execute the procedure. One way to execute the proc is in Object Explorer we can right click on the sp an click execute. This will open an ‘Execute Permission’ window kinda like a design view window, if there are parameters we can enter in values for the parameters then click the OK button and a new query window will open with the script created. We could also open a query window and use the following script.

-- Simple script to execute sp
Exec p_getNames

k. Parameters – Parameters are values passed into the stored procedure. This give us powerful ability in the procedure to query data. Often times we want to query data depending on values we provide. It’s critical to the use of SP's is to be able to pass data into the SP. Below is an example of a Stored Procedure with a parameter passed into it.
-- Example displays the use of passing in a parameter into the SP
ALTER PROCEDURE p_getNames
(
@tableID numeric
)
AS
BEGIN
Select nameOfPerson
From [newTablePeopleName]
WHERE newTableid = @tableID
END

Stored Procedures are very common & powerful. They are not EF4.0 or other ORM’s but they are very prominent way of accessing data. It's great for us to have an understanding, develop & use them.

What do you think of Stored Procedures?

Here are the other post in the series:
1 T-SQL Database Development IDE & Queries (1 of 4)
2 T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Later,
Catto

T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

2. T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Hey Now,

This is the second T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll discuss the difference between the Query Editor Window vs. Design View. We can accomplish many tasks by using either, its good know now the strengths & limitations of both. The fundamental difference between the two is Query Editor Window is just text & the design view is more of a GUI feel.

1. Design View
   1.a Creating Tables
   1.b Modifying Tables
   1.c Building Queries
2. Query Editor Window
   2,a General
   2.b Execute & Parse
   2.c Highlighting Text
   2.d Scripting

1. Design View - The design view's strength is that it has a relatively nice GUI graphical user interface. This in my opinion is nice especially when a person has less experience developing in T-SQL. Two good times to use the Design view when creating or editing a table or when querying within a single database & building a query (user can click & drag vs. typing specific syntax).

1.a Creating Tables – We can create a table in design view by right clicking the ‘Table’ node in Object Explorer & selecting ‘New Table’. In this view it’s a nice little interface where we can name the columns, select the data types, create keys, and create indexes & much more. When you save the table it will be reflected in the list of tables. It’s a nice way to start building table since its more point & clicky rather than all syntax, text & code.

1.b Editing Tables – We can also edit tables in design view which is a common task. In Object Explorer right click the name of the table & select edit, this will open up the design view so we’re able to alter the name or datatype of a column. We could always use the query editor window and use a script such as ‘Alter Table … lots of code…etc’. but it can be a little more complex. For example if you are adding a constraint on a column in a table it may be a little easier to accomplish this in the design view rather than a script in a query window.

1.c Building Queries – Building Queries is another powerful use of the design window, especially for those whose may have less experience in the query window. To open design view for building a query in Object Explorer right click on the table name then select ‘Edit Top 200’. This will open up the results grid with the top 200 records. Then there are four panes: Diagram, Criteria, SQL & Results which we can open by pressing Ctrl + 1, Ctrl + 2, Ctrl + 3. When opening design view for a table the default is just the results pane in a grid, then we add the other three to assist in building the query. The diagram pane is nice since we can graphically see the table, we can simply place a check in the checkbox of the column we’d like to select & it will build the syntax of the query in the SQL pane. Similarly we can use the Criteria pane to build the query. We can select the column we’d like to select in the column column. We can easily sort the query even by descending with the sort order column. We can use the alias column to create an alias quickly. We can create a where clause with the filter column. As we see the develop queries using the diagram & criteria panes. When starting to use joins or changing query types the design view accomplishes this without having to know the syntax.

2. Query Editor Window - Every task in T-SQL can be accomplished by scripts in a Query Editor Window. It is more powerful than the design view. For example if you are joining tables from two different servers we could not accomplish this in the design view, we'd have to use a Query Window because we can only add tables in design view from one server.

2. a General – The Query window is more powerful than design view in many ways. A simple way to open a new query window is in Object Explorer when you select/highlight the database then press Ctrl+N. Another way is to right click on a table & click ‘Select Top 1000 Rows’. The window is all text then there is also a results pane which can be displayed toward the bottom of the window. The results will display a grid of the results. The results grid can be resized by default it slits the screen, typically I prefer to resize it so the results screen takes up a smaller amount of screen real-estate about the lower quarter of the screen.

2.b Execute & Parse - F5 is execute & Ctrl + F5 is parse When in the query window an import task to know is to be able to execute the script. We can execute a script by clicking the ‘!Execute’ button or pressing F5. A common practice I perform is to parse my query before I run them which will check for syntax errors. Parsing will take half a second & can be performed by Ctrl+F5 or clicking the check mark.

2.c Highlighting Selected Text – We are able to highlight or select text in a query editor window then execute or parse only the selected text. This is a very useful feature since often times the scripts we are working become long & we only want to execute a portion of the script. Another time to highlight text is when there is a longer script & I want to execute a stored procedure, we can select/highlight the proc’s name then execute only the proc not the entire script.

2.d Scripting - Scripting is a very useful feature in SSMS. For example if we wanted a script to create a current table in Object Explorer we could right click on a table --> script table as --> Create --> New Query Window. This will display the script with the syntax to create this table. We can also script other objects such as Stored Procedures. Another scripting option is to script a table as a insert into, update & drop.

We really just covered some very important fundamentals of T-SQL including the difference between design view & Query Editor Window. In the next section we’ll check out Stored Procedures.


Below is the other posts in the series:
T-SQL Database Development IDE & Queries (1 of 4)

What do YOU think is the biggest difference between design view vs. query editor window?

Bye for Now,
Catto

T-SQL Database Development IDE & Queries (1 of 4)

Hey Now Everybody,

This is the first T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll break it up into two major sections the tool itself & querying data.

1. IDE SSMS SQL Server Management Studio
     a. Installation
     b. Connoting to Server
     c. Object Explorer
     d. Query Editor Window
     e Design View
2. Queries (Common Commands)
     a. Syntax
     b. Comments
     c. Data Types
     d. Create
     e. Select
     f. Insert
     g. Where
     h. Update
     i. Delete
     j. AND
     k. OR
     l. Order By
     m. Group By
     n. Count
     o. Joins
-->> T-SQL
1. IDE

a. Installation - SSMS in my opinion is the toughest windows application to install. This posts prereq is Management Studio is installed

b. Connecting to SQL Server- After opening the app the first thing commonly done is to connect to a SQL Server. Under the File menu there is an option 'Connect to Object Explorer' (Alt+F, Enter). Then the user is prompted enter the Server Name & authentication credentials.

c. Object Explorer - This is a powerful file folder explorer in a tree structure (expand collapse nodes). We can view it by pressing F8 or on the view menu. This is where we will commonly view the servers, databases, objects, tables, stored procedures. The parent node is the SQL Server once expanded we'll spend most of our time in the Databases node. When we expand the databases node we'll spend most time in the Tables node & Programmability --> Stored Procedures node.
SQL Server
     Databases
          Tables!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          Programmability
               Stored Procedures!!!!!!!!!!!!!!!!!

d. Query Editor Window - The query editor window is extremely powerful. We can open on by highlighting/selecting the database then pressing Ctrl+N (or File Menu -> New --> Query)

e. Design View - This is a view when querying data is useful for people who enjoy a more graphical view when developing queries. We can open this view by selecting/highlighting a table then right clicking -> select Edit top 200 rows. Then press Ctrl + 1, Ctrl + 2, Ctrl + 3. We can now see 4 panes Diagram, Criteria, SQL & Results. 


       2. Queries - Common Commands

a. Syntax Square Brackets [] - Important in T-SQL when referencing objects

b. Comments - T-SQL Comments can be done in two ways.
      a.1 /* Multiline comment any text between a slash & asterisk then asterisk & slash will be commented */
     a.2 -- Single Line comment any text on a line after two dashes will be comments
-- When on a line or lines of text is selected Ctrl+K, Ctrl+C will comment the line & Ctrl+K, Ctrl+U will uncomment a line or lines

c. Data Types - There are many data types & more get added in each release. Let's take a look at some common data types for variables in T-SQL
    int Integer
    char character
    datetime Date Time

d. Create - Create is a good command to create a table or stored procedure. In the following example we see the Create command to create a table:
-- Following Script will create a new table called newTablePeopleName w/ 2 columns an ID & Name
CREATE TABLE [dbo].[newTablePeopleName]
(
[newTableid] [int] NOT NULL
, [nameOfPerson] [char](50) NULL
)

e. Select - Select is an extremely powerful & commonly used command Below are two simple select statements from the table we created:
-- Select all columns from table newTablePeopleName
Select *
FROM newTablePeopleName
-- Select NameOfPerson columns from table newTablePeopleName
Select nameOfPerson
FROM newTablePeopleName

f. Insert - Insert is a useful command to insert data into a table. In the following example we display adding a row to our table:
-- Insert a row into newTablePeopleName
INSERT INTO [dbo]. [newTablePeopleName]
( newTableid
, nameOfPerson
)
VALUES
(
1
, 'Catto'
)

g. Where - The Where clause is a powerful command used to refine & select more specific data in a query. In the following example we use the where clause to select only names that match 'Catto':
-- Where example, we select only records that have name 'Catto'
Select *
From [newTablePeopleName]
WHERE nameOfPerson = 'Catto'

h. Update - Update is a useful command to update a record in a table. The example here displays to update the name of our record where id is 1:
-- Update record in table from name Catto to Catto the Tomato
UPDATE [newTablePeopleName]
SET nameOfPerson = 'Catto the Tomato'
WHERE (newTableid = 1)

i. Delete - Delete is a useful command to delete data such as a row or rows from a table In the following example a row is deleted from our table
-- Delete rows from table where the name is Catto
DELETE FROM [dbo.]newTablePeopleName]
WHERE (nameOfPerson = 'Catto')

j. AND - AND is a command great to use when querying data & want to select narrow search results. The following example selects records only with the id = 1 and name = Catto
-- AND is used to select only rows with specific criteria
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
         AND (nameOfPerson = 'Catto')

k. OR - Or is similar to and which is used to narrow search results. The following select statement could select more records since it will select a record if the id = 1 OR name = Catto
-- OR is used to select records with newTableid = 1 OR name is Catto
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
OR (nameOfPerson = 'Catto')

l. Order by - Order by is a useful command & will order results. The following select statement will return the results & order them by id:
-- Order by is used to order the results set
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid
-- DESCending Order is possible too
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid DESC

m. Group by - Group by is useful to refine a query to narrow the result set and group them by a field's value.
-- Select & Group the results set
SELECT newTableid
           , nameOfPerson
FROM [dbo].[newTablePeopleName]
GROUP BY nameOfPerson
, newTableid

n. Count - Count is a good command to count all the rows of a table or combined with the group to count groups of data. The following select statement would return the # of each name
SELECT COUNT(newTableid) AS TotalCountOfName
                     , nameOfPerson
FROM [newTablePeopleName]
GROUP BY nameOfPerson

o. Joins - Joins are a key command to understand when selecting data from multiple tables, there are inner & outer joins. In the following example we select data from 2 tables:
SELECT newTablePeopleName.nameOfPerson
           , oldTablePeeps.nameOfPerson
FROM [newTablePeopleName]
INNER JOIN [oldTablePeeps]
ON newTablePeopleName.newTableid = oldTablePeeps.newTableid

We really just covered some very important fundamentals of T-SQL including the tool & how to query.

Bye for Now,
Catto

FLA.NET Speaker Idol 2010 WebDev 0-60 in 10 min

Hey Now,

So our FLA.NET Speaker Idol competition is ongoing. I’m presenting today @ Comptec in West Palm. My session & content outlined below:

WebDev ASP.NET & T-SQL 0 – 60 in 10 minutes.

1.Browsers
     Popularity
     Use Favs Wisely
     Tabs
     Add-ons Speed Dial, FoxTab
     Dev Tools Firebug & IE Dev Toolbar
2.Keyboarding / Hotkeys
     Alt + Tab
     Opening Apps
     Catto Home Keys
3.RSS
     Feed Reader
     Drinking From Firehouse
4.Tools
5. Memory / Hardware
     Task Manager
     Minimize Apps
     Max RAM Hardware
6.Web Development
     ASP.NET
     Controls
     Master Pages
     FTP
     CSS
7.Database Development
     T-SQL
     Stored Procs
8.Certifications
     536 .NET Foundations
     562 ASP.NET WebDev
     433 Database Dev
9.SEO
     Index Domain
     Titles Links
10.Resources
     Trainings
     Blogs
     Pods

The slide deck will be available public shortly. Should be a really fun event that I’m really looking forward to.

That is all there is there is no more,

Catto

70-536 How to Study, Take & Pass Microsoft Exam

Hey Now Everybody,

The 70-536 Microsoft Exam .NET Foundations / Fundamentals is a very interesting exam, it's the prerequisite for over 9 certification. I've studied the content, taken the exam & passed. Below are the key items to know about studding, taking & passing the 70-536 exam.

Preparation I spend endless hours preparing for the exam by first using the self-paced book the cd in the book which included code, test exam, pdf. I didn't read every word of the book but an extremely large percentage. I also blogged about much of the content which helped me review the content in detail. Another resource which was the best resource to pass the exam (not necessarily learn the content) was a practice exam from a torrent I found which was produced by some crazy company crazytopcert.com (similar to crazytestking) which was a sample test of the exact similar questions as on the exam even the answer order some of them. They also sell these practice exams on ebay for ~$5.

Enrolling for the exam, I registered for a voucher which saved me 50% or $62.50 when to prorizion and registered on that site, picked a location & date.

Taking the exam was very intense & put quite a bit of a good kind of pressure since there is so much at stake. I went threw the test & answered all the questions, then reviewed the exam about 4 or 5 times, focusing on questions I knew I didn't get right. Now I logged into the Microsoft mcp site & obtained my logo & there is my transcript showing I passed the exam. Next up 50-562 .NET 3.5 ASP.NET & earn the MCTS!
TS: Microsoft .NET Framework - Application Development Foundation

Bye for now,

Catto

Fun Live Geek Events South FLA Dec ‘09 – Feb ‘10

Hey Now Everybody,

Looking forward to some live Geek / Technical / Training events in the near future. There are two great .NET User groups in South Florida the first Florida.NET & the second is Gold Coast User Group.
I really enjoy these two groups & the people I've meet being involved with them. If you search for a .NET user group in your location most likely there is one close by you too can check out. These events are usually free or a small charge such as a $5 donation.

December ‘09 SharePoint & Gold Coast Holiday Party
January ‘10 FLA Speaker Idol
February ‘10 South FLA .NET Code Camp

On Thursday December 3rd there is going to be Microsoft SharePoint Ft Lauderdale Microsoft SharePoint 2010 1 Day Workshop for Developers located in the local Microsoft office building. Paul Stubbs @paulstubbs will be one of the presenters who I'm looking forward to seeing present for the first time.

On Friday I'm scheduled to take my Microsoft Certification .NET Foundations 70-536 exam which I've been preparing for & I'm really looking forward to this. It will be the first Microsoft Certification exam I've ever taken so it'll be a great experience. Then to celebrate the exam I'm attending our annual Gold Coast User Group Holiday party where there are going to be some stellar session including:

Tim Huckaby -Multi Touch Silverlight applications
Ken Spencer-Windows Presentation Foundation Tips
Joe Homnick @smokingJoe -SharePoint 2010 Developer Blain Barton -Windows 7.0 Developer Tricks
Shervin Shakibi @shervsherv -SQL Azure Tips (this session is da bomb!)
Paul Stubbs @paulstubbs -SharePoint 2010 Silverlight WebParts
Miguel Castro @MiguelCastro67 -Code Generation Techniques with CodeBreeze
Jason Milgram @jmilgram -Linxter SDK
Dave Noderer @DaveNoderer -TFS 2010 Source Control
Hosts Russ Fustino @rfustino and Stan Schultes @fStanSchultes
The are going to be some great presentations this day along with a nice party with some good people @ night.

In January there is going to be the Speaker Idol continuing in FLA & there are going to be two meetings
So far we have two speaker idol sessions scheduled in January: I submitted a session for the West Palm meeting & plan to prepare for that which will be real fun. Ft Lauderdale - 1/19/2010 - 6:30 PM - Contact Rainer Haberman @RainerHabermann
West Palm - 1/26/2010 - 6:30 PM - Contact Vishal Shukla @vishal_shukla

Then in February there is our Code Camp February 27th which is the best event all year!

We all learn from each other, I’d like to thank in advance all the people who will make these event happen & participate in one way or another. These events I’m sure will be entertaining & educational.

Bye for now!

Catto

SW FLA Code Camp .NET Exam Cram

Hey Now Everybody,

Saturday is our SW Florida Code Camp & I’m presenting my session .NET Exam Cram. It’s going to be based on exam 70-536 which is the .NET Foundations exam. Also we’ll discuss topics that we’ll use everyday. This session will be great for beginners to guru’s. These topics will make us more efficient in our everyday tasks, also we’ll discuss positive personal productivity. The session is scheduled for

Room 124 @ 4:15pm Saturday Oct. 3rd 2009.

There will also be a surprise free give away to one lucky person. If you can’t make the session much of the content is outlined below:

Windows 7
Browsers
     FireFox 3.5
     IE8
     Chrome
     Safari
     Opera
RSS
Hotkeys – Keyboarding (Catto Home Keys)
    ALT + TAB
    Opening Apps
    Tab Key
     Browser Hotkeys
     Outlook
     Windows Key
     Visual Studio
     SQL Server Management Studio 
Memory Management  

MCTS 70-536 Summary Word Document ~70 pages

As for the code camp in general it’s just a really fun all day training & networking technology event. Special thanks to John Dunagan along with others for putting the event together.

As Always all comments welcome!

Catto