Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2007
    Posts
    14

    Unanswered: Wildcard not working

    I'm going through CBTNuggets prepping for 70-431 and he made an excellent little stored procedure that I tried on my SQL 05 Dev Edition and it won't work for me for some reason. Here's the SP (for the AdventureWorks DB):

    Code:
    Create PROCEDURE HumanResources.spGetEmployeesByName
    	@FirstName nvarchar(50) = '%',
    	@LastName nvarchar(50) = '%'
    AS
    	SELECT
    		pc.Title,
    		pc.FirstName,
    		pc.LastName,
    		pc.EmailAddress,
    		pc.Phone,
    		hre.BirthDate,
    		hre.HireDate
    	FROM
    		HumanResources.Employee hre
    			JOIN
    		Person.Contact pc
    			ON
    		hre.ContactID = pc.ContactID
    	WHERE
    		pc.FirstName LIKE @FirstName + '%'
    			AND
    		pc.LastName LIKE @LastName + '%'
    This should take partial names and return users (like it does when he ran it on the CBT) like so:

    EXEC HumanResources.spGetEmployeesByName 'm'
    or
    EXEC HumanResources.spGetEmployeesByName 'm', 's'
    or
    EXEC HumanResources.spGetEmployeesByName @LastName='smith'

    But for me, I get no results. It's like the % wildcard character is not being recognized...

    What do I have wrong here?

  2. #2
    Join Date
    Aug 2007
    Posts
    17
    Could you please confirm whether it indeed exists the data that satisfy your clause first of all?

  3. #3
    Join Date
    Jul 2007
    Posts
    14
    Yeah the data is definitely there.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I dont know how procedure Work

    why not try this
    replace @FirstName nvarchar(50) = '%', with @FirstName nvarchar(50) ,

    take out the '%'

    and change

    pc.FirstName LIKE '%' + @FirstName + '%'


    just trying to gve you ideas in where to start looking
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm running this on SQL 2005 dev edition and works fine. Could you run this please?
    Code:
    SELECT    collation_name
    FROM    sys.databases
    WHERE    name    = 'AdventureWorks'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Seems like some ppl are not present in the JOINED result set.
    Code:
    -- a Gustavo	 Achong is found
    select * from Person.Contact
    where FirstName LIKE '%Gus%'
    			AND
    		LastName LIKE '%Ach%'
    GO
    DROP PROCEDURE HumanResources.spGetEmployeesByName
    GO
    Create PROCEDURE HumanResources.spGetEmployeesByName
    	@FirstName nvarchar(50) = '',
    	@LastName nvarchar(50) = ''
    AS
    	SET @FirstName = '%' + @FirstName + '%'
    	SET @LastName = '%' + @LastName + '%'
    	
    	SELECT top 10
    		pc.Title,
    		pc.FirstName,
    		pc.LastName,
    		pc.EmailAddress,
    		pc.Phone,
    		hre.BirthDate,
    		hre.HireDate
    	FROM
    		HumanResources.Employee hre
    			JOIN
    		Person.Contact pc
    			ON
    		hre.ContactID = pc.ContactID
    	WHERE
    		pc.FirstName LIKE @FirstName
    			AND
    		pc.LastName LIKE @LastName
    GO
    
    -- Gustavo	 Achong is not found now
    EXECUTE HumanResources.spGetEmployeesByName 'Gus', 'Ach'
    GO
    -- but we do find a Guy Gilbert
    EXECUTE HumanResources.spGetEmployeesByName 'Gu', 'Gil'
    GO
    I guess due to the JOIN condition
    Code:
    	FROM HumanResources.Employee hre 
    			JOIN Person.Contact pc ON
    				hre.ContactID = pc.ContactID
    Gustavo Achong is filtered out. Try your search with 'Gu', 'Gil' that one should work.
    I have altered the SP somewhat:
    SET @FirstName = '%' + @FirstName + '%'
    SET @LastName = '%' + @LastName + '%'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jul 2007
    Posts
    14
    Pootle, this is the collation: Latin1_General_CS_AS

    Is that the problem? I thought I chose the default but maybe not...

  8. #8
    Join Date
    Jul 2007
    Posts
    14
    Here's another piece of sample code that's not working for some reason. It should declare an XML doc, insert a new value (seats) and return the new doc. I just get the original XML doc, no "seats"...

    Code:
    DECLARE @xmlData xml
    
    SET @xmlData = '<ProductInfo ProductID="1">
    			  <ProductName>Two-Person Bike</ProductName>
    			  <Color>Green</Color>
    			  <Components>
    			    <Wheels>3</Wheels>
    			    <Handlebars>2</Handlebars>
    			    <Chains>2</Chains>
    			  </Components>
    			  <Description>Big green two-person bike</Description>
    			</ProductInfo>'
    
    SET @xmlData.modify('insert <Seats>2</Seats> into (/PrductInfo/Components)[1]')
    
    SELECT @xmlData as InsertElement

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This collation means your AdventureWorks database is Case Sensitive (see the CS bit?).

    Try submitting your calls as below:
    EXEC HumanResources.spGetEmployeesByName 'M'

    EXEC HumanResources.spGetEmployeesByName 'M', 'S'

    EXEC HumanResources.spGetEmployeesByName @LastName='Smith'

    My installation is a different collation - could you run this:
    Code:
    SELECT    collation_name
    FROM    sys.databases
    WHERE    name    = 'Model'
    Read up on collations in Books Online. You might want to change it really - case sensitive is not really appropriate for this db.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2007
    Posts
    14
    Yep - that was it. I'm guessing you change that in the Surface Area tool? Or do I have to re-install?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No need - I can't remember as I haven't done this for ages.
    SQL SERVER CHANGE COLLATION - Google Search
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2007
    Posts
    14
    Thanks Pootle. I passed the first test today - woot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •