Stored Procedure with 3 parameters and ADO

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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure with 3 parameters and ADO

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2005-03-31 : 09:43:51

What do you think about this my procedure ...???

Something is crazy about my third parameter ... I am trying to pull data from this procedure with ADO.Command ...

If I make this parameter like this: '%@search_word%' I do not get any result from my procedure.

If I make like this '%' + @search_word + '%' I have Server connection Time Out (even I set ADO connection time out to 90 ...default is 15) ...

Is there any different way how I can set string parameter in stored procedure ....???

CREATE PROCEDURE Search_Sale_by_ContainWord
@date_from datetime,
@date_to datetime,
@search_word nvarchar(30)
AS
SET NOCOUNT ON
SELECT arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Sum(arhPOSInvoiceLineitems.Quantity) AS SumOfQuantity, Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
FROM arhPOSInvoiceLineitems INNER JOIN Inventory ON arhPOSInvoiceLineitems.[Item Number] = Inventory.[Item Number]
WHERE (((arhPOSInvoiceLineitems.[Ship Date]) Is Not Null And (arhPOSInvoiceLineitems.[Ship Date]) Between @date_from And @date_to))
GROUP BY arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
HAVING (((arhPOSInvoiceLineitems.Description) Like '%' + @search_word + '%'))
ORDER BY Sum(arhPOSInvoiceLineitems.Quantity);

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-03-31 : 21:10:51

Why don't you put the condition from your HAVING clause inside your WHERE clause?

WHERE (((arhPOSInvoiceLineitems.[Ship Date]) Is Not Null And (arhPOSInvoiceLineitems.[Ship Date]) Between @date_from And @date_to)) AND (((arhPOSInvoiceLineitems.Description) Like '%' + @search_word + '%'))

This way, before performing the GROUP BY the number of records would have already been decreased making the statement perform better.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 22:05:11

also what indexes do you have on your table? Hopefully ShipDate is indexed. As for searching the description column, by definition that will be inefficient since the condition you are using needs to search the entire text of every row in your table within the date range and cannot use indexes.

- Jeff

ilimax
Posting Yak Master

164 Posts

Posted - 2005-04-01 : 11:09:15

I changed what rfrancisco said but I still have Ado connection Time Out even it's set to 90 ....

My indexes are fine .. I have indexes on Ship Date field and description ...

My table has about 3,000,000 records ...

Is there any more idea what could be wrong ...???

CREATE PROCEDURE Search_Sale_by_ContainWord
@date_from datetime,
@date_to datetime,
@search_word nvarchar(30)
AS
SET NOCOUNT ON
SELECT arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Sum(arhPOSInvoiceLineitems.Quantity) AS SumOfQuantity, Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
FROM arhPOSInvoiceLineitems INNER JOIN Inventory ON arhPOSInvoiceLineitems.[Item Number] = Inventory.[Item Number]
WHERE (((arhPOSInvoiceLineitems.[Ship Date]) Is Not Null And (arhPOSInvoiceLineitems.[Ship Date]) Between @date_from And @date_to)) And (((arhPOSInvoiceLineitems.Description) Like '%' + @search_word + '%'))
GROUP BY arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
ORDER BY Sum(arhPOSInvoiceLineitems.Quantity);

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-01 : 11:14:15

Again: As for searching the description column, by definition that will be inefficient since the condition you are using needs to search the entire text of every row in your table within the date range and cannot use indexes.

Once a typical, average date range is specified, about how many rows are returned which need to be searched on the Description column? (e.g., "on average the user will select a year and that will return approximately 500,000 rows")

- Jeff

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-01 : 13:27:13

Try this one.

CREATE PROCEDURE Search_Sale_by_ContainWord
@date_from datetime,
@date_to datetime,
@search_word nvarchar(30)
AS
SET NOCOUNT ON
SELECT arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Sum(arhPOSInvoiceLineitems.Quantity) AS SumOfQuantity, Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
FROM arhPOSInvoiceLineitems INNER JOIN Inventory ON arhPOSInvoiceLineitems.[Item Number] = Inventory.[Item Number] And arhPOSInvoiceLineitems.[Ship Date] Between @date_from And @date_to
WHERE arhPOSInvoiceLineitems.Description Like '%' + @search_word + '%'
GROUP BY arhPOSInvoiceLineitems.Description, arhPOSInvoiceLineitems.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
ORDER BY Sum(arhPOSInvoiceLineitems.Quantity);

The only difference is I put the checking of the [Ship Date] as part of the condition in the INNER JOIN.

Also, try it without the WHERE clause wherein it checks for the Description and see if it will timeout.

Do you also have an index on [Item Number] on both Inventory and arhPOSInvoiceLineitems tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources