checking for a default value of a parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

checking for a default value of a parameter

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 15:48:59

I am using a query to grab information about the parameters of stored procedures. I am trying to determine if a parameter has a default value. I have tried adding a default value to the parameter of the stored procedure, but the returned value of params.has_default_value stays at zero. What is going on?

Here's the query to pull the stored procedure information:
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BY procname,
params.parameter_id

Here's the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = 'a'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT *
FROM BENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT *
FROM BENEFICIAL_USES_DM
WHERE DISPOSAL_AREA_NAME = @DISPOSAL_AREA_NAME
END

END

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:29:51

see if this helps.

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=277

Chirag

http://www.chirikworld.com

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 16:39:16

The script they have there seems like major overkill. I don't need the default value, I just need to know if one exists.

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 16:45:12

I'm sorry, my reply looks kind of rude, now that i reread it.

I guess that I was just overwhelmed by all that code. I was hoping that I could get the information I needed with a simple query. I would have to loop over the code you linked to for each parameter which might be problematic performance wise.

So the information returned by sys.all_parameters is not correct? What does the column has_defualt_value reference?

Thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:48:42

since if you read the documentation of the sys.all_parameters its states that

quote:

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.

reference : http://msdn.microsoft.com/en-us/library/ms190340.aspx

Chirag

http://www.chirikworld.com

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 16:55:19

quote:

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.

sys.sql_modules just seems to return the entirety of the stored procedure. How can that be used to find out about parameter defaults?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 16:57:05

if you see in the link which i had given to use, the sp does the same. it actually dumps the value
to the temp table and the it loops through and find it out if there is any default value to it .

Chirag

http://www.chirikworld.com

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 16:59:52

quote:
Originally posted by chiragkhabaria

see if this helps.

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=277

Chirag

http://www.chirikworld.com

The problem I see with the script in this link is that it returns NULL if there is no default, but it also returns NULL is the default is NULL. Is there any way to differentiate between the two?

The whole point of this exercise is that I am trying to test before a stored procedure is called if all of the required parameters have been passed in the requests. This is for a web service interface that will be calling the stored procedures. At this point it does not seem doable.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 17:13:34

I guess if there is no default value to a stored procedure then its returns the text NoDefaultExists

Chirag

http://www.chirikworld.com

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 17:30:47

quote:
Originally posted by chiragkhabaria

I guess if there is no default value to a stored procedure then its returns the text NoDefaultExists

Why do you say this? I couldn't find the text "NoDefaultExists" in the script anywhere. Also, when I ran the script, it returns NULL if there is no default, but it also returned NULL if the default was NULL.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 17:38:52

not sure which one you are looking into .. check out this ..

http://education.sqlfarms.com/education/PostAttachment.aspx?PostID=277

and then there is one more function for checking for the default parameter which is using above script

http://education.sqlfarms.com/education/PostAttachment.aspx?PostID=278

Chirag

http://www.chirikworld.com

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 17:56:57

Thanks for the links. I will play with the scripts tomorrow at work.

I really appreciate all the help you've provided. Thank you. I will probably have more questions, but that's it for now.

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 08:37:07

I am getting errors when I execute the create function scripts.

For f_GetsProcParamDefaultValue.sql
I get:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'f_GetsProcParamDefaultValue'.

For f_GetSProcParameters.sql
I get:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'f_GetSProcParameters'.

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 08:40:50

Sorry, silly mistake, I had some text selected when I tried to run the script.

I got f_GetSProcParameters.sql to run successfully, but for f_GetsProcParamDefaultValue.sql I get this error:
Msg 208, Level 16, State 6, Procedure f_GetsProcParamDefaultValue, Line 321
Invalid object name 'dbo.f_GetsProcParamDefaultValue'.

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 09:16:22

Ok, I got it working. I'll try not to post so quickly.

What I need is for the function to return whether or not the parameter is an input or an output. I think it is beyond my abilities to script something like that. Anyone want to give it a shot? I would really appreciate it.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources