Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating