Is there an end of file (eof) property in SQL?

[tokuzumi]

I have an operation I want to perform in a stored procedure. I am building a temp table that gradually gets larger. Now, what I want to do is this;

I have found some literature on the "while" statement, which sets a condition for a chunk of SQL to be repeated until a condition is met.

so, here would be the query, in English;

While (select....from...where) still has records
begin
some code I want to run
end

I hope my ranblings make sense. Thanks for any help.

 

[SQLSister]

In SQL server you can use the while statement, but it is definitely not recommended. Processing one record at a time will slow your system considerably. YOu should try to use a set based statemetn instead.

Tell uis more about what you need to do in the processing steps and we can help you decide how to do this is in a set-based, more efficient manner.

If you must use the loop, it will keep processing until there are no more records, you don't need to specify until EOF.

 

[tokuzumi]

In the database, there is a table that has a text-based field, with 13 values, seperated by a comma, and then a space. The field will look like this, minus the quotes:

"1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13"

The numbers won't always be 1-13, they can be anything, but you get the jist of what the column looks like.

The 13 values represent each week in a quarter, for a particular year.

So, in the table, we have a structure like this:
TableName
(
Id Int Autonumber
LocationId Int,
Location2Id Int,
TheYear Int,
Quarter Int,
Percentage text (has the 13 CSVs above)
)

I'm trying to build a temporary table, that displays each of the 13 values in it's own row, by each week ending date (which is a Saturday) in that quarter in a particular year.

So, the table I'm creating looks something like this:
#Table
(
Location1
Location2
Year
Quarter
Percentages (CSVs from above)
)
I have a UDF that takes a CSV string, and puts it into a temp table (

http://www.sqlteam.com/item.asp?ItemID=11499)

which works out great. I can build the temp table up to the point of the Percentages. I just build a series of temp tables, adding one column each time, and put new data in the temp table. But when I do the Percentages, using the UDF, the subquery returns multiple values, and the stored procedure dies. I don't want to use a cursor, since I know it is slow. But I'm not savvy enough in SQL yet to know the best way to tackle my problem. I hope I have explained what I need to do in a way that everyone can understand. Thanks for the reply.

 

[SQLSister]

First thinng I have to say is that your structure is completely wrong. One of the first rules of database design is NEVER store more than one piece of information in a field. YOu will never be able to process this type of information efficiently in the current structure. If your table will be large, this will slow the process down until it takes minutes instead of milleseconds. It is urgent that you fix the structure before you try to get data out of it.

So if you are going to freqently need to see this information broken up into separate fields, then you need to do a conversion to separate fields. If there is even a remote possibility that these fields will not stay stable at thirteen, I would put them in a related table.

That said, you are stuck with what you are stuck with and the methodology is simliar whether you separate them out for a select stement or for a move to another structure.

You best bet is a udf that returns a table variable.

Then you use this UDF to populate a temp table in the main stored procedure. Then you join this table with the data you need from other sources and perform calulations on it as needed. You shouldn't need a subquery to do simple percentage calculations. Assume the data is in a temp table.

Select col1/(col1+col2+col3) as percentageCol1 from #temp should work just fine. (I assumed three columns just for example purposes, I know you have more to create a total to calc a percentage against.

If this still isn't clear, may be you could post the SQL with the subquery and we will have a better idea of what you are trying to do.

 

[dky1e]

As SQLSister suggested, this is not the best way to store data and I would suggest you change the schema and add avoid using the text field and add an extra table instead, but I know it's easier said than done.

Write a UDF (user defined function) that will return the N(th) item in your csv string. The function should return NULL if index is out of range.


Then do the following (I'm assuming '0' is the first position in the array):

declare @i int
set @i = -1  --set to -1 so that it will be 0 at start

--this loop will continue until
--no more records will be added
while @@rowcount > 0  
begin
    set @i = @i + 1

    insert into #Table(Location1,Location2,Year,
                       Quarter,Percentages)
    select Location1,Location2,Year,
                       Quarter, fnGetValueByIndex(   
                                Percentages, @i )
    from TableName
    where fnGetValueByIndex(Percentages, @i) is not null
end

I really couldnt think of a way to do it with out a loop... but this will iterate only the maximum number of elements in your csv string with, not having to cursor through each record.

 

[tokuzumi]

Okay, I have found this UDF, that takes a CSV, and breaks out the values into a temp table, so "2,4,6" would look like this:

2
4
6

Now, what I want to do, is add a value field, that will increment by one, so the above row would look like this:

Value Field
1 2
2 4
3 6

Then, if I wanted the 2nd value in the string, I could select the field from the temp table where the value = 2. But I get an error, saying that "Select statements included within a function cannot return data to the client." What do I need to do to modify this UDF to do what I'm looking for? The UDF builds the table properly, so that's not really an issue. One other bug, if you put in a number greater than 1 for the @Id, it repeats the first number in the CSV the same number of times as the value for @Id. Anyways, here is the UDF. Thanks for the help.

Create Function dbo.NthValOfCSV ( @Id int, @Array varchar(1000))
returns @IntTable table
(
Value int,
IntValue int
)
AS
begin

declare @separator char(1), @i int
set @separator = ','
set @i = 0

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

set @i = @i + 1
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (@i, Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
--select IntValue from @IntTable where Value = @Id
end

return
end

 

[dky1e]

You can't 'select' out of a function, you'll have to set a variable to the value and then return it. You will be returning one value, not a table, so the function 'returns int'.

I haven't tested it, but you'll get the idea.

Create Function dbo.NthValOfCSV ( @Id int, @Array varchar(1000))
returns int
AS
begin

declare @iRetVal int

declare @separator char(1), @i int
set @separator = ','
set @i = 0

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

set @i = @i + 1

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

if @i = @Id --if we're at the right location
begin --get the value and get out of loop
set @iRetVal = Cast(@array_value as int))
break
end

select @array = stuff(@array, 1, @separator_position, '')

end

return @iRetVal
end