SQL: VB.NET Code Generating Stored Procedure

All computer applications need data to run. Preparing your code to read from a database using stored procedures has to be one of the most common, redundant and tedious tasks. The developer must know what stored procedures are available on the target database, parameters available, their ordinal position their datatype and precision. (doesnt sound like much fun)....... atleast I dont think it is and thus I wrote this small procedure to do all the above mentioned tasks and produces the desired code. :)


Instructions:

1. Copy the code in Pink (no reason for color choice) ,paste it on your query analyser and run it. A stored procedure called
sproc_GenerateParameters is created on the target databse
2. Execute sproc_GenerateParameters as follows:
exec sproc_GenerateParameters Param1,Param2,Param3,Param4

Key
---
Param1:= Name of Procedure you wish to generate
Param2:= Name of Business object being used to call Stored Procedure
Param3:= Name of BO function through which to call procedure
Param4:= Function return type E.G Integer, String, Object


/*
===============================================================
THIS CODE WILL GENERATE VB.NET CODE FOR CREATING PARAMETERS
THE CODE SHALL BE USED BY WHILE DEVELOPING THE
COMPONENT THAT WILL USE THE SPECIFIED STORED PROCEDURE

AUTHOR: MG
DATE: 20/07/2006
==================================================================
*/


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sproc_GenerateParameters]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sproc_GenerateParameters]

go

create proc sproc_GenerateParameters
@ProcedureName varchar (50), /*Name of Procedure you wish to generate*/
@objectName varchar (50), /*Name of Business object being used to call Stored Procedure*/
@FunctionName varchar (50), /*Name of BO function through which to call procedure*/
@returnType varchar (50) /*Function return type E.G Integer, String, Object*/
as



DECLARE @PARAM_NAME VARCHAR (100),
@DIRECTION INT,@TYPE_NAME VARCHAR(50),@LENGTH INT,
@ORDINAL_POSITION int, @isNULLABLE VARCHAR (5),
@DBtypeInt varchar (20), @DBtypeNvarchar varchar (20),
@DBTypeBit varchar (20), @DBTypeDateTime varchar (20),
@DBTypeSmallInt varchar (20),@DBTypeMoney varchar (20),
@Run int ,@@FunctionSignature varchar(4000),
@@FuncDefinition varchar (8000),@@FunctionCall varchar (8000)

set @Run = 0 /*Run counter to track number of cycles*/

set @DBtypeInt = 'SqlDbType.Int'
set @DBtypeNvarchar='SqlDbType.NVarChar'
set @DBTypeDateTime = 'SqlDbType.DateTime'
set @DBTypeBit = 'SqlDbType.Bit'
set @DBTypeMoney ='SqlDbType.Money'
set @DBTypeSmallInt='SqlDbType.SmallInt'
declare @paraString varchar(4000)

GETMETADATA:
declare ParaCur cursor fast_forward read_only
for


SELECT DISTINCT
PARAM_NAME = convert(sysname,c.name),DIRECTION = convert(smallint, 1+c.isoutparam),
TYPE_NAME = t.name,
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)end) LENGTH,
ORDINAL_POSITION = convert(int, c.colid),IS_NULLABLE = CASE (C.ISNULLABLE) WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' ELSE '' END

FROM syscolumns c
INNER JOIN sysobjects o ON o.ID=c.ID
INNER JOIN systypes t ON c.xusertype = t.xusertype
INNER JOIN master.dbo.spt_datatype_info d ON c.xtype = d.ss_dtype

WHERE o.name =@ProcedureName AND user_name(o.uid) like 'dbo' AND o.id = c.id
AND isnull(d.AUTO_INCREMENT,0) = 0 AND (o.type in ('P'))
ORDER BY ORDINAL_POSITION
if @run = 0
begin

print 'With '+@objectName+'
.CommandText ="'+@ProcedureName+'"
.CommandType = CommandType.StoredProcedure'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin

/*Data Type*/

if @TYPE_NAME = 'int' set @TYPE_NAME=@DBtypeInt
else if @TYPE_NAME = 'varchar' set @TYPE_NAME=@DBtypeNvarchar
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME=@DBTypeDateTime
else if @TYPE_NAME = 'Bit' set @TYPE_NAME=@DBTypeBit
else if @TYPE_NAME = 'Money' set @TYPE_NAME=@DBTypeMoney
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME=@DBTypeSmallInt
else set @TYPE_NAME = 'SqlDbType.'+@TYPE_NAME

/*Direction */

set @paraString =' .Parameters.Add("'+@PARAM_NAME+'",'+@TYPE_NAME+','+cast(@LENGTH as varchar(10))+' ) '
print @paraString
if @DIRECTION = 2
begin
set @paraString = ''
set @paraString = @paraString + ' .Parameters('+cast(@ORDINAL_POSITION as varchar(10))+').Direction = ParameterDirection.InputOutput'
print @paraString
end
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
set @paraString = ''
end
close paraCur
deallocate paraCur
print 'End With'
print'
===========================================================
'
set @run = 1
goto GETMETADATA /*Cannot believe I used a GOTO statement :( */
end
else
if @run = 1 /*Generate The Functions*/
begin
set @@FunctionSignature = 'Public Function '+@FunctionName+' (ByRef row As '
set @@FuncDefinition = 'With '+@objectName
set @@FuncDefinition = @@FuncDefinition+'
'
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin

/*Data Type*/

if @TYPE_NAME = 'int' set @TYPE_NAME= 'Integer'
else if @TYPE_NAME = 'varchar' set @TYPE_NAME='String'
else if @TYPE_NAME = 'DateTime' set @TYPE_NAME='DateTime'
else if @TYPE_NAME = 'Bit' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'Money' set @TYPE_NAME='Decimal'
else if @TYPE_NAME = 'Smallint' set @TYPE_NAME='Int16'
else if @TYPE_NAME = 'TinyInt' set @TYPE_NAME='Int16'

else set @TYPE_NAME = @TYPE_NAME

/*Signature*/

set @@FunctionSignature = @@FunctionSignature+', '+replace(@PARAM_NAME,'@','')+' as '+@TYPE_NAME
set @@FuncDefinition = @@FuncDefinition+'
.Parameters.Item("'+@PARAM_NAME+'").Value ='+replace(@PARAM_NAME,'@','')+' '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur

set @@FunctionSignature = @@FunctionSignature+' ) as '+ upper(@returnType)
set @@FuncDefinition = @@FuncDefinition+'
.ExecuteNonQuery()
If (CDbl(.Parameters.Item("@@error_value").Value) <> 0) Then Return


End With
End Function'
print @@FunctionSignature
print ''
print @@FuncDefinition

set @run = 2
print '
===========================================================
'
goto GETMETADATA /*I did it again :( */

end
else
if @run = 2 /*Generating Function Call*/
begin

print 'Generating Function Call'
print '========================'
set @@FunctionCall=@FunctionName+'(Row, '
open paraCur
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
while @@fetch_status=0
begin
set @@FunctionCall=@@FunctionCall+'<'+replace(@PARAM_NAME,'@','')+'>, '
FETCH NEXT FROM ParaCur INTO @PARAM_NAME, @DIRECTION ,@TYPE_NAME, @LENGTH,@ORDINAL_POSITION, @isNULLABLE
end
close paraCur
deallocate paraCur

set @@FunctionCall=left(@@FunctionCall,len(@@FunctionCall)-1)+' )'
print @@FunctionCall
end

Example:
---------
Scripting the CustOrderHist stored procedure in the Northwind Database
exec sproc_GenerateParameters 'CustOrderHist', 'CustOrder', 'getCustomerOrder' , 'integer'

Output
-------
Visual Basic.NET code for accessing the above Stored procedure.

Remarks
--------
The Output code will need to be tweaked with the following:
1.Create a command object
2.Create a connection and pass it to the command object
3. Copy the output and paste it into your code and Viola!!! u are good to go

Known Issues
--------------
The code procedure may return an error when executed on SQL Server 2005. Reason being that the table master.dbo.spt_datatype_info is not available. You can try a DTS import of the table into sql server 2005.