Stored procedure using the LIKE keyword

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure using the LIKE keyword

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2013-01-16 : 12:08:46

I am trying to create a Stored Procedure that will select the user's input using the LIKE command. Here is the Stored Procedure:

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[CustOrderHist] Script Date: 01/16/2013 12:02:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
SELECT ProductName , C.CustomerID,Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE c.CustomerID LIKE @CustomerID + '%'
AND C.CustomerID = O.CustomerID
AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName, c.CustomerID

The Stored Procedure will only return results on an exact match.
Can someone please show me how to select the data by passing in a string and macthing using LIKE( or any other method)?

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 12:30:10

Change the parameter type to NVARCHAR(5) instead of NCHAR(5). Or if you are not able to change the function signature, cast it to NVARCHAR(5) in the stored procedure before using it in the like clause.

stevenandler
Starting Member

42 Posts

Posted - 2013-01-16 : 13:14:07

That Worked!

Thank you James.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-16 : 14:02:31

You are welcome Steven - glad to be of help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources