February 26, 2014 at 1:57 am
Using Microsoft SQL Server 8 R2 Standard Edition (10.50.2550.0), I try to execute the following scrip:
use reclamations
go
select O.name
, P.name
, P.system_type_id
, P.max_length
, P.has_default_value
, P.default_value
FROM sys.parameters P INNER JOIN
sys.objects O ON O.[object_id] = P.[object_id] AND O.is_ms_shipped = 0INNER JOIN
sys.sql_modules SQLM ON SQLM.[object_id] = P.[object_id]
WHERE O.type = 'P'
ORDER BY O.name
, P.parameter_id;
which seems to work correctly, excepting that the column [has_default_value] contains only zeroes (0) and the column [default_value] contains only NULLs, although many of the stored procedures in the database do have input parameters declared with default values.
Example:
CREATE PROCEDURE [dbo].[Proc_Tbl_Reclamations_Images_Select_Ex]
(
@Row_ID INT = 0
, @Type INT = 1
)
AS
BEGIN
DECLARE @ReturnValue INT
DECLARE @status INT
DECLARE @Criteria INT
BEGIN TRY
...
The corresponding lines returned by the scrip are:
[font="System"]
name namesystem_type_idmax_lengthhas_default_valuedefault_value
Proc_Tbl_Reclamations_Images_Select_Ex@Row_ID5640NULL
Proc_Tbl_Reclamations_Images_Select_Ex@Type5640NULL
[/font]
Is there a special way of declaring the default value for a parameter so that it appears in sys.parameters?
Thank you for your time and have a nice day!
February 26, 2014 at 4:14 am
That value is for CLR procedures, not T-SQL procedures, per the documentation on sys.parameters. You can only pull out the defaults looking at the actual definitions of the procedures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2014 at 4:44 am
Thanks for the info!
I'll keep on parsing the value returned by OBJECT_DEFINITION(object_id) 🙁
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply