CREATE PROCEDURE KeywordSearchResults_Gen_Vend

/*
	Name: KeywordSearchResults_Gen_Vend
	Description: This sproc returns a list of parts based on generic description chosen and current page.  It sorts by Vendor, Part Number ASCENDING.
	Author: Michael Sumerano
	Date: 01/25/2002
*/

@BlanketMargin decimal(6,3),
@Warehouse int,
@AltWarehouse int,
@CustNo int,
@GenKey int,
@page int,
@size int,
@pagecount int OUTPUT

AS

declare @start int, @end int

	--Calculate the starting Row
	SET @Start = (((@Page - 1) * @Size) + 1)

	--Calculate the ending Row
	SET @End = (@Start + @Size - 1)

	--Create temporary table to store all results from search
	CREATE TABLE #FullResults
	(
		Row int IDENTITY(1,1) PRIMARY KEY,
		InvKey int,
		VendNum char(3),
		VendorName char(12),
		PartNum char(25),
		[Description] char(25),
		RetailPrice money,
		JobberPrice money,
		CorePrice money,
		FederalExciseTax money,
		PriceCode bit,
		OnHand int,
		AltInventory int,
		SpecialOrder bit,
		Reference bit
	)

	--Insert all results from search
	INSERT INTO #FullResults
	SELECT DISTINCT
		Inv.InvKey,
		inv.VendNum,
		Ven.VendorName,
		Inv.PartNum,
		Inv.[Description],
		jobberprice / (1 - ISNULL((markup), @BlanketMargin) / 100) AS RetailPrice,
		Inv.jobberprice,
		Inv.corePrice,
		Inv.federalExciseTax,
		Inv.PriceCode,
		boh.onHand, 
		alt.onHand as AltInventory,
		Inv.SpecialOrder,
		NULLIF(ISNUMERIC(ref.refkey), 0) as Reference
	FROM
		dbo.Inventory inv
	INNER JOIN
		dbo.Vendor ven ON Inv.VenKey = Ven.VenKey 
	LEFT OUTER JOIN
		dbo.reference ref ON inv.invkey = ref.invkey
	LEFT OUTER JOIN
		dbo.OnHand boh ON Inv.InvKey = boh.InvKey and boh.warehouse = 1
	LEFT OUTER JOIN 
		dbo.OnHand alt ON Inv.InvKey = alt.InvKey and alt.warehouse = 1
	LEFT OUTER JOIN 
		dbo.Markup mu ON mu.vendornum = ven.vendornum and mu.CustomerNumber = 9400 
	WHERE 
		inv.invkey in (select DISTINCT invkey from dbo.accessory where gennum = @GenKey)
	ORDER BY
		ven.vendorname, inv.partnum

	--Calculate the page count
	set @pagecount = CEILING(@@ROWCOUNT / cast(@size as numeric(5,3)))

	--Select the search results based on the current page
	SELECT
		InvKey, VendNum, VendorName, PartNum, [Description], RetailPrice, JobberPrice, CorePrice, FederalExciseTax, PriceCode, OnHand, AltInventory, SpecialOrder, Reference
	FROM 
		#FullResults
	WHERE 
		(Row >= @Start) AND (Row <= @End)

GO

quote:

---

Put a
set nocount on
at the top of the sp.
Or look at the next recordset in VB.

It is probably returning a recordset for the rowcount from the temp table. An oledb feature.

---

quote:

---

Put a
set nocount on
at the top of the sp.
Or look at the next recordset in VB.

It is probably returning a recordset for the rowcount from the temp table. An oledb feature.
y - nr on 01/29/2002 12:42:36

---

quote:

---

This will be because it isn't getting any rows from the query.
Try hard coding the statr and end rows in the query.
Try logging the parameters to a table at the top of the SP - you'll probably find they're not what you expect.

---

quote:

---

You will need to get all the rows back to the client before the output parameter is available (and possibly close the recordset).

I always work with client side disconnected recordsets so the parameters are available.

---