VB6/ADO Stored Procedure Call Generator

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