do help problem in stored procedure select

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

SQL Server 2005 Forums

Transact-SQL (2005)

do help problem in stored procedure select

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-03 : 02:33:23

hi all....
i have a table as collegedetails having columns named as college id,collegename ,shortname,address, contactnumber.

i need to make a stored procedure in which whenever any kind of input is passed it gives the four columns college name,short name,city as output.
Input parameters can be of any type ...
for example if user passes 'sym' it should provide the records of all the colleges having 'sym' in there collegedetails column...

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-03 : 03:19:20

SELECT *
FROM collegedetails
WHERE collegename LIKE %sym% OR shortname LIKE %sym% OR address LIKE %sym% OR contactnumber LIKE %sym%

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:20:13

declare @type varchar(32)
set @type = 'sym'
select * from collegedetails where collegename like '%'+@type+'%'

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-03 : 03:29:42

bklr what do you think about my query?

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:32:49

quote:
Originally posted by AvanthaSiriwardana

bklr what do you think about my query?

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

hey avantha, its just fraction of seconds man i haven't seen ur posted query k

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-03 : 03:33:04

i think i am not able to describe my problem correctly to you people...
suppose i make a parameter @info in my procedure...and in where clause i put some condition @info is the information i want to match in my table....then how the procedure will change according to the requirment???

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-03 : 03:35:39

abcd
you can use both my query and bklr query as well

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:36:34

just check this sample sp
create proc dbo.usp_sample
(
@type varchar(32),
)
AS
SET NOCOUNT ON
BEGIN

select * from urtable where empname like '%'+@type+'%' -- inbetween word

select * from urtable where empname like ''+@type+'%' -- type will starts with given parameter

select * from urtable where empname like '%'+@type+'' -- end with given word

END
SET NOCOUNT OFF

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-02-03 : 05:26:29

hey thank you all......
i got my solution...
God bless.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources