July 19, 2013 at 12:35 am
I have a table
create table searchtbl
(
skills varchar(100),
position varchar(40),
location varchar(50)
)
with records as
skills position location
sqldevelopercontract hyd
javadeveloperpermanent hyd
sqldeveloperpermanent Bang
.netdevelopercontract Bang
oracledevelopercontract chennai
phpdeveloperparttime hyd
.netdeveloperpermanent hyd
Now,i want to write a stored procedure when i pass a parameter from front end example if i have to seach for .net delvelopr i must only get the details of all .net developers,like that if i want to search hyd employes i must get all the details of hyd people and if i didn't pass any i must get all the records.Is it possible to write all those in on stored procedure.If so how?
July 19, 2013 at 12:57 am
You'll probably want to use a dynamic catch-all query[/url].
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 19, 2013 at 1:39 am
If you are able to specify on which column you are searching, then below is a simple solution:
create procedure search_row
(
@skills nvarchar(100) = null
, @position nvarchar(40) = null
, @location nvarchar(50) = null
)
as
begin
select
skills
, position
, location
from #searchtbl
where
skills = coalesce(@skills, skills)
and position = coalesce(@position, position)
and location = coalesce(@location, location)
end
go
-- execution samples
exec search_row null, 'contract'
exec search_row 'sqldeveloper'
exec search_row null, null, 'Bang'
exec search_row
July 19, 2013 at 1:40 am
Thanks for your reply.can you please help me in using the dynamic query ?
July 19, 2013 at 1:41 am
HanShi (7/19/2013)
If you are able to specify on which column you are searching, then below is a simple solution:
create procedure search_row(
@skills nvarchar(100) = null
, @position nvarchar(40) = null
, @location nvarchar(50) = null
)
as
begin
select
skills
, position
, location
from #searchtbl
where
skills = coalesce(@skills, skills)
and position = coalesce(@position, position)
and location = coalesce(@location, location)
end
go
-- execution samples
exec search_row null, 'contract'
exec search_row 'sqldeveloper'
exec search_row null, null, 'Bang'
exec search_row
I recommend that you read the article that I posted too.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 19, 2013 at 1:50 am
HanShi (7/19/2013)
If you are able to specify on which column you are searching, then below is a simple solution:
Simple, yes, providing performance isn't a consideration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2013 at 2:01 am
@ dwain.c.
Once again thank you but what i wanted is somewhat different.for example in the execution samples which you provided i want as below:
Instead of this : exec search_row null, null, 'Bang' i want exec search_row 'bang'
I don't want any null values to be written.similarly all others.hope you got me.
Thank you
July 19, 2013 at 2:02 am
dwain.c (7/19/2013)
I recommend that you read the article that I posted too.
Thanks Dwain, I've read the post.
But sometimes (when the table is small) a stored procedure that is easier to read can be perferred above the performance gains.
July 19, 2013 at 2:07 am
iiit.raju (7/19/2013)
Instead of this : exec search_row null, null, 'Bang' i want exec search_row 'bang'I don't want any null values to be written.similarly all others.hope you got me.
For this case you have to change the AND's to OR's in the WHERE clause:
create procedure search_row
(
@search nvarchar(100) = null
)
as
begin
select
skills
, position
, location
from #searchtbl
where
skills = coalesce(@search, skills)
or position = coalesce(@search, position)
or location = coalesce(@search, location)
end
July 19, 2013 at 2:13 am
Thank you.It worked correctly but if i want to use with multiple items then there is a problem in this.i want both.
For example:
search_row 'sqldeveloper','hyd'
so here i want all the records who skills is sqldeveloper and location is hyd
HanShi (7/19/2013)
iiit.raju (7/19/2013)
Instead of this : exec search_row null, null, 'Bang' i want exec search_row 'bang'I don't want any null values to be written.similarly all others.hope you got me.
For this case you have to change the AND's to OR's in the WHERE clause:
create procedure search_row(
@search nvarchar(100) = null
)
as
begin
select
skills
, position
, location
from #searchtbl
where
skills = coalesce(@search, skills)
or position = coalesce(@search, position)
or location = coalesce(@search, location)
end
July 19, 2013 at 2:19 am
The query execution won't get any better with this solution. But the code below will do what you want:create procedure search_row
(
@search_1 nvarchar(100) = null
, @search_2 nvarchar(100) = null
, @search_3 nvarchar(100) = null
)
as
begin
select
skills
, position
, location
from #searchtbl
where
(skills = coalesce(@search_1, skills)
or position = coalesce(@search_1, position)
or location = coalesce(@search_1, location)
)
and
(
skills = coalesce(@search_2, skills)
or position = coalesce(@search_2, position)
or location = coalesce(@search_2, location)
)
and
(skills = coalesce(@search_3, skills)
or position = coalesce(@search_3, position)
or location = coalesce(@search_3, location)
)
end
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply