Loading

How to write procedures with one Parameter even if i declare two parameters?

How to write procedures with one Parameter even if i declare two parameters?

Jesupriyan

16y2.9kSQL

Hi!,

I have declared two parameters in Stored Procedures.

I want to write 2 sections.

One section has only one parameter. So I used that parameter only. If I didnt mentions the 2nd parameter in coding means It shows an error.

For Ex.

Fname varchar(50),

DOB Date,

flag varchar(50)

===============

if flag='NameWise' then

select * from tbl_sample where FirstName=Fname;

elseif flag='DateWise' then

select * from tbl_sample where DateofBirth=DOB;

end if;

====================

?????

From the Above when If use 'Name Wise' means I will pass only the 'name'. So It shows 'DOB' error which has not declared.

Reply me soon. Please

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jesupriyan16y ago
Thanks for ur reply.

But I want the coding in MySQL. Ur link which u was sent for XML format know.

Thank u.
Amit Choudhary16y ago
hi friend,

well if you want some thing like parameter array then you can go to the following article will tell you how to pass any no. of parameters:

http://weblogs.asp.net/dwahlin/archive/2007/02/16/passing-an-array-of-values-to-a-stored-procedure-in-sql-server-2005.aspx

Don't forget to mark the answer if it helps.
Jesupriyan16y ago
Hi,

Ok If I have two parameters I can pass it with empty strings.

Suppose I have 10 more than 20, etc means what could I do?.

I want to write the Stored Procedures like for One Form. Ex: "Category Master"

For That,

I have 10 paramaters

Insert = 10 params
Select = 1 param
Delete = 1 param is enough

Here, for select or delete, I have to pass the 9 parameters with Empty. It time \s tkan know.

Any other way??????????????????????????????/
Amit Choudhary16y ago
hi friend,
As i understood with your problem specified
I suggest you to create a stored procedure that takes 3 arguments, first is your flag and according to flag you can pass the value of DOB or fname.

if you call SP with flag value 'NameWise' then send the fname and pass DOB as Empty string.

or if you call SP with flag value 'DateWise' then send the DOB and pass fname as Empty string.

Please mark the answer if it helps you.