Regarding stored procedure parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Regarding stored procedure parameters

Author

Topic

mukkanti
Starting Member

9 Posts

Posted - 2007-06-29 : 08:21:23

I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 08:24:35

what is the error message ?

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-29 : 08:25:33

You have to make parameters optional by specifying default value and then in the code, you have to handle the condition where parameter value may or may not be passed.

Create Proc test
(
 @x int = 0,
 ..
)
as
if @x = 0
begin
-- no parameter value for @x passed
end
else
Begin
-- Apply parameter value passed for @x
End
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-29 : 13:15:43

Depending on what you are trying to do you can do what Harsh suggested or you do something slightly different like:

CREATE PROC test
(
	@x int = NULL,
	...
)
AS

SELECT 
	*
FROM
	MyTable
WHERE
	(MyValue = @x OR @X IS NULL)
	AND ...
GO

Here is a link about some dynamic search queries:
[url]http://www.sommarskog.se/dyn-search.html[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources