| Author |
 Topic  |
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
 Posted - 04/18/2007 : 07:11:35
|
How do Iput wildcards around a number in an sp ? If my user leaves BoxNo blank it will list all boxes
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and BoxNo like %+@strBoxNo%
|
Edited by - Pinto on 04/18/2007 07:12:35
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 04/18/2007 : 07:14:37
|
BoxNo is integer field? or varchar?
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
 |
|
|
pbguy
Constraint Violating Yak Guru
India
319 Posts |
Posted - 04/18/2007 : 07:15:06
|
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or boxno is null)
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 04/18/2007 : 07:15:07
|
and cast(BoxNo as varchar) like '%' + @strBoxNo + '%'
Peter Larsson
Helsingborg, Sweden |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/18/2007 : 08:40:33
|
| BoxNo is Integer. I will try the other solutions. Thank you |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/18/2007 : 08:54:34
|
| I have used PbGuy suggestion as if they enter a 2 they will get boxes 2, 22, 222, 23 etc etc I wanted 2 only. |
Edited by - Pinto on 04/18/2007 09:03:51 |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/18/2007 : 09:11:08
|
| Sorry - it does not return any records if they leave BoxNo blank. It works if a BoxNo is entered. Every record will have a BoxNo, but the user may filter on this if they want to, or list all boxes by leaving BoxNo blank. |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 04/18/2007 : 09:21:57
|
and (BoxNo = @BoxNo or @BoxNo = '')
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/18/2007 : 10:39:44
|
| That does not work because every record will have a BoxNo so if one is not entered to filter on no records satisfy Boxno = '' |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 04/18/2007 : 10:42:15
|
That is NOT the column criteria. It's the parameter criteria.
Have you tried the any of the suggestions made to you?
Peter Larsson
Helsingborg, Sweden |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 04/18/2007 : 10:44:37
|
Have you tried my solution at all?
It says:
and (BoxNo = @BoxNo or @BoxNo = '')
and not:
and (BoxNo = @BoxNo or BoxNo = '')
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/18/2007 : 11:10:50
|
Sorry :( I have tried the solutions and a mix of the solutions.
Here it is time to go home - I will try again tomorrow. |
Edited by - Pinto on 04/18/2007 11:13:18 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 04/18/2007 : 14:32:40
|
I think BPGuy had the solution. Here is some code to try out:DECLARE @Table TABLE (BoxNo INT)
DECLARE @BoxNo VARCHAR(10)
SET @BoxNo = '2'
--SET @BoxNo = ''
INSERT @Table
SELECT 2 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 40 UNION ALL
SELECT 32
SELECT *
FROM @Table
WHERE BoxNo = CAST(@BoxNo AS INT) OR @BoxNo = '' -- OR @BoxNo IS NULL
Depending on what @BoxNo is when the user does not specify anything blank or NULL, you may need to adjust the WHERE clause.
-Ryan |
|
|
|
Pinto
Aged Yak Warrior
United Kingdom
590 Posts |
Posted - 04/19/2007 : 04:26:33
|
Here is my sp now which nearly works !
If I enter a Consignment number only - it works
if I enter a Consignment number and Box number and suffix - it works
If I enter a Consignment number and a Box number and no suffix - it returns boxes with that box number, with a suffix and without a suffix.
If there is Box 1A and Box 1 I only want the 1A boxes displayed if a suffix is entered.
CREATE PROCEDURE spRMU_GettblFilesandBox
@strRMUConsignmentNo nvarchar(10),
@strViewRestricted bit,
@strtxtFileRef nvarchar (100),
@strtxtSubject nvarchar (100),
@strtxtDescription nvarchar (100),
@strBoxNo Integer,
@strBoxSuffix nvarchar (5)
AS
IF @strViewRestricted = 1
BEGIN
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')
ORDER BY Id
END
IF @strViewRestricted = 0
BEGIN
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo and
Restricted = 0
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')
ORDER BY Id
END
GO |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 04/19/2007 : 04:30:25
|
SELECT *
FROM tblFiles
WHERE
ConNo =@strRMUConsignmentNo and
Restricted <= @strViewRestricted -- no need for dual select
and FileRef like '%'+@strtxtFileRef+'%'
and Subject like '%'+@strtxtSubject+'%'
and FileDescription like '%'+@strtxtDescription+'%'
and (BoxNo = @strBoxNo or @strBoxNo = '')
and (BoxSuffix=@strBoxSuffix or @strBoxSuffix ='')
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
Wildcards and numbers