SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Wildcards and numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  07:11:35  Show Profile  Reply with Quote
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  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
BoxNo is integer field? or varchar?


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

pbguy
Constraint Violating Yak Guru

India
319 Posts

Posted - 04/18/2007 :  07:15:06  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/18/2007 :  07:15:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
and cast(BoxNo as varchar) like '%' + @strBoxNo + '%'


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  08:40:33  Show Profile  Reply with Quote
BoxNo is Integer. I will try the other solutions. Thank you
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  08:54:34  Show Profile  Reply with Quote
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
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  09:11:08  Show Profile  Reply with Quote
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.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/18/2007 :  09:21:57  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
and (BoxNo = @BoxNo or @BoxNo = '')


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  10:39:44  Show Profile  Reply with Quote
That does not work because every record will have a BoxNo so if one is not entered to filter on no records satisfy Boxno = ''
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/18/2007 :  10:42:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/18/2007 :  10:44:37  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/18/2007 :  11:10:50  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/18/2007 :  14:32:40  Show Profile  Reply with Quote
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
Go to Top of Page

Pinto
Aged Yak Warrior

United Kingdom
590 Posts

Posted - 04/19/2007 :  04:26:33  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/19/2007 :  04:30:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000