Technical Article

VB6/ADO Stored Procedure Call Generator

,

Generates a VB6/ADO procedure prototype, procedure call, and ADO Command Object Parameters.Append calls.  To use, simply set the @spname variable equal to the name of your stored procedure, run, and cut and paste the output into you VB program.  The generated code may require some slight modifications, but this should save you a lot of typing.

DECLARE @spname varchar(128)

SET @spname = 'pr_myStoredProcedure'

SET NOCOUNT ON

SELECT ProcedurePrototype =
CASE st.name
WHEN 'bigint' THEN 'vnt'
WHEN 'binary' THEN 'vnt'
WHEN 'bit' THEN 'bln'
WHEN 'char' THEN 'str'
WHEN 'datetime' THEN 'dt'
WHEN 'decimal' THEN 'vnt'
WHEN 'float' THEN 'dbl'
WHEN 'identity' THEN 'lng'
WHEN 'image' THEN 'vnt'
WHEN 'int' THEN 'lng'
WHEN 'money' THEN 'cry'
WHEN 'nchar' THEN 'str'
WHEN 'ntext' THEN 'str'
WHEN 'numeric' THEN 'vnt'
WHEN 'nvarchar' THEN 'str'
WHEN 'real' THEN 'sng'
WHEN 'smalldatetime' THEN 'dt'
WHEN 'smallint' THEN 'int'
WHEN 'smallmoney' THEN 'cry'
WHEN 'sql_variant' THEN 'vnt'
WHEN 'text' THEN 'str'
WHEN 'timestamp' THEN 'vnt'
WHEN 'tinyint' THEN 'byt'
WHEN 'uniqueidentifier'THEN 'vnt'
WHEN 'varbinary' THEN 'vnt'
WHEN 'varchar' THEN 'str'
END
+ REPLACE(REPLACE(SUBSTRING(sc.name, 2, LEN(sc.name) - 1), '_', ' '), ' ', '')
+ ' As ' +
CASE st.name
WHEN 'bigint' THEN 'Variant'
WHEN 'binary' THEN 'Variant'
WHEN 'bit' THEN 'Boolean'
WHEN 'char' THEN 'String'
WHEN 'datetime' THEN 'Date'
WHEN 'decimal' THEN 'Variant'
WHEN 'float' THEN 'Double'
WHEN 'identity' THEN 'Long'
WHEN 'image' THEN 'Variant'
WHEN 'int' THEN 'Long'
WHEN 'money' THEN 'Currency'
WHEN 'nchar' THEN 'String'
WHEN 'ntext' THEN 'String'
WHEN 'numeric' THEN 'Variant'
WHEN 'nvarchar' THEN 'String'
WHEN 'real' THEN 'Single'
WHEN 'smalldatetime' THEN 'Date'
WHEN 'smallint' THEN 'Integer'
WHEN 'smallmoney' THEN 'Currency'
WHEN 'sql_variant' THEN 'Variant'
WHEN 'text' THEN 'String'
WHEN 'timestamp' THEN 'Variant'
WHEN 'tinyint' THEN 'Byte'
WHEN 'uniqueidentifier'THEN 'Variant'
WHEN 'varbinary' THEN 'Variant'
WHEN 'varchar' THEN 'String'
ELSE 'UNKNOWN'
END
+ ', _'
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xtype
)
WHERE
sc.id = OBJECT_ID(@spname)
ORDER BY
sc.colorder


SELECTProcedureCall = 
'rsRecords("' + sc.name + '"), _'
FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xtype
)
WHERE
sc.id = OBJECT_ID(@spname)
ORDER BY
sc.colorder


SELECT ParametersList = 
'.Parameters.Append .CreateParameter("' 
+ sc.name + '", ' + 
CASE st.name
WHEN 'bigint' THEN 'adBigInt'
WHEN 'binary' THEN 'adBinary'
WHEN 'bit' THEN 'adBoolean'
WHEN 'char' THEN 'adChar'
WHEN 'datetime' THEN 'adDBTimeStamp'
WHEN 'decimal' THEN 'adNumeric'
WHEN 'float' THEN 'adDouble'
WHEN 'image' THEN 'adLongVarBinary'
WHEN 'int' THEN 'adInteger'
WHEN 'money' THEN 'adCurrency'
WHEN 'nchar' THEN 'adWChar'
WHEN 'ntext' THEN 'adLongVarWChar'
WHEN 'numeric' THEN 'adNumeric'
WHEN 'nvarchar' THEN 'adVarWChar'
WHEN 'real' THEN 'adSingle'
WHEN 'smalldatetime' THEN 'adDBTimeStamp'
WHEN 'smallint' THEN 'adSmallInt'
WHEN 'smallmoney' THEN 'adCurrency'
WHEN 'sql_variant' THEN 'adVariant'
WHEN 'text' THEN 'adLongVarChar'
WHEN 'timestamp' THEN 'adBinary'
WHEN 'tinyint' THEN 'adUnsignedTinyInt'
WHEN 'uniqueidentifier'THEN 'adGUID'
WHEN 'varbinary' THEN 'adVarBinary'
WHEN 'varchar' THEN 'adVarChar'
ELSE 'UNKNOWN'
END
+ ', ' +
CASE sc.isoutparam
WHEN 1 THEN 'adOutputParam'
ELSE ''
END
+ ', ' +
CASE
CASE st.name
WHEN 'char' THEN 1
WHEN 'varchar' THEN 1
WHEN 'binary' THEN 1
WHEN 'varbinary' THEN 1
ELSE 0
END
WHEN 1 THEN CONVERT(varchar, sc.length)
ELSE ''
END
+ ', ' +
CASE st.name
WHEN 'bigint' THEN 'vnt'
WHEN 'binary' THEN 'vnt'
WHEN 'bit' THEN 'bln'
WHEN 'char' THEN 'str'
WHEN 'datetime' THEN 'dt'
WHEN 'decimal' THEN 'vnt'
WHEN 'float' THEN 'dbl'
WHEN 'identity' THEN 'lng'
WHEN 'image' THEN 'vnt'
WHEN 'int' THEN 'lng'
WHEN 'money' THEN 'cry'
WHEN 'nchar' THEN 'str'
WHEN 'ntext' THEN 'str'
WHEN 'numeric' THEN 'vnt'
WHEN 'nvarchar' THEN 'str'
WHEN 'real' THEN 'sng'
WHEN 'smalldatetime' THEN 'dt'
WHEN 'smallint' THEN 'int'
WHEN 'smallmoney' THEN 'cry'
WHEN 'sql_variant' THEN 'vnt'
WHEN 'text' THEN 'str'
WHEN 'timestamp' THEN 'vnt'
WHEN 'tinyint' THEN 'byt'
WHEN 'uniqueidentifier'THEN 'vnt'
WHEN 'varbinary' THEN 'vnt'
WHEN 'varchar' THEN 'str'
ELSE 'UNK'
END
+
REPLACE(REPLACE(SUBSTRING(sc.name, 2, LEN(sc.name) - 1), '_', ' '), ' ', '')
+ ')'

FROM
syscolumns sc
JOIN
systypes st
ON(
sc.xtype = st.xtype
)
WHERE
sc.id = OBJECT_ID(@spname)
ORDER BY
sc.colorder


SET NOCOUNT OFF

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating