are order by''s allowed in varible tables
-
Hi,
I have a stored proc that declares a varible table then inserts data into a varible table. It works fine untill I put an order by in the select statement that inserts values - I get a syntax error. Does anyone know why this is and is there any other way to insert the rows into the varible table ordered by the startdate (ass)?
DECLARE @Results TABLE
(
RowNum int IDENTITY (1,1),
ResCode varchar(9) NULL,
Grade varchar(50) NULL,
Rate decimal(18, 3) NULL,
PeriodStart datetime NULL,
PeriodEnd datetime NULL
)
INSERT @Results (Grade, Rate, PeriodStart, PeriodEnd)
(
SELECT Grade, Rate, PeriodStart, PeriodEnd
FROM grade
WHERE grade = @Grade
AND periodstart < @End
AND (periodend > @Start or periodend is null)
ORDER BY periodstart --fails if this is in but works if it is not, syntax error near order
)
the full stored proc;
CREATE PROCEDURE updateratehistory2
--Updates the ratehistory table when a resources grades history has been changed
--This script is currently designed to run only where there is a end date
--Declare inputs
@ResCode VARCHAR(255),
@Grade VARCHAR(255),
@GradeCur VARCHAR(255),
@Start DATETIME,
@StartCur DATETIME,
@End DATETIME,
@EndCur DATETIME
AS
--Varible to insert how many rates exist for the grade within the start and end times
DECLARE @NumRate int,
@Counter int
SET @Counter = 2;
--input all the different grade values over the timeframe into temp table
DECLARE @Results TABLE
(
RowNum int IDENTITY (1,1),
ResCode varchar(9) NULL,
Grade varchar(50) NULL,
Rate decimal(18, 3) NULL,
PeriodStart datetime NULL,
PeriodEnd datetime NULL
)
INSERT @Results (Grade, Rate, PeriodStart, PeriodEnd)
(
SELECT Grade, Rate, PeriodStart, PeriodEnd
FROM grade
WHERE grade = @Grade
AND periodstart < @End
AND (periodend > @Start or periodend is null)
ORDER BY periodstart
)
Update @Results SET Rescode = @Rescode
--put the number of different rates that exist for grade over time
SELECT @NumRate = max(RowNum) FROM @Results
Select * from @Results
--one rate exists
IF (@NumRate = 1)
BEGIN
--as simple just update to new values
UPDATE ratehistory
SET Grade = @Grade,
Periodstart = @Start,
Periodend = @End,
Rate = (select rate from @results where rownum = 1)
WHERE grade = @GradeCur
AND rescode = @ResCode
AND periodstart = @StartCur
AND periodend = @EndCur
END
--more than one rate exists
ELSE IF (@NumRate > 1)
BEGIN
--update first record to new values
UPDATE ratehistory
SET Grade = @Grade,
Periodstart = @Start,
Periodend = (select periodend from @results where rownum = 1),
Rate = (select rate from @results where rownum = 1)
WHERE grade = @GradeCur
AND rescode = @ResCode
AND periodstart = @StartCur
AND periodend = @EndCur
--loop to insert all remaining ones
WHILE (@Counter < @NumRate)
BEGIN
SET @Counter = @Counter + 1
INSERT INTO ratehistory (ResCode, Grade, Rate, PeriodStart, Periodend)
(Select ResCode, Grade, Rate, PeriodStart, Periodend from @results where rownum = @Counter)
END
--Inset the last record, if this isnt also the first
INSERT INTO ratehistory (ResCode, Grade, Rate, PeriodStart, Periodend)
(Select ResCode, Grade, Rate, PeriodStart, Periodend from @results where rownum = @NumRate)
END
--no rates exits
ELSE IF (@NumRate < 1)
BEGIN
select 'Exception', @NumRate
END
--should never go into
ELSE
BEGIN
select 'Exception', @NumRate
END
SELECT * from ratehistory where rescode = @ResCode
GO
thanks
-
First of all, there is no reason to insert the rows in any specific order. The rows in a table (or a set) does not have any specific order. They might be physically stored in a specific order, but that does not guarantee that they will be returned in that order when you select them. You should use ORDER BY when selecting rows from the table variable if you need them in a specific order.
You can have an ORDER BY statement in the INSERT .. SELECT however, if you remove the parentheses (which I do not know why they are there to start with). But as I said there is no reason to have it there.
-
Example
Use Northwind
go
DECLARE @Results TABLE
(
RowNum int IDENTITY (1,1),
CustomerID varchar(20),
PostalCode varchar(30)
)
INSERT INTO @Results (CustomerID, PostalCode)
SELECT CustomerID, PostalCode
FROM customers ORDER BY PostalCode
SELECT * FROM @Results
try the following, hope this helps
INSERT INTO @Results (Grade, Rate, PeriodStart, PeriodEnd)
--(
SELECT Grade, Rate, PeriodStart, PeriodEnd
FROM grade
WHERE grade = @Grade
AND periodstart < @End
AND (periodend > @Start or periodend is null)
ORDER BY periodstart
--)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply