Create .NET parameters

,

This file will script out the code needed for .NET parameters. The idea is based on a script to create VB parameters which I found on this site but I updated it to create VB.NET patrameters

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

Rate

3 (2)

Share

Share

Rate

3 (2)