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,
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 + 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
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
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:
However, this is an example of a custom made Stored Procedure for updating a record in a specific table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
GO
--
===========================================================
-- Author: Mert Man
©
-- 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
AS
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:
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