sql server (microsoft): Like problem -- sql server (microsoft)

Like problem

Roy Forkner
8/21/2003 10:12:10 PM

sql server (microsoft): I created a query using the SQL 2000 Query Builder. It has a LIKE statement.
This is the resulting query:

SELECT Inventory.InvID, Inventory.YearMod, Inventory.VIN,
Inventory.TagNum, Inventory.Make, InvMake.Make AS Make, InvModel.Model,
CustName.NameType, CustName.Fname, CustName.MI,
CustName.LName, CustName.SurName
FROM CustName RIGHT OUTER JOIN
Inventory ON CustName.CompanyID = Inventory.CompanyID
AND CustName.CustId = Inventory.CustId LEFT OUTER JOIN
InvModel INNER JOIN
InvMake ON InvModel.MakeId = InvMake.MakeId ON
Inventory.Model = InvModel.ModelId AND Inventory.Make = InvMake.MakeId
WHERE (Inventory.VIN LIKE N'%201471%') AND (Inventory.CompanyID = 1)

This query returned the expected results.

I copied the query and created a sproc (declared the variables, etc) but,
the sproc does n ot like the N following LIKE. Removing the N says syntax
correct and saves. However, it does not return any results. This is the code
ion the sproc: In query analyzer This is the statement:
Can someone tell me where the problem lies?
Your help will be much appreciated
Roy

nt_searchbyvin 1,'1', '%20147%'

CREATE PROCEDURE nt_SearchByVIN
@compid int,
@type char(1),
@vin varchar(25)

AS

print @vin
/* select @vin = @vin + '%' */
SELECT
CustName.CustId AS Custid, CustName.Fname AS Expr1, CustName.MI AS Expr2,
CustName.LName AS Expr3, CustName.LName AS Expr4,
CustName.DL, CustName.SS, Inventory.InvID,
Inventory.YearMod, InvMake.Make AS Make, InvModel.Model, Inventory.VIN,
Inventory.TagNum
FROM
CustName RIGHT OUTER JOIN
Inventory ON CustName.CompanyID = Inventory.CompanyID AND
CustName.CustId = Inventory.CustId
LEFT OUTER JOIN InvModel INNER JOIN InvMake
ON InvModel.MakeId = InvMake.MakeId
ON Inventory.Model = InvModel.ModelId AND Inventory.Make = InvMake.MakeId
WHERE
(CustName.CompanyID = @compid) AND
(Inventory.vin LIKE @vin)
GO

Re: Like problem

John Bell
8/23/2003 6:14:06 PM

Hi

The N infront of a string converts it to national character. You could
probably declare the procedure parameter as nvarchar to overcome the
problem.

You should also read the following and other articles on the site:
http://www.algonet.se/~sommar/dynamic_sql.html#Security2

John
[quoted text, click to view]