Thursday, June 5, 2014

SQL Server -- Query Table Record Data via TSQL - WHERE CLAUSE - AND Oper...

This SQL Server tutorial shows the tricks for using the AND operator in the query WHERE clause. It demonstrates how to do this from the TSQL Command line.




SQL SERVER COURSE - 95% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.



Normally this course is $199.00, but for a limited time it is available through this offer for only $3.00!   Click the link below to get started:

SQL Server -- Query Table Record Data via TSQL - WHERE CLAUSE - The OR O...

This SQL Server tutorial shows the tricks for using the ORoperator in the query WHERE clause. It demonstrates how to do this from the TSQL Command line.





SQL SERVER COURSE - 95% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.



Normally this course is $199.00, but for a limited time it is available through this offer for only $3.00!   Click the link below to get started:

SQL Server -- Query Table Record Data via TSQL -- WHERE CLAUSE -- AND, O...

When it comes to using SQL Server and writing TSQL queries, you really need to understand the WHERE CLAUSE. It is primarily used to state your criteria, or in other words, how you will be filtering records. Other wise you will return every record in a table or a set of tables, and that is almost never desirable.
 
The structure of the WHERE CLAUSE has a lot of pieces to it and you can nest as many AND and/or OR operators. It is very handy. The video below will get you started.
 
Note: The WHERE CLAUSE always follows the FROM CLAUSE and always PRECEDES the ORDER BY Clause, if the ORDER BY Clause is used. The Order BY Clause is optional. Actually, so is the WHERE Clause. The FROM Clause is also optional, but it is highly unlikely that it will be missing.
 
Most of the time you will be running a query (F5 button) from the command line in SSMS (SQL Server Management Studio) or perhaps from within a stored procedure.
 

 



SQL SERVER COURSE - 95% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.



Normally this course is $199.00, but for a limited time it is available through this offer for only $3.00!   Click the link below to get started:

SQL Server FAST TRACK - Course 101 - Tables

SQL SERVER - STORED PROCEDURES - HOW TO WRITE TO A FILE

One of the coolest features of SQL Server coding is the ability to write to a text file. You can actually write to any ASCII based flat file format, like plain text (.TXT). comma separated values (.CSV) also known less officially as comma separated files, tab delimited and even special import files for QuickBooks (.IIF).
 
In order to do this you will need to turn on a few features in SQL Server that are initially turned off:
 
xp_cmdshell
 
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
 
Ole Automation
 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
reconfigure with override;
GO
 

 
The video below will go into a lot more detail than this article. However, you need to understand that even if you run the two previous scripts (and you must be an administrator), and even if you are referencing a real location on the server, sometimes this writing to a file still won't work. It can be frustrating. If this happens to you it is possible that you do not have OS (operating system) permissions on the folder. Permissions will always burn you.
 
If so, try adding the user "Everyone" and then give the "Everyone" account write permissions. That usually takes care of any issues in WIndows.
 
If you find this video helpful, please leave a comment below!
 



SQL SERVER COURSE - 95% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.



Normally this course is $199.00, but for a limited time it is available through this offer for only $3.00!   Click the link below to get started:

SQL Server FAST TRACK - Course 101 - Tables

SQL SERVER - QUERIES - FROM CLAUSE - JOIN vs NO JOIN




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER - QUERIES - FROM CLAUSE - JOIN vs NO JOIN

This video covers some of the more important theories of SQL. A join is a way of stating certain requirements in the FROM CLAUSE as opposed to writing much more confusing and convoluted code in the WHERE CLAUSE. The reason to do this is that it will make your code cleaner and tighter (easier to maintain) as well as making it much more easier to read.
 

 
By adhering to these coding standards you will have a better coding experience. The technologies that are available in both SQL (Structured Query Language) and then SQL Server's T-SQL (Transact SQL) are really in place to help you to create a more robust and clear query. Your stored procedures (SP's or sprocs or procedures), user defined functions (UDF's or functions) and trigger code will be much better and will integrate more easily with the feelings and preferences of most other typical and advanced software developers.
 



SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER - QUERIES - FROM CLAUSE - LEFT JOIN

This tutorial explains how to use LEFT JOIN in a query as opposed to INNER JOIN and/or NO JOIN. It explains the problems that are solved when you correctly use a LEFT JOIN.





SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER - QUERIES - FROM CLAUSE - INNER JOIN vs LEFT JOIN

This tutorial covers the difference between using a LEFT JOIN or an INNER JOIN. It looks at all of the mistakes that newer developers can make when they fail to use an actual JOIN in the correct way. It also explains the pitfalls of when data becomes dirty. .




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER - QUERIES - FROM CLAUSE - LEFT JOIN vs RIGHT JOIN




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER - QUERIES - FROM CLAUSE - INNER JOIN and TEMP TABLES




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

Wednesday, June 4, 2014

SQL SERVER - QUERIES - FROM CLAUSE - INNER JOIN and TABLE VARIABLES




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $59.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started

SQL SERVER UNION QUERY EXAMPLE

This video demonstrates how to properly use a UNION query in SQL from the TSQL command line in SSMS.

 



SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $59.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started


Wednesday, February 5, 2014

HOW TO INSERT RECORDS INTO A TABLE VIA STORED PROCEDURE - WITH DEFAULT VALUES

This post is about how you can use a Stored Procedure to directly insert records into a database table.

You might be asking, "Why might you want to bother with creating a stored procedure for a simple insert?' The reasons are twofold: First, It is a good practice to compartmentalize your code as much as possible, for the sake of future code management; Second, using a stored procedure to contain this functionality will keep your VB code more clean, lean and manageable. This is especially true when it comes to supporting existing applications that have been compiled and distributed.

In other words, as opposed to having to write all of your T-SQL commands directly in VB or C Sharp (C#), all you have to do is execute a single line of code, something like this. in VB:


Shared Sub InsertIntoCustomerTypes(     CTDisplay         As String,
                                        CTDescription     As String,
                                        CTVisible         As Boolean,
                                        CTOrder           As Integer)


Dim sRet    As String
Dim sp      As String
Dim str     As String
Dim cmd     As OleDbCommand

Dim Conn As New OleDbConnection


Conn = ConnectToServer()

Conn.Open()

cmd = New OleDbCommand

cmd.Connection = Conn

sp = [INSERT INTO - CustomerTypes] "

sp = sp + "'" + CTDisplay + "', "
sp = sp + "'" + CTDescription + "', "
sp = sp + CTVisible + ", "
sp = sp + CTOrder

cmd.CommandText = sp

On Error Resume Next

cmd.ExecuteNonQuery()
If Err.Number <> 0 Then
            MsgBox(Err.Description)
            Exit Sub
End If

On Error GoTo 0

End Sub



The next step is to create a Stored  Procedure (sproc/sp) in T-SQL. You will become a Stored Procedure evangelist once you get rolling. This is because you can still alter your T-SQL code even after an end user application has been compiled and/or deployed. This is a huge selling point, since a customized implementation for an otherwise 'off-the-shelf' solution might be locked down and immutable. In a case like that, you can't even re-order the fields that are being returned from a query. With a Stored Procedure, which is in fact compiled, but 'apart from' and 'outside of' the end user interface project code. You can modify the Transact-SQL statements in the Stored Procedure code all day long anytime that you want.

An EXE or DLL that is compiled is basically frozen shut. Having the Transact SQL Code live in a Stored Procedure means that you get a second chance to fix things and/or alter things in your SQL Code even after the user application has been compiled and locked down.  This is hugely important. It is so important that it can mean the difference between telling your biggest customer "Yes" or "No" when  they need to have something enhanced enhanced in their system.

Following is some SQL Code snippets. The order of the code examples will be this:

    1. Table Creation Script
    2. Stored Procedure Creation Script
    3. Example of Inserting Into table via Stored Procedure

Our first example is an executable script that will create a table called CustomerTypes, if it does not yet exist.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CustomerTypes](
     [CT_Key] [int] IDENTITY(1,1) NOT NULL,
     [CT_Display] [varchar](2000) NULL,
     [CT_Description] [varchar](2000) NULL,
     [CT_Visible] [bit] NULL,
     [CT_Order] [int] NULL,
     [CT_Created] [datetime] NULL,
     [CT_Modified] [datetime] NULL,
 CONSTRAINT [PK_CustomerTypes] PRIMARY KEY CLUSTERED
(
     [CT_Key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CustomerTypes] ADD  CONSTRAINT [DF_CustomerTypes_CT_Description]  DEFAULT ('') FOR [CT_Description]
GO

ALTER TABLE [dbo].[CustomerTypes] ADD  CONSTRAINT [DF_CustomerTypes_CT_Visible]  DEFAULT ((1)) FOR [CT_Visible]
GO

ALTER TABLE [dbo].[CustomerTypes] ADD  CONSTRAINT [DF_CustomerTypes_CT_Order]  DEFAULT ((9999)) FOR [CT_Order]
GO

ALTER TABLE [dbo].[CustomerTypes] ADD  CONSTRAINT [DF_CustomerTypes_CT_Created]  DEFAULT (getdate()) FOR [CT_Created]
GO



       The next script will build the Stored Procedure. (If you need to re-run the script to modify the stored procedure, then you will need to modify the CREATE command word to ALTER.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ===========================================================
-- Author:      Mert Man ©
-- Create date: 2014-01-22
-- Description: Inserts record in CustomerTypes
-- ===========================================================

CREATE PROCEDURE [dbo].[INSERT INTO - CustomerTypes]

     @CTDisplay                 VARCHAR(2000),
     @CTDescription             VARCHAR(2000),
     @CTVisible                 BIT = 1,
     @CTOrder                   INT = 2000

AS

BEGIN


INSERT INTO CustomerTypes     (
                                   CT_Display, 
                                   CT_Description, 
                                   CT_Visible, 
                                   CT_Order
                              )
    
SELECT          @CTDisplay, 
                @CTDescription, 
                @CTVisible, 
                @CTOrder





Finally we will go and run a few statements to actually add records to the table. You can run this and see what happens after first having successfully run the previous scripts referenced above for both table creation and stored procedure creation.



DELETE FROM CustomerTypes

EXEC [INSERT INTO - CustomerTypes] 'Individual','Individual',1,100
EXEC [INSERT INTO - CustomerTypes] 'Business','Business',1
EXEC [INSERT INTO - CustomerTypes] 'Non-Profit','Non-Profit'

SELECT * FROM CustomerTypes




SSQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started
Please note in the example above, which has been copied and pasted below, but with highlighted areas, that some of the EXEC statements have less parameters than others:     



DELETE FROM CustomerTypes

EXEC [INSERT INTO - CustomerTypes] 'Individual','Individual',1,100
EXEC [INSERT INTO - CustomerTypes] 'Business','Business',1
EXEC [INSERT INTO - CustomerTypes] 'Non-Profit','Non-Profit'

SELECT * FROM CustomerTypes




The first EXEC has 4 parameters, the second has 3 and the last has only 2. How can this still work properly?

It is because this particular stored procedure has some parameters that have been granted default values:



CREATE PROCEDURE [dbo].[INSERT INTO - CustomerTypes]

     @CTDisplay                 VARCHAR(2000),
     @CTDescription             VARCHAR(2000),
     @CTVisible                 BIT = 1,
     @CTOrder                   INT = 2000

AS

BEGIN

...




The equal sign following the data type declaration (= 1 and = 2000) gives the parameter a default value in case the parameter is not passed in by the calling application. Always put defaulted arguments at the end of the argument list. You can be missing them but you cannot skip over them.

Only non set paramteres can be at the end, meaning that you can do this:


EXEC [INSERT INTO - CustomerTypes] 'Business','Business',1


...but you cannot do this:


EXEC [INSERT INTO - CustomerTypes] 'Individual','Individual', ,100


Watch the video:



For more information, please see the following article:




SQL SERVER COURSE - 70% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $18.00!   

SQL SERVER COURSE - SAVE 70% !!

SQL Server FAST TRACK for Novices - Tables

Quickly master intelligent table design and management via SSMS, Queries, TSQL, Scripting and Stored Procedures.   Get Started