Using wildcards with Stored procedures 1

[Zirak]

Hi,
is it possible to use wildcards with Parameter stored procedure and pass somthing like 415% as the parameter to the SP.
I am trying it but it doesn't work.
I use the Query Analyzer and run the SQL Statment of a Stored Procedure with a WHERE statment and wildcards, it works, but when I try to pass the same value as a parameter to a SP it fails.

Can any one help me?

Thanks

 

[swampBoogie]

Well, it depends on what you are trying to do with the parameter. Could you show some code and explain what you mean by fails.

 

[ClaireHCM]

create table table1(Date datetime,Code int,Amount money)
go
insert into table1 select '03/02/2003',20001,570
insert into table1 select '04/03/2002',30001,980
insert into table1 select '05/04/2001',4001,180
insert into table1 select '06/05/2000',501,60
insert into table1 select '07/06/1999',61,57
go
--------------------------------
create proc proc1 @code varchar(10)
as
declare @str varchar(100)
set @str = "select * from table1 where code like '%"+@code+"%'"
exec (@str)

exec proc1 '0'
go

claire

 

[Zirak]

thanks got it!

 

[Zirak]

I have another question though:

Is it possibel to pass a wild card to the parameter stored procedure from the dialog input box?

My main problem is that I have a search form that I provide the user with several parameters that they can fill in.
After they hit a button I have a parameter stored procedure that the values are passed to it.
I want to pass the values to the SP from the form where they are not null.
However if the value from the form is null (the user is not search based on that field) I want to pass a wild card to the SP like %.

But it looks like it doesn't work

 

[ClaireHCM]

Hi,
Could you give some example data....it's little dificult to imagine....^^

claire

 

[Zirak]

Thanks for your time Claire.

I have a Communications table with the following fields:

Caller
Date
Building
Status
Manager

I have a search form.
On my search form I have control boxes for each of the above fields.

For example if the user wants to see all communications on 1/4/2003 for Building XXX, I would want to run a stored procedure with 5 parameters where Date and Building get their values from the control box and those parameters that their value is Null are passed a wildcard of % so it will return all Communications for Building XXX on 1/4/2003 for all values of "callers", all values of "managers " and all values of status.


I don't know if you got my point?

Thanks a lot

 

[ClaireHCM]

Try this----Here I set all are varchar,if you have datetime you just have to do conversion.



alter procedure proc1 @coller varchar(20),@date varchar(20),@build varchar(20),@status varchar(10)
,@manager varchar(30)
as

declare @yr varchar(4)
declare @mm varchar(2)
declare @dd varchar(2)
if @date =''
begin
set @yr = ''
set @mm = ''
set @dd = ''
end
else
begin
set @mm = month(@date)
set @yr= year(@date)
set @dd = day(@date)
end

declare @string varchar(1000)

set @string = "select * from table_name where coller like '%"+@coller+"%' and month(date) like '%"+@mm+"%' and day(date) like
'%"+@dd+"%' and year(date) like '%"+@yr+"%' and building like '%"+@build+"%' and status like '%"+@status+"%' and
manager like '%"+@manager+"%'"
--exec (@string)
print @string



exec proc1 '123,'2/3/2003','','',''


So tired~~~

 

[Zirak]

Thanks a lot.
This would help a lot.
But in my case, for example if in the Caller box the user enters "S12" for building this SP will return all buildings that that have S12 in the middle like GHS12GH or dS1211221,
right?

I really appreciate the time and effort you put answering my question.

 

[ClaireHCM]

Sure it will.That's why it's called wildcard!!

claire

 

[Zirak]

another way that I tried is to set the default to %. So if the param is not passed they will consider all values for that param, but for some reason it woun't work that way.
For some reason if I set the default to % it won't show every thing. Don't know why.

 

[ClaireHCM]

AS FAR AS I KNOW STORED PROCEDURE NEEDS YOU TO PASS PARAMETERS AS MANY AS YOU DEFINED IT.HOW YOU ARE GOING TO SET DEFAULT FOR THOSE PARAMETERS WHICH NEED TO BE PASSED WHEN EXCUTING THAT PROCEDURE?

 

[Zirak]

I thought that.
But according to this article (

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/8_des_07_8.htm)

"The default can include wildcard characters (%, _, [] and [^]) if the stored procedure uses the parameter with the LIKE keyword."

"The following variation of the stored procedure au_info has defaults with wildcard characters for both parameters:

CREATE PROC au_info2 @lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'

AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id

WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname



If au_info2 is executed with no parameters, all the authors with last names beginning with the letter D are displayed:

EXECUTE au_info2

GO"


I don't know what I'm doing wrong but this is not working for me!

 

[Zirak]

Never mind Got it.




Thanks a lot,
I greatly appreciate your help!

 

[ClaireHCM]

Which parameter you want to set default?Very easy...
if you want to set manager as default '%'



alter procedure proc1 @coller varchar(20),@date varchar(20),@build varchar(20),@status varchar(10)
,@manager varchar(30)='%'
as

declare @yr varchar(4)
declare @mm varchar(2)
declare @dd varchar(2)
if @date =''
begin
set @yr = ''
set @mm = ''
set @dd = ''
end
else
begin
set @mm = month(@date)
set @yr= year(@date)
set @dd = day(@date)
end

declare @string varchar(1000)

set @string = "select * from table_name where coller like '%"+@coller+"%' and month(date) like '%"+@mm+"%' and day(date) like
'%"+@dd+"%' and year(date) like '%"+@yr+"%' and building like '%"+@build+"%' and status like '%"+@status+"%' and
manager like @manager"
--exec (@string)
print @string



exec proc1 '123,'2/3/2003','',''

 

[Zirak]

Thanks.

I don't know why I still get an error for one of my fields that is int Data type, the error is:

"Syntax error converting the varchar value '%' to a column of data type int"

% works for varchars but doesn't work for int

 

[ClaireHCM]

You have to do conversion....

if your coller is in int datatype.modify the @string like this

set @string = "select * from table_name where

convert(varchar(20),coller)

like '%"+@coller+"%' and month(date) like '%"+@mm+"%' and day(date) like
'%"+@dd+"%' and year(date) like '%"+@yr+"%' and building like '%"+@build+"%' and status like '%"+@status+"%' and
manager like @manager"

 

[Zirak]

Thanks.
Its working!!