Once devs are pushed toward using parameters, they get confused as to why their sp is not reused and has many compilations when in use.

The reason is most of the time, that the parameters are defined with a wrong datatypemapping in the application.

I use this proc the generate the .Net-code to define and use the parameters for a commandobject named sqlcmd. A simple copy/paste helps out in this case.

It is primitive, and it has some flaws, but it helps out in 90 % ...

CREATE  proc spc_ALZ_CreateSqlParameter 

   @ProcedureName sysname

as

begin

set nocount on

Select cast('        wrkCmdParam = New SqlClient.SqlParameter' as varchar(4000))

+ char(13) + '        With wrkCmdParam'

+ char(13) + '            .ParameterName = "' + Parameter_Name + '"'

+ char(13) + '            .SqlDbType = SqlDbType.' + Data_Type

+ char(13) + '            ''.DbType = DbType.String'

+ char(13) + '            ' + isnull('.Size = ' + cast(Character_Maximum_Length as varchar(15)) , '''.Size = ') + ''

+ char(13) + '            .Direction = ParameterDirection.' + case Parameter_Mode when 'IN' then 'Input' else '???' end

+ char(13) + '        End With'

+ char(13) + '        sqlcmd.Parameters.Add(wrkCmdParam) '

+ char(13) + ' '

--select *

FROM INFORMATION_SCHEMA.PARAMETERS

where SPECIFIC_NAME = @ProcedureName

order by Ordinal_Position

print '---'

select ' sqlcmd.parameters("' + Parameter_Name + '").value = rij.Item("' + replace(Parameter_Name,'@','') + '")'

FROM INFORMATION_SCHEMA.PARAMETERS

where SPECIFIC_NAME = @ProcedureName

order by Ordinal_Position

end