Question
Can I have optional parameters to my stored procedures?
Answers
Yes, you can have a stored procedure that has optional parameters. You can do this by setting a default value for
each parameter that you want to make optional. The default value is typically NULL, but it doesn't have to be. Here
is some sample code, ready to test in Query Analyzer:
CREATE PROCEDURE foo
@param1 VARCHAR(32) = NULL,
@param2 INT = NULL
AS
BEGIN
PRINT '-------------------------'
PRINT COALESCE
(
'@param1 = '+@param1,
'@param1 was empty'
)
PRINT COALESCE
(
'@param2 = '+CAST(@param2 AS CHAR(9)),
'@param2 was empty'
)
/*
-- sample usage in a query
SELECT columns FROM table WHERE
(@param1 IS NULL OR column1 = @param1)
AND
(@param2 IS NULL OR column2 = @param2)
*/
END
GO
EXEC foo @param1='bar', @param2=4
EXEC foo @param1='bar'
EXEC foo @param2=4
EXEC foo 'bar',4
EXEC foo 'bar'
EXEC foo
For an Access stored query, you can use the following logic:
PARAMETERS Param1 VARCHAR(32), Param2 INT;
SELECT columns FROM table WHERE
([Param1] IS NULL OR column1 = [Param1])
AND
([Param2] IS NULL OR column2 = [Param2])
  Your Comment




  • Contributors: *,
More Software Questions..
What is the inputsplit in map reduce software?
What is software configuration management?
What Is Java Api For Xml-based Rpc (jax-rpc)?
How can you implement fine-grained auditing?
What is IBM’s simple explanation for Big Data’s four critical features?
What is static synchronized method in JDBC API? Give an example?
What does the NULLIF function do?
What happens if a start method is not invoked and the run method is directly invoked?
Should we override finalize method
what is the difference between mysql_fetch_array and mysql_fetch_object?
How will XML affect my document links?
Why to use Style Sheets?
What are Filters in MVC?
Can you explain Application layer in OSI model?
How to define new testplan attributes?
What are the minimum system requirements to run Photoshop? Is it possible to run Photoshop over linux?
Which oracle package is used to manage the oracle lock management services?
What is Latch Up? Explain Latch Up with cross section of a CMOS Inverter. How do you avoid Latch Up?
What is marker interface?
What types of partitioning are there for BW?


Search
Can you Answer!!
  • Q What is an Internal Table?
  • Q You are given 7 identical balls and another ball that looks the same as the others but is heavier than them. You can use a balance only two times. How would you identify which is the heavy ball?
  • Q Why should i trade forex?
  • Q How do you execute a PHP script from the command line?
  • Q List r/3 user types?
  • Q Which river is known as 'Churni' in ancient times?
  • Q Which is considered as the world’s worst industrial disaster ?
  • Q How many Airports are in Himachal Pradesh?
  • Q What was the ancient name of Bengal?
  • Q The train is arriving ___ platform number 4:
  • Q What is atopic dermatitis?