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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-07 : 00:59:53
|
Jon writes "I have moved up to SQL from Access and have rejoiced in finding out about stored procedures. I modified the code I got from an article at http://www.4guysfromrolla.com/webtech/062899-1.shtml called "Paging through Records using a Stored Procedure".
Everything seemed to be working until in one of my many stored procedure queries that I try to select records that match a "nvarchar" field instead of the "int" fields I normally use.
I tried using the differnt methods like "like", "in", even created a full text index and tried "contains", but these methods return other records than the specific ones I needed.
In the stored procedure I use this to get the variable:
CREATE PROCEDURE listbytypebrand
(
@Page int,
@RecsPerPage int,
@cat int,
@prtyp nvarchar
)
------
And this this for the querry:
SELECT
TblProducts.PrBraNam,
TblProducts.PrTyp,
TblProducts.PrID,
TblProducts.PrPicLink,
TblProductDetails.PdID,
TblProductDetails.PdNam,
TblProductDetails.PdSalPri,
TblProductDetails.PdSku,
TblProductDetails.PdCurInv,
TblProductDetails.PdStock
FROM TblProducts INNER JOIN
TblProductDetails ON
TblProducts.PrID = TblProductDetails.PdBraID
WHERE (TblProducts.PcID = @cat) AND
(TblProductDetails.PdDis = 0) and TblProducts.PrTyp = @prtyp order by TblProducts.PrBraNam, TblProductDetails.PdNam
--------
in my ASP page I use:
mySQL= "listbytypebrand " & currentPage & "," & iRecordsPerPage & "," & cat & ",'" & prtyp & "'"
---------
All my variables like "cat" are numeric and primary keys. The "prtyp" one is text.
If I edit the stored procedure to use regular text like:
TblProducts.PrTyp = 'This Text'
All the matches to "This Text" come up.
I also try creating a new view with the same querry and it returns my data.
Only when I use a string variable passing to a stored procedure do the records not match and all my searching on the SQL and ASP sites only come up with wider text searching not =
Thanks in advance,
Jon" |
|
|
|
|
|
|
|
String Variable Searches in a Stored Procedure