February 18, 2003 at 6:18 pm
I'm needing to determine attributes of stored procedure parameters. I have some queries that interrogate the systables and give me everything I need but one: How can I determine whether a sproc parameter has a default value (and if so, what it is?)
February 18, 2003 at 7:37 pm
Try sp_procedure_params_rowset
Article at : http://p2p.wrox.com/archive/activex_data_objects/2002-02/4.asp
February 19, 2003 at 9:55 am
sp_procedure_params_rowset is an extremely interesting proc(actually 2 procs in
one). After looking at its sql, I can not see where it pulls the defaults from,
or if at all it does. The problem lies in the fact that the cdefault column in
the syscolumns table is always zero and does not link to a row in the
syscomments where the default is stored. In fact the result of every
PARAMETER_HASDEFAULT and PARAMETER_DEFAULT was 0 and NULL and was hard coded as
such in the sp_procedure_params_rowset.
Run this query to see what objects have defaults assigned to columns in your
database. Interestingly enough no procedures OR functions appear in this list.
SELECT OBJECT_NAME(scol.[id]) As ObjectName,
scol.[name],
scom.[text]
FROM syscomments scom
INNER JOIN syscolumns scol
ON scol.[cdefault] = scom.[id]
WHERE scol.[cdefault] > 0
ORDER BY ObjectName,
scol.colid
AFAIK I could not find a way to view the parameter defaults from querying the
systables without actually parsing the text of syscomments itself.
Then I set out to see what I could discover from the ado command object. Paste
the below code into a Form_Load event, and change the three lines specified in
the comments, and make sure you pick a stored procedure that has defaults. When
running this code the ONLY thing it could pick up about the parameters was that
it was nullable, or had a default in other words.
Option Explicit
Private Sub Form_Load()
Dim adoCmd As Object 'ADODB.Command
Dim adoParam As Object 'ADODB.Parameter
Dim adoProp As Object 'ADODB.Property
'CHANGE THESE THREE LINES
Dim strServer As String: strServer = "SERVER"
Dim strDatabase As String: strDatabase = "DATABASE"
Dim strProcedure As String: strProcedure = "STORED PROC"
Set adoCmd = CreateObject("ADODB.Command")
With adoCmd
.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & strDatabase & ";Data Source=" & strServer
.CommandType = adCmdStoredProc
.CommandText = strProcedure
.Parameters.Refresh
For Each adoParam In .Parameters
Debug.Print adoParam.Name & " " & adoParam.Value
'ParameterAttributesEnum
If adParamLong And adoParam.Attributes = adParamLong Then
Debug.Print String(2, vbTab) & "adParamLong"
End If
If adParamNullable And adoParam.Attributes = adParamNullable Then
Debug.Print String(2, vbTab) & "adParamNullable"
End If
If adParamSigned And adoParam.Attributes = adParamSigned Then
Debug.Print String(2, vbTab) & "adParamSigned"
End If
For Each adoProp In adoParam.Properties
Debug.Print String(3, vbTab) & adoProp.Name & " " & adoProp.Value & " " & adoProp.Attributes
Next adoProp
Next adoParam
.ActiveConnection.Close
End With
Set adoCmd = Nothing
End Sub
If anyone knows of a way to get to these defaults, I would be extremely
interested, the only option I have not tried this morning is using DMO. If no
one beats me to it, I will try to do that sometime this week.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
February 19, 2003 at 5:20 pm
On the same token, how do you determine if input parameters for stored procedures are required or not??
February 21, 2003 at 8:35 am
The VB code I posted earlier tells you with the
adParamNullable And
adoParam.Attributes = adParamNullable
test. If true, that parameter is
optional and from VB can be set to Empty. When you set it to Empty from VB ADO
will send the keyword "default" which tells SQL Server to use the supplied
optional parameter from the stored procedure. Now dynamically finding out what
that default value is, is interestingly enough very difficult to do....
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
February 21, 2003 at 5:24 pm
Thanks Tim! I tried your code and it returns exactly what I was looking for.
I looked around and could not find a way to do it from a stored procedure though!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply