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.
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:
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:
...but you cannot do this:
EXEC [INSERT INTO - CustomerTypes] 'Individual','Individual', ,100
Watch the video:
For more information, please see the following article:
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!
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!
|  |
| 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
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
...
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!
| |
No comments:
Post a Comment