SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Create proc cspCaseSelProcParamsVbNET @ProcName varchar(255) = null , @OleDbOrSql varchar(255) = 'Sql' --'OleDb' -- AS -- exec cspCaseSelProcParamsVbNET null,'OleDb' -- exec cspCaseSelProcParamsVbNET null,'sql' Declare @@s1 varchar(600) , @@s2 varchar(600) , @@sDim varchar(600) , @@sParam1 varchar(600) , @@sParam2 varchar(600) , @@sBottomFillReturnDs varchar(1000) , @@sBottomParamDebug varchar(1000) , @@sFunctionDs varchar(512) , @@sFunctionDataReader varchar(512) , @@sFunctionExNonQ varchar(600) , @@sFunctionExNonQOpt varchar(600) --Declare @@sDeclare @@sDim varchar(1000) , @@sObjParm varchar(25) , @@sDbTypeLib varchar(25) , @@sRetValAddOleDb varchar(255) -- = '.Add(New OleDbParameter("@RETURN_VALUE", OleDbType.Integer, 4, ParameterDirection.ReturnValue, True, 0, 0, "RETURN_VALUE", DataRowVersion.Current, iRETURN_VALUE))' , @@sRetValAddSql varchar(255) -- = '.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, True, 0, 0, "RETURN_VALUE", DataRowVersion.Current, iRETURN_VALUE))' set @@sObjParm = case when @OleDbOrSql = 'sql' then 'SqlParameter' else 'oledbParameter' end set @@sDbTypeLib = case when @OleDbOrSql = 'sql' then 'SqlDbType' else 'OleDbType' end Set @@sFunctionExNonQ = 'Public shared Function dsGetTOKEN_FUNCTION_NAME(TOKEN_ARGUMENTS) as Integer' Set @@sFunctionDs = 'Public shared Function dsGetTOKEN_FUNCTION_NAME(TOKEN_ARGUMENTS) as DataSet' Set @@sFunctionDataReader = 'Public shared Function rdGetTOKEN_FUNCTION_NAME(TOKEN_ARGUMENTS) as IDataReader' If @OleDbOrSql = 'sql' Set @@sDim = 'Dim sProcedureName As String = "TOKEN_PROC_NAME"' + char(13) + 'Dim cnDB As New SQLConnection(datAbase.coNnectionstring)' + char(13) + 'Dim cmdDB As New SQLCommand(sProcedureName, cnDB)' + char(13) + 'Dim prmDB As New ' + case when @OleDbOrSql = 'sql' then 'SqlParameter()' else 'oledbParameter()' end + char(13) + 'Dim daDB As New SQLDataAdapter(cmdDB)' + char(13) + 'Dim dsDB As New DataSet(sProcedureName)' -- + char(13) + 'Dim sParamName As String' -- + char(13) + 'Dim iParamNumber As Integer' + char(13) + 'Dim iRETURN_VALUE As Integer' Else Set @@sDim = 'Dim sProcedureName As String = "TOKEN_PROC_NAME"' + char(13) + 'Dim cnDB As New oledbConnection(datAbase.coNnectionstring)' + char(13) + 'Dim cmdDB As New oledbCommand(sProcedureName, cnDB)' + char(13) + 'Dim prmDB As New oledbParameter()' + char(13) + 'Dim daDB As New oledbDataAdapter(cmdDB)' + char(13) + 'Dim dsDB As New DataSet(sProcedureName)' -- + char(13) + 'Dim sParamName As String' -- + char(13) + 'Dim iParamNumber As Integer' + char(13) + 'Dim iRETURN_VALUE As Integer' Set @@sParam1 = char(13) + 'With cmdDB' + char(13) + char(9) + '.CommandText = sProcedureName' + char(13) + char(9) + '.CommandType = CommandType.StoredProcedure' + char(13) + char(9) + 'With .Parameters' + char(13) + char(9) + 'TOKEN_PARAM_LIST' + char(13) + char(9) + 'End With' + char(13) + 'End With' Set @@sParam2 = char(9) + 'End With' + char(13) + 'End With' Set @@sBottomParamDebug = char(13) -- + '#region "Parameters Debug Section"' + '''*************************************************************************' + char(13) + '''* Parameters debugging code - Comment and UnComment as a block ***' + char(13) + '''* Use only for debugging - Comment or Delete before release ***' + char(13) + '''*************************************************************************' + char(13) + '''If m_iTraceFlag Then' + char(13) + '''m_sTraceMessage = ("Load Stored Proc: Name = " & cmdDB.CommandText & vbCrLf)' + char(13) + '''For iParamNumber = 1 To cmdDB.Parameters.Count - 1' + char(13) + char(9) + '''prmDB = cmdDB.Parameters(iParamNumber)' + char(13) + char(9) + '''sParamName = prmDB.ParameterName' + char(13) + char(9) + '''If IsDBNull(cmdDB.Parameters(iParamNumber).Value) Then' + char(13) + char(9) + char(9) + '''m_sTraceMessage = m_sTraceMessage & ("Param: " & sParamName & " = NULL" & vbCrLf)' + char(13) + char(9) + '''Else' + char(13) + char(9) + char(9) + '''m_sTraceMessage = m_sTraceMessage & ("Param: " & sParamName & " = " & cmdDB.Parameters.Item(sParamName).Value.ToString() & vbCrLf)' + char(13) + char(9) + '''End If' + char(13) + '''Next' + char(13) + '''End If' + char(13) + '''******End of Parameters Debug Section************************************' -- + char(13) + '#End region' Set @@sBottomFillReturnDs = char(13) + '''*************************************************************************' + char(13) + '''***Fill and Return a DataSet*********************************************' + char(13) + '''***Throw Exception during debugging but only write to log after release**' + char(13) + '''*************************************************************************' + char(13) + '' -- + char(13) + 'If 0 = sConnect.length then sConnect = sConnectDefault' + char(13) + 'Try' + char(13) + char(9) + 'cnDB.ConnectionString = Database.ConnectionString' --sConnect' + char(13) + char(9) + 'cnDB.Open() '' Open Db connection' + char(13) + char(9) + 'daDB.Fill(dsDB) '' Get Db data with DataAdapter which has reference to SqlCommand' + char(13) + Case When @oledborsql = 'sql' then 'Catch ex As System.Data.SqlClient.SqlException' else 'Catch ex As System.Data.oledb.oledbException' end + char(13) + char(9) + 'Throw New System.Exception(ex.ToString)' + char(13) + 'Catch ex As System.Exception' + char(13) + char(9) + 'Throw New System.Exception(ex.ToString())' + char(13) + 'Finally' + char(13) + char(9) + 'If cnDB.State = ConnectionState.Open Then' + char(13) + char(9) + char(9) + 'cnDB.Close() '' Close Db Connection' + char(13) + char(9) + 'End If' + char(13) + 'End Try' + char(13) + '' + char(13) + '''Return the DataSet to the calling process' + char(13) + 'Return dsDB' + char(13) + '' + char(13) + 'End Function' Set @@sRetValAddOleDb = '.Add(New OleDbParameter("@RETURN_VALUE", OleDbType.Integer, 4, ParameterDirection.ReturnValue, True, 0, 0, "RETURN_VALUE", DataRowVersion.Current, iRETURN_VALUE))' Set @@sRetValAddSql = '.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, True, 0, 0, "RETURN_VALUE", DataRowVersion.Current, iRETURN_VALUE))' if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCaseProcParms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblCaseProcParms] SELECT FuncDataSet = replace(@@sFunctionDs,'TOKEN_FUNCTION_NAME',O.NAME) ,FuncDataReader = replace(@@sFunctionDataReader,'TOKEN_FUNCTION_NAME',O.NAME) ,FuncDataSetArgOpt = replace(@@sFunctionDs,'TOKEN_FUNCTION_NAME',O.NAME) ,FuncDataReaderArgOpt = replace(@@sFunctionDataReader,'TOKEN_FUNCTION_NAME',O.NAME) ,FuncExNonQ = replace(@@sFunctionExNonQ,'TOKEN_FUNCTION_NAME',O.NAME) ,FuncExNonQArgOpt = replace(@@sFunctionExNonQOpt,'TOKEN_FUNCTION_NAME',O.NAME) ,Dims = replace(@@sDim ,'TOKEN_PROC_NAME',o.name) --replace(o.name,'@','')) ,ParamTop = @@sParam1 ,ParamBottom = @@sParam2 ,FuncBottomParamDebug = ' ' --@@sBottomParamDebug ,FuncBottomDs = @@sBottomFillReturnDs , ParamAdd = Case When spt_dtp.LOCAL_TYPE_NAME = 'int' and c.colid = 0 then char(9) + '.Add(New ' + @@sObjParm + '("@RETURN_VALUE", ' + @@sDbTypeLib + '.Integer, 4, ParameterDirection.ReturnValue, True, 0, 0, "RETURN_VALUE", DataRowVersion.Current, iRETURN_VALUE))' When ((spt_dtp.LOCAL_TYPE_NAME = 'int' ) and c.colid <> 0) then 'if 0 = i' + replace(c.name,'@','') + ' then' + char(13) + char(9) + char(9) + '''If the integer value is Zero then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Integer, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Integer, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, i' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When ((spt_dtp.LOCAL_TYPE_NAME = 'smallint' ) and c.colid <> 0) then 'if 0 = i' + replace(c.name,'@','') + ' then' + char(13) + char(9) + char(9) + '''If the integer value is Zero then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.SmallInt, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.SmallInt, 2, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, i' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When ((spt_dtp.LOCAL_TYPE_NAME = 'tinyint' ) and c.colid <> 0) then 'if 0 = i' + replace(c.name,'@','') + ' then' + char(13) + char(9) + char(9) + '''If the integer value is Zero then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.tinyInt, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.TinyInt, 1, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, i' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' --------------------------------------- When spt_dtp.LOCAL_TYPE_NAME = 'char' then 'if 0 = s' + replace(c.name,'@','') + '.Length then' + char(13) + char(9) + char(9) + '''If the string value is empty then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.varchar, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.VarChar, ' + CONVERT(varchar(6), OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) + ' , ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, s' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When spt_dtp.LOCAL_TYPE_NAME = 'nchar' then 'if 0 = s' + replace(c.name,'@','') + '.Length then' + char(13) + char(9) + char(9) + '''If the string value is empty then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.varwchar, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.VarWChar, ' + CONVERT(varchar(6), OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) + ' , ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, s' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When spt_dtp.LOCAL_TYPE_NAME = 'varchar' then 'if 0 = s' + replace(c.name,'@','') + '.Length then' + char(13) + char(9) + char(9) + '''If the string value is empty then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.varchar, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.VarChar, ' + CONVERT(varchar(6), OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) + ' , ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, s' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When spt_dtp.LOCAL_TYPE_NAME = 'nvarchar' then 'if 0 = s' + replace(c.name,'@','') + '.Length then' + char(13) + char(9) + char(9) + '''If the string value is empty then pass the stored procedure a NULL value. Be sure this provides expected results' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.varwchar, 4, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dbNull.value))' + char(13) + char(9) + 'Else' + char(13) + char(9) + char(9) + '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.VarWChar, ' + CONVERT(varchar(6), OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) + ' , ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, s' + replace(c.name,'@','') + '))' + char(13) + char(9) + 'End if' When spt_dtp.LOCAL_TYPE_NAME = 'uniqueidentifier' then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Guid, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, guid' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'timestamp' then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.DbTimeStamp, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, ts' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'bit' and c.colid <> 0 then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Boolean, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, b' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'datetime' and c.colid <> 0 then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.DbDate, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dt' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'smalldatetime' and c.colid <> 0 then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.DbDate, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dt' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'float' and c.colid <> 0 then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Double, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, flt' + replace(c.name,'@','') + '))' When spt_dtp.LOCAL_TYPE_NAME = 'decimal' and c.colid <> 0 then '.Add(New ' + @@sObjParm + '("' + c.name + '", ' + @@sDbTypeLib + '.Numeric, 0, ParameterDirection.Input, False, 0, 0, "' + replace(c.name,'@','') + '", DataRowVersion.Current, dec' + replace(c.name,'@','') + '))' else 'Name = ' + c.name + '; Type = ' + spt_dtp.LOCAL_TYPE_NAME end , ParamArgOptional = char(9) + Case When spt_dtp.LOCAL_TYPE_NAME = 'int' and c.colid = 0 then '' When ((spt_dtp.LOCAL_TYPE_NAME = 'int' ) and c.colid <> 0) then ', Optional ByVal i' + replace(c.name,'@','') + ' as Integer = 0 _ ' When ((spt_dtp.LOCAL_TYPE_NAME = 'smallint' ) and c.colid <> 0) then ', Optional ByVal i' + replace(c.name,'@','') + ' as Integer = 0 _ ' When ((spt_dtp.LOCAL_TYPE_NAME = 'tinyint' ) and c.colid <> 0) then ', Optional ByVal i' + replace(c.name,'@','') + ' as Integer = 0 _ ' When spt_dtp.LOCAL_TYPE_NAME = 'uniqueidentifier' then ', Optional ByVal guid' + replace(c.name,'@','') + ' as guid = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'char' then ', Optional ByVal s' + replace(c.name,'@','') + ' as string = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'nchar' then ', Optional ByVal s' + replace(c.name,'@','') + ' as string = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'varchar' then ', Optional ByVal s' + replace(c.name,'@','') + ' as string = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'nvarchar' then ', Optional ByVal s' + replace(c.name,'@','') + ' as string = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'timestamp' then ', Optional ByVal ts' + replace(c.name,'@','') + ' as string = "" _ ' When spt_dtp.LOCAL_TYPE_NAME = 'bit' and c.colid <> 0 then ', Optional ByVal b' + replace(c.name,'@','') + ' as boolean = false _ ' When spt_dtp.LOCAL_TYPE_NAME = 'datetime' and c.colid <> 0 then ', Optional ByVal dt' + replace(c.name,'@','') + ' as date = #1/1/1900# _ ' When spt_dtp.LOCAL_TYPE_NAME = 'smalldatetime' and c.colid <> 0 then ', Optional ByVal dt' + replace(c.name,'@','') + ' as date = #1/1/1900# _ ' When spt_dtp.LOCAL_TYPE_NAME = 'float' and c.colid <> 0 then ', Optional ByVal flt' + replace(c.name,'@','') + ' as double = 0 _ ' When spt_dtp.LOCAL_TYPE_NAME = 'decimal' and c.colid <> 0 then ', Optional ByVal flt' + replace(c.name,'@','') + ' as double = 0 _ ' else 'Name = ' + c.name + '; Type = ' + spt_dtp.LOCAL_TYPE_NAME end , ParamArg = char(9) + Case When spt_dtp.LOCAL_TYPE_NAME = 'int' and c.colid = 0 then '' When ((spt_dtp.LOCAL_TYPE_NAME = 'int' ) and c.colid <> 0) then ', ByVal i' + replace(c.name,'@','') + ' as Integer _ ' When ((spt_dtp.LOCAL_TYPE_NAME = 'smallint' ) and c.colid <> 0) then ', ByVal i' + replace(c.name,'@','') + ' as Integer _ ' When ((spt_dtp.LOCAL_TYPE_NAME = 'tinyint' ) and c.colid <> 0) then ', ByVal i' + replace(c.name,'@','') + ' as Integer _ ' When spt_dtp.LOCAL_TYPE_NAME = 'uniqueidentifier' then ', ByVal guid' + replace(c.name,'@','') + ' as guid _ ' When spt_dtp.LOCAL_TYPE_NAME = 'char' then ', ByVal s' + replace(c.name,'@','') + ' as string _ ' When spt_dtp.LOCAL_TYPE_NAME = 'nchar' then ', ByVal s' + replace(c.name,'@','') + ' as string _ ' When spt_dtp.LOCAL_TYPE_NAME = 'varchar' then ', ByVal s' + replace(c.name,'@','') + ' as string _ ' When spt_dtp.LOCAL_TYPE_NAME = 'nvarchar' then ', ByVal s' + replace(c.name,'@','') + ' as string _ ' When spt_dtp.LOCAL_TYPE_NAME = 'timestamp' then ', ByVal ts' + replace(c.name,'@','') + ' as string _ ' When spt_dtp.LOCAL_TYPE_NAME = 'bit' and c.colid <> 0 then ', ByVal b' + replace(c.name,'@','') + ' as boolean _ ' When spt_dtp.LOCAL_TYPE_NAME = 'datetime' and c.colid <> 0 then ', ByVal dt' + replace(c.name,'@','') + ' as date _ ' When spt_dtp.LOCAL_TYPE_NAME = 'smalldatetime' and c.colid <> 0 then ', ByVal dt' + replace(c.name,'@','') + ' as date _ ' When spt_dtp.LOCAL_TYPE_NAME = 'float' and c.colid <> 0 then ', ByVal flt' + replace(c.name,'@','') + ' as double _ ' When spt_dtp.LOCAL_TYPE_NAME = 'decimal' and c.colid <> 0 then ', ByVal flt' + replace(c.name,'@','') + ' as double _ ' else 'Name = ' + c.name + '; Type = ' + spt_dtp.LOCAL_TYPE_NAME end , sProcName = 'sProcedureName = ' + o.name ,CommandText = '.CommandText = sProcedureName' ,CommandType = '.CommandType = CommandType.StoredProcedure' ,PROC_NAME = o.name ,ORDINAL_POSITION = c.colid ,PARAMETER_MODE = CONVERT(nvarchar(10), CASE WHEN c.colid = 0 THEN 'OUT' WHEN ColumnProperty(c.id, c.name, 'IsOutParam')= 1 THEN 'IN/OUT' ELSE 'IN' END) , IS_RESULT = CONVERT(nvarchar(10), CASE WHEN c.colid = 0 THEN 'YES' ELSE 'NO' END) , PARAMETER_NAME = c.name , DATA_TYPE = spt_dtp.LOCAL_TYPE_NAME , CHARACTER_MAXIMUM_LENGTH = CONVERT(int, OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) --, --sCHARACTER_MAXIMUM_LENGTH = CONVERT(varchar(6), OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin) , CHARACTER_OCTET_LENGTH = CONVERT(int, spt_dtp.charbin + CASE WHEN spt_dtp.LOCAL_TYPE_NAME IN ('nchar', 'nvarchar', 'ntext') THEN 2 * OdbcPrec(c.xtype, c.length, c.xprec) ELSE OdbcPrec(c.xtype, c.length, c.xprec) END) ,RetValOleDb = @@sRetValAddOleDb ,RetValSql = @@sRetValAddSql into tblCaseProcParms FROM sysobjects o, syscolumns c JOIN master.dbo.spt_datatype_info spt_dtp ON c.xtype = spt_dtp.ss_dtype AND (spt_dtp.ODBCVer IS NULL OR spt_dtp.ODBCVer = 2) AND (spt_dtp.AUTO_INCREMENT IS NULL OR spt_dtp.AUTO_INCREMENT = 0) , master.dbo.syscharsets a_cha WHERE (o.name = @ProcName or @ProcName is null) and (o.name not like 'dt_%') AND o.xtype IN ('P', 'FN', 'TF', 'IF') AND o.id = c.id AND (c.number = 1 OR (c.number = 0 AND o.xtype = 'FN')) AND permissions(o.id) != 0 AND a_cha.id = isnull(CONVERT(tinyint, CollationProperty(c.collation, 'SQLcharset')), CONVERT(tinyint, ServerProperty('SQLcharset'))) ORDER BY o.name,c.colid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO Create proc cSpCaseSelVbNetFunctions as SET NOCOUNT ON Declare @FuncDataSet nvarchar (4000) ,@FuncDataReader nvarchar (4000) ,@FuncDataSetArgOpt nvarchar (4000) ,@FuncDataReaderArgOpt nvarchar (4000) ,@FuncExNonQ nvarchar (4000) ,@FuncExNonQArgOpt nvarchar (4000) ,@Dims nvarchar (4000) ,@ParamTop varchar (600) ,@ParamBottom varchar (600) ,@FuncBottomParamDebug varchar (1) ,@FuncBottomDs varchar (1000) ,@ParamAdd nvarchar (4000) ,@ParamArgOptional nvarchar (4000) ,@ParamArg nvarchar (4000) ,@sProcName nvarchar (145) ,@CommandText varchar (29) ,@CommandType varchar (42) ,@PROC_NAME sysname ,@ORDINAL_POSITION smallint ,@PARAMETER_MODE nvarchar (10) ,@IS_RESULT nvarchar (10) ,@PARAMETER_NAME sysname ,@DATA_TYPE nvarchar (128) ,@CHARACTER_MAXIMUM_LENGTH int ,@CHARACTER_OCTET_LENGTH int ,@RetValOleDb varchar(255) ,@RetValSql varchar(255) ,@_Inner_FuncDataSet nvarchar (4000) ,@_Inner_FuncDataReader nvarchar (4000) ,@_Inner_FuncDataSetArgOpt nvarchar (4000) ,@_Inner_FuncDataReaderArgOpt nvarchar (4000) ,@_Inner_FuncExNonQ nvarchar (4000) ,@_Inner_FuncExNonQArgOpt nvarchar (4000) ,@_Inner_Dims nvarchar (4000) ,@_Inner_ParamTop varchar (600) ,@_Inner_ParamBottom varchar (600) ,@_Inner_FuncBottomParamDebug varchar (1) ,@_Inner_FuncBottomDs varchar (1000) ,@_Inner_ParamAdd nvarchar (4000) ,@_Inner_ParamArgOptional nvarchar (4000) ,@_Inner_ParamArg nvarchar (4000) ,@_Inner_sProcName nvarchar (145) ,@_Inner_CommandText varchar (29) ,@_Inner_CommandType varchar (42) ,@_Inner_PROC_NAME sysname ,@_Inner_ORDINAL_POSITION smallint ,@_Inner_PARAMETER_MODE nvarchar (10) ,@_Inner_IS_RESULT nvarchar (10) ,@_Inner_PARAMETER_NAME sysname ,@_Inner_DATA_TYPE nvarchar (128) ,@_Inner_CHARACTER_MAXIMUM_LENGTH int ,@_Inner_CHARACTER_OCTET_LENGTH int ,@_Inner_RetValOleDb varchar(255) ,@_Inner_RetValSql varchar(255) ,@message varchar(80) ,@@ArgList varchar(8000) ,@@sFunction varchar(8000) ,@@ParmList varchar(8000) ,@@sTab varchar(1) -- = CHAR(9) ,@@sLF varchar(1) -- CHAR(10) ,@@sCR varchar(1) -- CHAR(13) Set @@sTab = CHAR(9) Set @@sLF = CHAR(10) Set @@sCR = CHAR(13) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCaseFunctionsDs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblCaseFunctionsDs] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCaseFunctionsExec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblCaseFunctionsExec] CREATE TABLE [dbo].[tblCaseFunctionsDs] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [sProcName] [varchar] (128) NULL , [sFunction] [varchar] (7860) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[tblCaseFunctionsExec] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [sProcName] [varchar] (128) NULL , [sFunction] [varchar] (7860) NULL ) ON [PRIMARY] PRINT '-------- VB.Net Functions --------' --DECLARE @RC int --DECLARE @ProcName varchar(255) --DECLARE @OleDbOrSql varchar(255) -- Set parameter values EXEC [cspCaseSelProcParamsVbNET] null,'oledb' DECLARE curProcNames CURSOR FOR SELECT distinct FuncDataSet, FuncDataReader, FuncDataSetArgOpt, FuncDataReaderArgOpt, FuncExNonQ, FuncExNonQArgOpt , Dims, ParamTop, ParamBottom, FuncBottomParamDebug, FuncBottomDs , sProcName, CommandText, CommandType, PROC_NAME ,RetValOleDb ,RetValSql FROM tblCaseProcParms ORDER BY PROC_NAME OPEN curProcNames FETCH NEXT FROM curProcNames INTO @FuncDataSet, @FuncDataReader, @FuncDataSetArgOpt, @FuncDataReaderArgOpt, @FuncExNonQ, @FuncExNonQArgOpt , @Dims, @ParamTop, @ParamBottom, @FuncBottomParamDebug, @FuncBottomDs , @sProcName, @CommandText, @CommandType, @PROC_NAME , @RetValOleDb ,@RetValSql WHILE @@FETCH_STATUS = 0 BEGIN --PRINT ' ' --SELECT @message = '----- PROCs by ProcName: ' + @PROC_NAME + ' ' + @PROC_NAME --PRINT @message -- Declare an inner cursor based -- on ProcName from the outer cursor. DECLARE curParmNames CURSOR FOR SELECT distinct FuncDataSet, FuncDataReader, FuncDataSetArgOpt, FuncDataReaderArgOpt, FuncExNonQ, FuncExNonQArgOpt , Dims, ParamTop, ParamBottom, FuncBottomParamDebug, FuncBottomDs , sProcName, CommandText, CommandType, PROC_NAME, RetValOleDb ,RetValSql , ParamAdd, ParamArgOptional, ParamArg , ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, PARAMETER_NAME, DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM tblCaseProcParms Where Proc_Name = @Proc_Name -- Variable value from the outer cursor Order by Proc_Name,ORDINAL_POSITION OPEN curParmNames FETCH NEXT FROM curParmNames INTO @_Inner_FuncDataSet, @_Inner_FuncDataReader, @_Inner_FuncDataSetArgOpt, @_Inner_FuncDataReaderArgOpt, @_Inner_FuncExNonQ, @_Inner_FuncExNonQArgOpt , @_Inner_Dims, @_Inner_ParamTop, @_Inner_ParamBottom, @_Inner_FuncBottomParamDebug, @_Inner_FuncBottomDs , @_Inner_sProcName, @_Inner_CommandText, @_Inner_CommandType, @_Inner_PROC_NAME, @_Inner_RetValOleDb ,@_Inner_RetValSql , @_Inner_ParamAdd, @_Inner_ParamArgOptional, @_Inner_ParamArg , @_Inner_ORDINAL_POSITION, @_Inner_PARAMETER_MODE, @_Inner_IS_RESULT, @_Inner_PARAMETER_NAME, @_Inner_DATA_TYPE , @_Inner_CHARACTER_MAXIMUM_LENGTH, @_Inner_CHARACTER_OCTET_LENGTH IF @@FETCH_STATUS <> 0 PRINT ' <>' WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = ' ' + @_Inner_PARAMETER_NAME --PRINT @message Select @@ArgList = isnull(@@ArgList,'') + @@sCR + @_Inner_ParamArgOptional Set @@ParmList = isnull(@@ParmList,'') + @@sCR + @_Inner_ParamAdd FETCH NEXT FROM curParmNames INTO @_Inner_FuncDataSet, @_Inner_FuncDataReader, @_Inner_FuncDataSetArgOpt, @_Inner_FuncDataReaderArgOpt, @_Inner_FuncExNonQ, @_Inner_FuncExNonQArgOpt , @_Inner_Dims, @_Inner_ParamTop, @_Inner_ParamBottom, @_Inner_FuncBottomParamDebug, @_Inner_FuncBottomDs , @_Inner_sProcName, @_Inner_CommandText, @_Inner_CommandType, @_Inner_PROC_NAME, @_Inner_RetValOleDb ,@_Inner_RetValSql , @_Inner_ParamAdd, @_Inner_ParamArgOptional, @_Inner_ParamArg , @_Inner_ORDINAL_POSITION, @_Inner_PARAMETER_MODE, @_Inner_IS_RESULT, @_Inner_PARAMETER_NAME, @_Inner_DATA_TYPE , @_Inner_CHARACTER_MAXIMUM_LENGTH, @_Inner_CHARACTER_OCTET_LENGTH END CLOSE curParmNames DEALLOCATE curParmNames --------------PRINT HERE Set @@ArgList = STUFF(@@ArgList, PATINDEX('%,%', @@ArgList), 1, '') --DS Insert tblCaseFunctionsDs(sProcName,sFunction) Values(@_Inner_Proc_Name, Replace(@_Inner_FuncDataSet,'TOKEN_ARGUMENTS',' _ ' + @@ArgList + @@sCR)) Insert tblCaseFunctionsDs(sProcName,sFunction) Values(@_Inner_Proc_Name, replace(@_Inner_Dims,'dim',@@sTab + 'DIM ')) Insert tblCaseFunctionsDs(sProcName,sFunction) Values(@_Inner_Proc_Name, replace(@_Inner_ParamTop,'TOKEN_PARAM_LIST',@@ParmList)) Insert tblCaseFunctionsDs(sProcName,sFunction) Values(@_Inner_Proc_Name, @_Inner_FuncBottomDs) --Exec --Print '---------------------EXEC--' Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, REplace(Replace(Replace(@_Inner_FuncDataSet,'TOKEN_ARGUMENTS',' _ ' + @@ArgList + @@sCR),'dsGet','intExec'),'dataset','Integer')) Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, replace(@_Inner_Dims,'dim',@@sTab + 'DIM ')) Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, replace(@_Inner_ParamTop,'TOKEN_PARAM_LIST',@@ParmList)) Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '''*************************************************************************') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '''***Execute the stored proc *********************************************') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '''***Throw Exception during debugging but only write to log after release**') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '''*************************************************************************') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'Try') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'cmdDb.Connection = cnDB') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'cnDB.Open() '' Open Db connection') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'Return cmdDB.ExecuteNonQuery()') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'Catch ex As System.Data.OleDb.oledbException') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + ' Throw New System.Exception(ex.ToString)') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'Catch ex As System.Exception') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + ' Throw New System.Exception(ex.ToString)') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'Finally') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + ' If cnDB.State = ConnectionState.Open Then') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + ' cnDB.Close() '' Close Db Connection') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + ' End If') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, @@sTab + 'End Try') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, 'End Function') Insert tblCaseFunctionsExec(sProcName,sFunction) Values(@_Inner_Proc_Name, '') ------------------------ --------------Reset HERE Set @@ArgList = '' Set @@ParmList = '' -- Get the next ProcName. FETCH NEXT FROM curProcNames INTO @FuncDataSet, @FuncDataReader, @FuncDataSetArgOpt, @FuncDataReaderArgOpt, @FuncExNonQ, @FuncExNonQArgOpt , @Dims, @ParamTop, @ParamBottom, @FuncBottomParamDebug, @FuncBottomDs , @sProcName, @CommandText, @CommandType, @PROC_NAME , @RetValOleDb ,@RetValSql END CLOSE curProcNames DEALLOCATE curProcNames /* */ select replace(replace(sFunction,'SqlDbType.DbDate','SqlDbType.Datetime'),'SqlDbType.Integer','SqlDbType.Int') from tblCaseFunctionsDs where sprocname like 'spSel%' order by id select replace(replace(sFunction,'SqlDbType.DbDate','SqlDbType.Datetime'),'SqlDbType.Integer','SqlDbType.Int') from tblCaseFunctionsExec where sprocname like 'spDel%' or sprocname like 'spInsUpd%' order by id GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO