Optional parameters in SQL Server stored procedure

by

You can declare it like this:

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* Check for the NULL / default value (indicating nothing was passed) */
if (@Parameter3 IS NULL)
BEGIN
    /* Whatever code you desire for a missing parameter */
    INSERT INTO ........
END

/* And use it in the query as so */
SELECT *
FROM Table
WHERE Column = @Parameter

More Related Contents:

  1. SQL Server Insert if not exists
  2. Enable ‘xp_cmdshell’ SQL Server
  3. Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation
  4. SQL MERGE statement to update data
  5. How to get the last record per group in SQL
  6. A transport-level error has occurred when receiving results from the server [closed]
  7. How do I find a stored procedure containing ?
  8. storing passwords in SQL Server
  9. How does sql server sort your data?
  10. Procedure or function !!! has too many arguments specified
  11. How to restore to a different database in SQL Server?
  12. Reporting Services permissions on SQL Server R2 SSRS
  13. T-SQL substring – separating first and last name
  14. How to get week number of the month from the date in sql server 2008
  15. Entity Framework Database.SetInitializer simply not working
  16. Cannot open backup device. Operating System error 5
  17. What is the default transaction isolation level for SQL Server with ADO.NET?
  18. T-SQL strip all non-alpha and non-numeric characters
  19. Execute a SQL Stored Procedure and process the results [closed]
  20. Avoiding IF ELSE due to variable been NULL
  21. How to execute a stored procedure inside a select query
  22. Check if a parameter is null or empty in a stored procedure
  23. Stored procedure hangs seemingly without explanation
  24. Can I pass variable to select statement as column name in SQL Server [duplicate]
  25. SQL Server 2008 Connection Error “No process is on the other end of the pipe”
  26. SSIS LookUp is not dealing with NULLs like the docs say it should
  27. Populate Dataset With Table Names From Stored Procedure
  28. Recursive Child/Parent queries in T/SQL
  29. Bulk insert using stored procedure
  30. Forcing code-first to always initialize a non-existent database?

Leave a Comment