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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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