Create .NET parameters

set nocount on
/******************************************************************************
	File: createParameter.sql

	Desc: This file scripts out the parameters needed for VB.NET code
	      To use just change the name of the sp below and run in query analyser
        with results in text
	Auth: Peter Livesey 
	Date: 20/2/2006 
*******************************************************************************
	Change History
*******************************************************************************
	Date:		Author:				Description:
   
*******************************************************************************/

declare @sp_name varchar(200)



set @sp_name= 'dbo.usp_procname
SELECT ParametersList = 
'.Parameters.Add(New SqlParameter("' 
	+ sc.name + '", SqlDbType.' + 
	 st.name
	+
	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
	 + ')).value =' +
	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 'g'
		WHEN 'varbinary' 	THEN 'vnt'
		WHEN 'varchar' 		THEN 'str'
		ELSE 'str'
	END
	+
	REPLACE(REPLACE(SUBSTRING(sc.name, 2, LEN(sc.name) - 1), '_', ' '), ' ', '')
	

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