case statement with wild card query

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)

case statement with wild card query

Author

Topic

jonekim
Starting Member

35 Posts

Posted - 2012-05-15 : 11:43:10

I've a table

create table user (userId nvarchar(10), userName nvarchar(10))

insert into user
select 'NAME1','name1'
union all select 'NAME2','name2' 
union all select 'NAME3','name3'
union all select 'NAME4','name4'

I want to create stored procedure for select statement for two cases.
1. passing name of user
2. passing wild card parameter
via single stored procedure. something like

case1:
create procedure select_name
(@userName nvarchar(10))
as
select * from userTable
where userName = @userName
case2:
create procedure name_wildcard
(@userName nvarchar(10) = '%')
as
begin
set nocount on;
select userId,userName from userTable
where userName like @userName

I want to combine these two stored procedures into single stored procedure with case statement. how can I do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-15 : 11:59:01

I'm assuming the 2 cases are the same result set, just differenvt where clauses. In that case just try in one query
set nocount on;
select userId,userName from userTable
where userName like @userName

If an actual name is passed in that like will work just as and "=", if the "%" is passed in you'll get all records from the table.

Jim

Everyday I learn something that somebody else already knew

jonekim
Starting Member

35 Posts

Posted - 2012-05-15 : 12:16:20

thank you jimf. my problem is solved.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-15 : 12:29:05

Glad to help!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources