Simple steps to write a store procedure in SQL Server 2005 that will help the novice/beginners in writing their first store procedure for inserting the data into database. Here the sample procedure which I will write will insert the binary file into database. This binary file passes to the procedure as parameter along with filetype and filename. This binary file parameter accepts the binary array which it reads from UI (User Interface). You can pass these parameters to store procedures using Parameters.Add method as argument. Here I will not go into the details of front end events that will use to save the values to database. To begin write away to the procedure you will first write the CREATE PROCEDURE command:
CREATE PROCEDURE Files_Insert
This simple command will create a procedure in database. Now add the parameter list to procedure. This parameter list contains the filetext, filename, filetype as I have discussed with you earlier:
@p_FileID int output,
@p_FileText varbinary(MAX) = null,
@p_FileName varchar(50) = null,
@p_FileType varchar(50) = null
After adding parameter list to store procedure simply write the insert command. The @@IDENTITY will return the identity value of primary key value.
INSERT INTO [Files]
(
[FileText],
[FileName],
[FileType]
)
VALUES
(
@p_FileText,
@p_FileName,
@p_FileType
)
SET @p_FileID = @@IDENTITY
Your final procedure will look like as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE Files_Insert
-- Add the parameters for the stored procedure here
@p_FileID int output,
@p_FileText varbinary(MAX) = null,
@p_FileName varchar(50) = null,
@p_FileType varchar(50) = null
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Files]
(
[FileText],
[FileName],
[FileType]
)
VALUES
(
@p_FileText,
@p_FileName,
@p_FileType
)
SET @p_FileID = @@IDENTITY
END
GO
