Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

What query will show which procedure parameters are optional?

Posted on 2005-04-29
4
Medium Priority
?
275 Views
Last Modified: 2012-05-05
Sp_help my_proc shows parameters used by a stored procedure, named “my_proc”.
"Select * from select * from information_schema.parameters
where SPECIFIC_NAME = ‘my_proc’" also shows parameter definitions.

What query will show me which procedure parameters are optional?  That is what query will show the default values, if any of the parameters for a procedure?
0
Comment
Question by:Satyabodhi
4 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 13895312
There isn't one really.  You would have to parse the CREATE PROCEDURE code, which is apparently exactly the way SQL Server does it.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895483
And a procedure source code is stored in syscomments table, sometimes on multiple rows. Or you can use sp_helptext SP to display the code.
0
 
LVL 34

Accepted Solution

by:
arbert earned 600 total points
ID: 13895764
Look at the information_schema.routines view--it has a 4000byte limitation, but, you can run some queries against it to see parm information:

select * from information_schema.routines

Brett
0
 
LVL 3

Author Comment

by:Satyabodhi
ID: 14003411
Here is a partial solution.  I am waiting for the better tools, like C# coded procedures, in Yukon, before coding the ultimate solution.
create function dbo.normalize_spaces(@string nvarchar(4000))
returns nvarchar(4000)
as
-- Return text with a tab or spaces set to a single space
-- and comma-space set to just comma.
-- Useful for comparing two versions of syscomments or other SQL texts.
-- Bodhi Densmore 19OCT01
BEGIN
      declare @s nvarchar(4000)
      
      set @s = rtrim(ltrim(@string))
      set @s = replace(@s, char(9), ' ' )  -- replace tab with one space
      set @s = replace(@s, '  ', ' ') -- replace two spaces with one
      while charindex ('  ',@s) > 0
            set @s = replace(@s, '  ', ' ') -- replace two spaces with one
      set @s = replace(@s, ', ', ',') -- replace comma space with comma

      return rtrim(ltrim(@s))
END
go
-------------------------------------------------------------------------------
drop function normalize_white_space
go
create function dbo.normalize_white_space(@string nvarchar(4000))
returns nvarchar(4000)
as
-- Return text with a white space set to a single space
-- Useful for comparing two versions of syscomments or other SQL texts.
-- Bodhi Densmore 19OCT01
BEGIN
      declare @s nvarchar(4000)
      
      set @s = rtrim(@string)
      set @s = replace(@s, char(10), ' ' )  -- replace newline with one space
      set @s = replace(@s, char(13), ' ') -- replace carriage return with one space
      return dbo.normalize_spaces(@s)
END
----------------------------------------------------------------------------------
go
CREATE FUNCTION parameter_definition_text
      (@routine_name sysname)
RETURNS nvarchar(3986)
AS
-- Returns first 3986 characters of the parameter definitions for a procedure or function.
-- KNOWN DEFECTS:  Fails when a parameter type uses the AS keyword.
--    Use "@p int" instead of "@p AS int".
-- Created 29APR05 by Bodhi Densmore
BEGIN
declare @last_parm sysname, @first_parm sysname
declare @id int, @text nvarchar(4000)
declare @p1 int, @plast int, @p_as int
--set @id = object_ID(N'p_random_name')
select @first_parm = parameter_name
from INFORMATION_SCHEMA.parameters
where SPECIFIC_NAME = @routine_name
 and SPECIFIC_SCHEMA = 'dbo'
 and ORDINAL_POSITION = 1

if @first_parm is null
      return N' no parameters'
select @last_parm = (
select top 1 parameter_name from INFORMATION_SCHEMA.parameters
where SPECIFIC_NAME = @routine_name
 and SPECIFIC_SCHEMA = 'dbo'
 and ORDINAL_POSITION > 1
order by ORDINAL_POSITION DESC)
IF @last_parm is null
      set @last_parm = @first_parm

select @text = ROUTINE_DEFINITION
from information_schema.routines
where SPECIFIC_NAME = @routine_name
  and SPECIFIC_SCHEMA = 'dbo'
if @text is null
      return N' Encrypted'
select @p1 = charindex(@first_parm,@text)
      , @plast = charindex(@last_parm,@text)
if isnull(@plast,0) = 0
      set @text = substring(@text, @p1, 3999 - @p1)
else begin
      set @p_as = charindex('AS',@text, @plast + len(@last_parm)+1)
      if @p_as = 0
            set @p_as = 4000
      set @text = substring(@text, @p1, @p_as - (@p1+1))
end
set @text = dbo.normalize_white_space(@text)
RETURN @text
END
GO
IF 'testing' is  NULL
BEGIN
      SELECT dbo.parameter_definition_text('list_tables')
      SELECT dbo.parameter_definition_text('Expire_table')
      SELECT dbo.parameter_definition_text('p_random_name')
      SELECT name, dbo.parameter_definition_text(name)
      from sysobjects where type = 'p'
      order by 2,1
END
GO
-------------------------------------------------------------------------------



CREATE FUNCTION parameter_default
      (@parameter_name sysname, @definition_text nvarchar(3986))
RETURNS nvarchar(256)
AS
-- Returns first 256 characters of the parameter default value
-- of the named parameter of a procedure or function.
-- Returns '##encrypted##' if the object is encrypted.
-- Returns '## bad param name:'+@parameter_name when the name is not in the definition_text.
-- KNOWN DEFECTS:  Fails when a parameter type uses the AS keyword.
--    Use "@p int" instead of "@p AS int".
-- Created 29APR05 by Bodhi Densmore
BEGIN
declare @p1 int, @p2 int, @p3 int, @p4 int, @pq int, @pcomma int
declare @excerpt nvarchar(300)
if @definition_text is null or len(@definition_text) < 2
      return NULL
if @parameter_name like '%no parameters%'
      return NULL
if @definition_text = ' Encrypted'
      return N'##encrypted##'
set @p1 = patindex('%'+@parameter_name+'[^0-9A-Z_#]%',@definition_text)
if @p1 = 0
      return N'## bad param name:'+@parameter_name
set @excerpt = substring(@definition_text, @p1 + len(@parameter_name), 36)
set @p2 = charindex('=', @excerpt)
set @pcomma = charindex(',', @excerpt)
if @p2 > @pcomma and @pcomma > 0
      set @p2 = 0
if @p2 = 0
      return NULL
set @pq = charindex('''', @excerpt, @p2)
if @pq = 0 begin
      -- not quoted
      set @p3 = @p1 + len(@parameter_name) + @p2
      set @excerpt = substring(@definition_text, @p3, 60)
      --return @excerpt
      set @excerpt = ltrim(@excerpt)
      set @p4 = patindex('%[, ]%',@excerpt)
      if @p4 = 0 set @p4 = len(@excerpt)
            else set @p4 = @p4 -1
      set @excerpt = left(@excerpt, @p4)
end else begin -- quoted
      set @excerpt = substring(@definition_text, @p1 + len(@parameter_name)+@pq-1, 360)
      --return @excerpt
      set @p3 = charindex('''', @excerpt, 2)
      --return cast(@p3 as nvarchar)
      if @p3 = 0 return N'## malformed quote bug ##'
      while (substring(@excerpt, @p3+1, 1) = '''')
            set @p3 = @p3 + 1
      set @excerpt = substring(@excerpt, 1, @p3)
end
return @excerpt
END
go
if 'testing' is /*not*/ null begin
declare @proc_name sysname, @param_defintion_text nvarchar(4000)
      -- USE NORTHWIND
      set @proc_Name = 'SalesByCategory'
      set @param_defintion_text = dbo.parameter_definition_text(@proc_name)
      select dbo.parameter_default
            ('@OrdYear',dbo.parameter_definition_text(@proc_name))
end
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question