Daryl, if you post some table structure and sample data, someone can probably help you figure it out.

I'm no expert on this stuff by any means, but BOL says...

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters....

You need to know the dimensions of the bottles, not necessarily only the volume. Why don't you make one of each size box, and see which one holds the...

Probably should be one "datum" 🙂

Maybe you could just add it as another parameter in one of your stored procedures?

USE [OHT]

GO

/****** Object: StoredProcedure [dbo].[GetLookingFor] Script Date: 05/18/2009 11:09:30 ******/

SET ANSI_NULLS ON

GO

SET...

I often see code with semicolons here, but usually without. I have queried in DB2...

Check out Books On Line for "expanding heirarchies"

Chris, this seems like a much better solution than mine. After messing around with it a bit, I can understand what it is doing for the most part, but...

Ah ha. Not too familiar with what scope is, or how it affects things. Thanks for the tip.

Christopher Stobbs (5/14/2009)

---

Have you tried using the system tables in SQL?

Here is a dynamic unpivot solution with not temp tables or table variables.

I must admit that I found this code...

mdowns (5/14/2009)

---

I think you will find you can't INSERT .... EXEC into a table variable in SQL Server 2000. It has to be SQL Server 2005 or higher.

Why not just...

Christopher Stobbs (5/14/2009)

---

are you sure that happycat59's solution above does not work it works for me?

Or you could try this:

DECLARE @sql VARCHAR(200)

SELECT @sql = 'DECLARE @table TABLE

(

col1 CHAR(1)

)

INSERT INTO @table...

Thanks both, but its back to the drawing board. When I tried your solutions, I got...

Server: Msg 197, Level 15, State 1, Line 7

EXECUTE cannot be used as a...

Well, I guess I can now also take the liberty of assuming maybe this is not what the OP was looking for. (crickets chirping)

See if this article does not give you what you need.

http://www.sqlservercentral.com/articles/Test+Data/61572/