how to create a procedure for search data,id,name

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi, I have a table as id int, age int, name varchar(20), date datetime
and I have created a stored procedure as

SQL

create procedure search (@text varchar(50))
as
  select *from employees where 
    cast(id as varchar)=cast(@text as varchar) or
    cast(age as varchar)=cast(@text as varchar) or
    cast(name as varchar)=cast(@text as varchar) or
    cast(date as varchar(30))=cast(@text as varchar)

When I search for id, name, age it is giving me the correct output but
when I search for date it is not giving me any output. Even when that date exist in that table.

How can I search for a date?

Please help

Posted 10-Nov-10 7:42am

software_Engi08

Updated 10-Nov-10 7:51am

Henry Minute

v3

Add a Solution

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)

Henry Minute · Answer 1 · 2010-11-10T07:57:00

Solution 1

I am by no means an SQL expert but a quick google gave me this[^].

From a quick reading I would suggest translating your @text into a DateTime for comparison, rather than converting the DateTime into text.

Posted 10-Nov-10 7:57am

Henry Minute

Comments

software_Engi08 10-Nov-10 14:05pm

ok ,fine when i change the datatype of @text to datetime it is working but then it is not working for id,name,
age now what should i do to all work .please help

Henry Minute · Answer 2 · 2010-11-10T08:17:00

I think you might have misunderstood, or I did not explain properly :) .

I meant that you should change your SP to something like

SQL

create procedure search (@text varchar(50))
as
  select *from employees where
    cast(id as varchar)=cast(@text as varchar) or
    cast(age as varchar)=cast(@text as varchar) or
    cast(name as varchar)=cast(@text as varchar) or
    date = CAST(@text AS datetime))

As I said before, I am not an proficient in SQL, so the option I have shown for the date comparison may not be correct for your data. It may be that one of the SELECT convert(datetime,@text,4) would be a better fit.