Teach Yourself SQL Server: February 2014

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

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

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

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

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

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

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

SET QUOTED_IDENTIFIER ON

-- ===========================================================

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

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

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:

http://devwithdave.com/SQLServer/tables/SQLServer-Tables--INSERT-RECORDS-INTO-TABLE-VIA-STORED-PROCEDURE.asp

SQL SERVER COURSE - 70% OFF FOR OUR READERS

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

This post is about how you can use a stored procedure to update records in a database.

The reason why you would want to do this in a production system or enterprise software solution is this: Using a stored procedure to contain the guts off this functionality will keep your compiled application code cleaner and more manageable.

In other words: Instead of writing all of the T-SQL code directly in VB.Net, ASPX, or C Sharp, all you need to do is call one line of code, like this:

Shared Sub UpdateCustomerTypes( CTKey As Integer,

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 + CTKey + ", "

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 highlighted section could just as easily have been written on a single line. The multiple lines were coded to make the VB Script a little more easy to read,

The rest of the code can live in the Stored Procedure in SQL Server.

Now, normally you would need to write code like this in SSMS:

UPDATE CustomerTypes

SET CT_Modified = GETDATE()

UPDATE CustomerTypes

SET CT_Modified = NULL

WHERE CT_Display = 'Individual'

SQL SERVER COURSE - 95% OFF FOR OUR READERS

SQL Server FAST TRACK - Course 101 - Tables

However, this is an example of a custom made Stored Procedure for updating a record in a specific table:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- ===========================================================

-- Create date: 2014-01-22

-- Description: Updates record in CustomerTypes

-- ===========================================================

CREATE PROCEDURE [dbo].[UPDATE TABLE - CustomerTypes]

@CTKey INT,

@CTDisplay VARCHAR(2000),

@CTDescription VARCHAR(2000),

@CTVisible BIT,

@CTOrder INT

BEGIN

UPDATE CustomerTypes

SET CT_Display = @CTDisplay,

CT_Description = @CTDescription,

CT_Visible = @CTVisible,

CT_Order = @CTOrder,

CT_Modified = GETDATE()

WHERE CT_Key = @CTKey

END

Then, to use the stored procedure, either call it from the TSQL command line or from VB.Net and/or C#:

VB.Net

(See example at beginning)

TSQL

EXEC [UPDATE TABLE - CustomerTypes] 2, 'B', 'Business', 1, 100

To find out more, please see the following video. It discusses how to update table records via a Stored Procedure in SQL Server. This lesson is a very quick and informative tutorial with code that will be easy to duplicate and re-use.

Watch the video:

For more information, please see the following article:

http://devwithdave.com/SQLServer/tables/SQLServer-Tables--UPDATE-RECORDS-IN-TABLE-VIA-STORED-PROCEDURE.asp

SQL SERVER COURSE - 70% OFF FOR OUR READERS

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

Teach Yourself SQL Server

Wednesday, February 5, 2014

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

HOW TO UPDATE RECORDS IN A TABLE VIA STORED PROCEDURE