I've got an SQL query that asks for input and returns info (big surprise
there!). Trouble is, the query gets messed up when you give it
completely-numeric input. It's fine with alpha-numeric. The line causing the
trouble is below-
.....
AND Products.Description like '%' +<<Description>> + '%'
.....

I've been trying various ways of using CONVERT or CAST to make sure the
input ends up being character, not numeric, but can't quite get a handle on
it. Is this really something simple or is there a good reason (besides me
being inept at this sort of thing) for why I can't figure this one out? The
entire script is shown below.

Thanks-

--Mike Jacoubowsky
Chain Reaction Bicycles
www.ChainReaction.com
Redwood City & Los Altos, CA USA


SELECT TOP 1 Locations.ID,
'**SKU' AS SKU,
'Description'AS Description,
RIGHT('QOH',6) AS QOH,
'Total Units'
FROM Locations
UNION
SELECT Products.ID,
Products.[Store UPC] AS SKU,
Products.Description,
CONVERT(VARCHAR, Products.Quantity),
CONVERT(VARCHAR, SUM(SalesItems.QUantity))
FROM Sales, SalesItems, Products
WHERE Sales.Hide = 0
AND SUBSTRING(CONVERT(VARCHAR, Sales.FinishDate, 112), 1,6) <=
SUBSTRING(CONVERT(VARCHAR,
GETDATE(), 112), 1,6)
AND SUBSTRING(CONVERT(VARCHAR, Sales.FinishDate, 112), 1,6) >
SUBSTRING(CONVERT(VARCHAR,
DATEADD(YEAR, -1, GETDATE()),112), 1,6)
AND Sales.ID = SalesItems.SalesID
AND SalesItems.Hide = 0
AND SalesItems.ProductID = Products.ID
AND Products.Brand like '%' +<<Brand>>+ '%'
AND Products.Description like '%' +<<Description>> + '%'
GROUP BY Products.ID,
Products.[Store UPC],
Products.Description,
Products.Quantity
ORDER BY SKU,
Description

On 5 mar, 04:45, "Mike Jacoubowsky" <Mi... (AT) ChainReaction (DOT) com> wrote:
[...]
Have you tried using a prepared statement? As a side note, the sql
concat operator is ||, not +, see PIPES_AS_CONCAT


/Lennart

[...]

"Lennart" <erik.lennart.jonsson (AT) gmail (DOT) com> wrote

Near as I can tell, I'm dealing with is an sql "window" into an application
built with VB6. That could account for the non-standard conventions seen in
the script I listed. I tried using the standard for piping but that throws
out an error message.

From what I've read of SQL, there is no standard convention for user input
within brackets, as seen in the line I referenced.

Thanks-

--Mike Jacoubowsky
Chain Reaction Bicycles
www.ChainReaction.com
Redwood City & Los Altos, CA USA

On 5 mar, 06:23, "Mike Jacoubowsky" <Mi... (AT) ChainReaction (DOT) com> wrote:
There is no such thing as user input in sql. That is handled by the
application and a query is constructed and sent to the dbms. But I
think your problem lies in the applications layer, so perhaps you
should address this issue in a VB6 forum.


/Lennart

[...]