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 -

1. Tables start with tbl and lookup tables start with tlkp.
2. 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