Detect this parameter difference in C#

Question

Following are the ways by which I can declare parameters inside a StoredProcedure.

@Doc_Type_Id int

and

@Doc_Type_ID int=null

Second implementation shows that this parameter is optional.

Now I am dynamically detecting parameters using

SqlCommandBuilder.DeriveParameters(cmd);

How can i differentiate between these two types of parameter. ParameterDirection of both is Input only. Please suggest.

Answer 1

I don't think you can tell the difference. Is there a specific reason you need to?

My main response, is really to see if you actually need to use SqlCommandBuilder to derive the parameters for a stored procedure like this? Reason being, you incur a performance hit due to it having to do a round-trip to the database to get this information.

Answer 2

Hi,

You can get the paramater list by calling:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='yourSPnameHere'

But I think this link can really help you

http://www.codeproject.com/KB/database/FindDefaultValueSPParams.aspx

I guess it can be useful.

Answer 3

I tried creating a SP in SQL SERVER 2005

alter PROCEDURE test
(
  @no INT,
  @id INT=0
)
AS 
 BEGIN
  SET @id=5
  PRINT @id
 END

Now when i use this query

 SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='test'

Results are same for both parameters

If i use different query

 SELECT
    p.name,    
    pm.parameter_id,
    pm.name AS parameter_name,
    pm.has_default_value,
    pm.default_value
 FROM sys.procedures p
   JOIN sys.parameters pm ON p.object_id = pm.object_id
   JOIN sys.types t ON pm.system_type_id = t.system_type_id
 WHERE p.name = 'test'

results are same from

pm.has_default_value,
pm.default_value

I think even SQL Server doesnot know this difference.

Answer 4

Use SET FMTONLY ONto allow validating the parameter values without modifying data or returning rows. Then execute the procedure once for each parameter while setting its value to DEFAULT, and all others to NULL. An exception is returned for each non-optional parameter which can be handled by a catch block.

CREATE PROC GetContacts(@name int = NULL, @phone int, @email int)
AS RETURN
GO

SET FMTONLY ON
EXEC GetContacts DEFAULT, null, null
EXEC GetContacts null, DEFAULT, null
EXEC GetContacts null, null, DEFAULT
SET FMTONLY OFF
GO

/*
-- Results
Msg 201, Level 16, State 4, Procedure GetContacts, Line 0
Procedure or function 'GetContacts' expects parameter '@phone', which was not supplied.
Msg 201, Level 16, State 4, Procedure GetContacts, Line 0
Procedure or function 'GetContacts' expects parameter '@email', which was not supplied.
*/