This section will give you two examples. The first one explains how you can create Delete Stored Procedure for your table and the second example explains how you can build VB declaration function using Stored Procedures created. You can create Insert, Update, Get and GetSingle stored procedures OR you can check the references section for information on downloading free code.
Example 1: Stored Procedure Generation
This Stored Procedure was designed following these standards -
- Tables start with tbl and lookup tables start with tlkp.
- All Stored procedures will have the format of procedure type and tablename without tbl and tlkp. For example, tblUsers table will have Get procedure name as GetUsers. In the following example it is going to be DeleteUsers.
CREATE Procedure prCreateDeleteProcedure
@table_Name nvarchar(128),
@print bit
AS
Declare @SQLStatement varchar(8000), --Actual Delete Stored Procedure string
@parameters varchar(8000), -- Parameters to be passed to the Stored Procedure
@deleteStatement varchar(8000), -- To Store the Delete SQL Statement
@procedurename nvarchar(128), -- To store the procedure name
@DropProcedure nvarchar(1000) --To Store Drop Procedure SQL Statement
-- Initialize Variables
SET @parameters = ''
SET @deleteStatement = ''
--Get Parameters and Delete Where Clause needed for the Delete Procedure.
SELECT @parameters = @parameters + Case When @parameters = '' Then ''
Else ', ' + Char(13) + Char(10)
End + '@' + INFORMATION_SCHEMA.Columns.COLUMN_NAME + ' ' +
DATA_TYPE +
Case When CHARACTER_MAXIMUM_LENGTH is not null Then
'(' + Cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)) + ')'
Else ''
End,
@deleteStatement = @deleteStatement + Case When @deleteStatement = '' Then ''
Else ' AND ' + Char(13) + Char(10)
End + INFORMATION_SCHEMA.Columns.COLUMN_NAME + ' = @' + + INFORMATION_SCHEMA.Columns.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME AND
INFORMATION_SCHEMA.Columns.Column_name = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.Column_name AND
INFORMATION_SCHEMA.Columns.table_name = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AND
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_name = @table_Name AND
CONSTRAINT_TYPE = 'PRIMARY KEY'
-- the following logic can be changed as per your standards. In our case tbl is for tables and tlkp is for lookup tables. Needed to remove tbl and tlkp...
SET @procedurename = 'Delete'
If Left(@table_Name, 3) = 'tbl'
Begin
SET @procedurename = @procedurename + SubString(@table_Name, 4, Len(@table_Name))
End
Else
Begin
If Left(@table_Name, 4) = 'tlkp'
Begin
SET @procedurename = @procedurename + SubString(@table_Name, 5, Len(@table_Name))
End
Else
Begin
-- In case none of the above standards are followed then just get the table name.
SET @procedurename = @procedurename + @table_Name
End
End
--Stores DROP Procedure Statement
SET @DropProcedure = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @procedurename + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)' + Char(13) + Char(10) +
'Drop Procedure ' + @procedurename
-- In case you want to create the procedure pass in 0 for @print else pass in 1 and stored procedure will be displayed in results pane.
If @print = 0
Begin
-- Create the final procedure and store it..
Exec (@DropProcedure)
SET @SQLStatement = 'CREATE PROCEDURE ' + @procedurename + Char(13) + Char(10) + @parameters + Char(13) + Char(10) + ' AS ' +
+ Char(13) + Char(10) + ' Delete FROM ' + @table_Name + ' WHERE ' + @deleteStatement + Char(13) + Char(10)
-- Execute the SQL Statement to create the procedure
Exec (@SQLStatement)
End
Else
Begin
--Print the Procedure to Results pane
Print ''
Print ''
Print ''
Print '--- Delete Procedure for ' + @table_Name + '---'
Print @DropProcedure
Print 'GO'
Print 'CREATE PROCEDURE ' + @procedurename
Print @parameters
Print 'As'
Print 'DELETE FROM ' + @table_Name
Print 'WHERE ' + @deleteStatement
Print 'GO'
End
GO
Example 2: Building VB functions based on Stored Procedures created -
This example is specifically designed for DotNetNuke 2.0. The following Stored procedure will generate function declaration for Data Access Layer for specific stored procedure. This function needs to be overridden.
CREATE PROCEDURE prCreateDataProviderSubs
@TableName nvarchar(4000)
AS
Declare @routineName nvarchar(128), -- To Store Stored Procedure Name
@functionname nvarchar(4000) -- Actual VB Function or Sub
-- As in our case, we are removing tbl and tlkp from Table Names when creating stored procedure.
SET @TableName = Replace(@TableName, '|tbl', '|')
SET @TableName = Replace(@TableName, '|tlkp', '|')
-- To loop through all Stored Procedures to create Subs and Functions.
Declare curRoutineName cursor For
SELECT Routine_name
FROM INFORMATION_SCHEMA.ROUTINES
WHERE (CharIndex('|' + SubString(Routine_name, 4, Len(Routine_name)) + '|', @TableName) > 0 OR
CharIndex('|' + SubString(Routine_name, 7, Len(Routine_name)) + '|', @TableName) > 0 OR
CharIndex('|' + SubString(Routine_name, 10, Len(Routine_name)) + '|', @TableName) > 0)
Order by 1
Open curRoutineName
FETCH NEXT FROM curRoutineName INTO @routineName
WHILE @@FETCH_STATUS = 0
Begin
If @routineName is not null AND @routineName <> ''
Begin
-- In case of Add, Get and GetSingle the routines might return something.
If Left(@routineName, 3) = 'Add' OR Left(@routineName, 3) = 'Get'
SET @functionname = 'Public MustOverride Function ' + @routineName + '('
Else
SET @functionname = 'Public MustOverride Sub ' + @routineName + '('
End
-- Get all the parameter information and build the parameter string for the function and sub.
SELECT @functionname = @functionname + Case When Right(@functionname, 1) <> '(' Then ', '
Else ''
End +
'ByVal ' + SubString(PARAMETER_NAME, 2, Len(PARAMETER_NAME)) + ' as ' +
Case When DATA_TYPE = 'int' or DATA_TYPE = 'smallint' Then 'integer'
When DATA_TYPE = 'nvarchar' or DATA_TYPE = 'Char' or DATA_TYPE = 'varchar' Then 'string'
When DATA_TYPE = 'datetime' Then 'date'
When DATA_TYPE = 'bit' Then 'boolean'
Else 'object'
End
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE Specific_name = @routineName
-- In case of Add then the return is going to be integer. In case of Get or GetSingle the return value is going to be IDataReader
If Left(@routineName, 3) = 'Add' OR Left(@routineName, 3) = 'Get'
Begin
If Left(@routineName, 3) = 'Add'
SET @functionname = @functionname + ') as integer'
Else
SET @functionname = @functionname + ') as IDataReader'
End
Else
-- In case of Update and Delete it is sub.
SET @functionname = @functionname + ')'
-- Print the function
Print @functionname
FETCH NEXT FROM curRoutineName INTO @routineName
End
Close curRoutineName
Deallocate curRoutineName
