Table variable difficulty in a stored procedure

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 2008 Forums

Transact-SQL (2008)

Table variable difficulty in a stored procedure

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2012-04-22 : 12:45:02

There is one part of a stored procedure in sql server 2008 that uses a table variable the syntax of which I can’t figure out.

I have a table var:

declare @rttemp table(
[RouteID] [int] NOT NULL,
RouteCode char(3) null)

Which has a set of records with routeID key and the routecode

I need to link this to another table, tblOrder, which has, for this purpose,
An orderID key, a RouteID, SequenceInRoute, and an invoiceDate.

ORDERID int primary
RouteID int
SequenceInRoute int
InvoiceDate smalldatetime

I want to only select the orderID’s from the tblorder where the routeID in the tblORder is one of the routeID’s in the @rrtemp table. I want only the orders within a date range, and I need the result to be sorted by routecode, SequenceInRoute.

However, routecode is a char(3) so I had to find a sort to handle alphanumeric routecodes. The sort will be RouteCode, SequenceInRoute.

Here is the final part of the sp that takes these two tables and links them to produce these results. Two parameters, @from, @to which are smalldatetimes are passed to the stored procedure for the date range.

The select query result will be a rownumber (because of the sort) and an orderID. Below is the last part of the sp and the one that I am having problems with. Thanks everyone.

Select Row_Number() Over (Order by
Case When IsNumeric(@rttemp.RouteCode) = 1 then Right(Replicate('0',21) + @rttemp.RouteCode, 20)
When IsNumeric(@rttemp.RouteCode) = 0 then Left(@rttemp.RouteCode + Replicate('',21), 20)
Else @rttemp.RouteCode
END, tblOrder.SequenceInRoute ) As RowNumber,
dbo.tblOrder.OrderID fROM dbo.tblOrder where routeID in (select RouteID from @rttemp) and
(tblOrder.InvoiceDate BETWEEN CONVERT(DATETIME, @from, 102) AND CONVERT(DATETIME, @to, 102))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-22 : 12:54:19

just join your @rttemp instead of using it with IN()

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-22 : 12:59:41

[code]Select
Row_Number() Over (Order by
Case
When IsNumeric(r.RouteCode) = 1 then Right(Replicate('0',21) + r.RouteCode, 20)
When IsNumeric(r.RouteCode) = 0 then Left(r.RouteCode + Replicate('',21), 20)
Else r.RouteCode
END,
o.SequenceInRoute ) As RowNumber,
o.OrderID

FROM dbo.tblOrder o
join @rttemp r on r.routeID = o.routeID

where o.InvoiceDate BETWEEN CONVERT(DATETIME, @from, 102) AND CONVERT(DATETIME, @to, 102))
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources