how to write stored procedure to pass 8 parameters

Question

0.00/5 (No votes)

See more:

, +

We are passing 8 parameters in stored procedure

i have to check all parameters are passing or not and the result has to be display if all parameters passed or if any one value is passing or if any two,three ,four,five ,six and all passing.

if all r null also i have to display the total result

i write the stored procedure for it.
It has many conditions i.e 500 conditions

but the problem has occured while executing the query displays the error like it shows that query has too many lines.
you have to break up the query

how to write stored procedure for above problem

Posted 21-Aug-11 21:05pm

Member 7932936

Add a Solution

Comments

walterhevedeich 22-Aug-11 3:18am

Would you mind posting the exact error message?

Member 7932936 24-Aug-11 1:34am

my error message is

Msg 191, Level 15, State 1, Procedure procadvancedsearch, Line 466
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anurag Gandhi · Answer 1 · 2011-08-21T21:16:00

Solution 1

For parameter problems, use default value in parameter.

To simplify the conditions, you can split the query according to the scenario you have. We can't help more unless you tell us what your query is all about.

Posted 21-Aug-11 21:16pm

Anurag Gandhi

Comments

Member 7932936 22-Aug-11 3:24am

this is my stored procedure.

create proc procadvancedsearch(@keyword varchar(max)=null,@joblocation varchar(max)=null,@experience varchar(max)=null,@industry varchar(max)=null,@function varchar(max)=null,@role varchar(max)=null,@jobfreshbydays datetime=null)
as
begin
if(@keyword IS NOT NULL and @joblocation IS NOT NULL and @experience IS NOT NULL and @industry IS NOT NULL and @function IS NOT NULL and @role IS NOT NULL and @jobfreshbydays IS NOT NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where keyskills Like '%'+ @keyword+'%' and joblocation in (select items from dbo.Split(@joblocation,',')) and @experience between minexperience and maxexperience and jobindustry in (select items from dbo.Split(@industry,',')) and jobfunctionality in (select items from dbo.Split(@function,',')) and jobrole in (select items from dbo.Split(@role,',')) and jobposteddate between @jobfreshbydays and GETDATE()
end
else if(@keyword IS NOT NULL and @joblocation IS NULL and @experience IS NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where keyskills Like '%'+ @keyword+'%'
end
else if(@keyword IS NULL and @joblocation IS NOT NULL and @experience IS NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where joblocation in (select items from dbo.Split(@joblocation,','))
end
else if(@keyword IS NOT NULL and @joblocation IS NOT NULL and @experience IS NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where keyskills Like '%'+ @keyword+'%' and joblocation in (select items from dbo.Split(@joblocation,','))
end
else if(@keyword IS NULL and @joblocation IS NULL and @experience IS NOT NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where @experience between minexperience and maxexperience
end
else if(@keyword IS NOT NULL and @joblocation IS NULL and @experience IS NOT NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where keyskills Like '%'+ @keyword+'%' and @experience between minexperience and maxexperience
end
else if(@keyword IS NULL and @joblocation IS NOT NULL and @experience IS NOT NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where joblocation in (select items from dbo.Split(@joblocation,',')) and @experience between minexperience and maxexperience
end
else if(@keyword IS NOT NULL and @joblocation IS NOT NULL and @experience IS NOT NULL and @industry IS NULL and @function IS NULL and @role IS NULL and @jobfreshbydays IS NULL)
begin
select e.jobtitle,e.jobpostedd

Member 7932936 24-Aug-11 1:35am

my erro message is

Msg 191, Level 15, State 1, Procedure procadvancedsearch, Line 466
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Keith Barrow · Answer 2 · 2011-08-21T21:24:00

Knowing what to do precisely is impossible, as I don't know your situation, but it sounds like your SQL is just too complicated. In general the simplest thing you can do is to extract select subqueries into views break down parts of your SQL into other stored procedures. Another thing you can do is to write a user defined function, the parameter checking seems a prime candidate for this. You can create the user defined functions in c#. I've never actually done this, but it seems simple enough (famous last words!), see this blog entry[^] for guidance.

Hopefully other CPians will chip in with other advice, but this is where I'd start