URGENT Store Procedure Help

[EscapeUK]

I have the following select statement which works fine

select * from mytable where refnumber1 like '%' and refnumber2 = 32 and refnumber3 = 48

However I have tried to convert this in to a stored procedure and I can't get values to be returned.

Here is the stored procedure:


CREATE PROCEDURE sp_Mysp
@Ref1 varchar(100),
@Ref2 varchar(100),
@Ref3 varchar(100)

AS
select *

from Mytable


Where Mytable.Refnumber1 LIKE @Ref1 And
Mytable.Refnumber2 LIKE @Ref2
Mytable.Refnumber3 LIKE @Ref2

Here are the ways I have tried to call it.

exec sp_mysp '%', '32', '48'

I have also tried

exec sp_mysp '%', 32, 48

 

[Guest_imported]

Try this querry without the first option eg
exec sp_mysp 32, 48 and remove all the code that
was used as part of the Like statement. The query does
not look like a standard SQL query it looks like a JET DB
query.

 

[Andel]

Try this also...

CREATE PROC sp_mySp
@Ref1 varchar(100) = '%',
@Ref2 varchar(100) = '%',
@Ref3 varchar(100) = '%'
AS
SELECT * FROM myTable
WHERE RefNumber1 LIKE '%'+ @Ref1 + '%' AND
RefNumber2 LIKE '%'+ @Ref2 + '%' AND
RefNumber3 LIKE '%'+ @Ref3 + '%'

you will then execute the following as these:

EXEC sp_mySp '', 10, 20
or
EXEC sp_mySp 5, '', 20

in other words, you replace blank parameter with ''

Hope this helps.



Andel
maingel@hotmail.com