views:

158

answers:

4

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.

+1  A: 

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.

AdaTheDev
yes,I understand that there is a performance hit. I need it because i am maintaining an application. Now i am using SqlHelper.cs from Data access block. I have added this new parameter to storedProcedure and set it to null.If we use normal command and connection objects it would have worked fine.But in this case there is a paramter mismatch because SqlCommandBuilder.DeriveParameters(cmd) gets parameter count as 4 and try to assign values to them but already coded functions in DAL have only 3 parameters.
Rohit
A: 

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.

burak ozdogan
+1  A: 

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.

Rohit
A: 

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.
*/
Anthony Faull