Input varchar parameter Get in And Help Me

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

General SQL Server Forums

New to SQL Server Programming

Input varchar parameter Get in And Help Me

Author

Topic

EitanBoss
Starting Member

2 Posts

Posted - 2010-12-04 : 07:49:10

How to use input varchar parameter to indicate a pattern in name
Here The Problem:
alter procedure sp_GetNumClients
@strings varchar(10),@num int output
as
begin
select @num = count (*) from Client where name like '%@strings%' return @num
end

Sachin.Nand

2937 Posts

Posted - 2010-12-04 : 08:05:15

Have a look at LIKE predicates in Books Online.

PBUH

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 08:06:16

You have to use dynamic SQL (1 option). The string you pass isn't treated as "list" it is treated as a literal string


alter procedure sp_GetNumClients
@strings varchar(10),@num int output
as
Begin

Declare @sql varchar(150)
Select @SQL = 'select count (*) from Client where name in(' + @strings + ')'
Select @num = Exec (@SQL)

return @num
end

If the pattern is something which is potentially contained in the "name" column, then do

alter procedure sp_GetNumClients
@strings varchar(10),@num int output
as
Begin

Declare @sql varchar(150)
Select @SQL = 'select count (*) from Client where name like ''%' + @strings +'%'''
Select @num = Exec (@SQL)

return @num
end[/code]

Poor planning on your part does not constitute an emergency on my part.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:10:54

No need of dynamic SQL for your requirement

CREATE procedure sp_GetNumClients
@strings varchar(10),@num int output
as
begin
select @num = count (*) from Parameter where Parametername like '%'+ @strings + '%'
return @num
end

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:12:30

Ohhh I replied before your edit...

Vaibhav T

If I cant go back, I want to go fast...

EitanBoss
Starting Member

2 Posts

Posted - 2010-12-04 : 08:15:41

vaibhavktiwari83 And dataguru1971
Thanks You Very Mucht You Help Me Alot!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:21:44