Posted by Erland Sommarskog on 11/05/05 16:36

Eugene Anthony (solomon_13000@yahoo.com) writes:
> create procedure sp_PagedTaxPerPlace

The sp_ prefix is reserved for system stored procedures and SQL Server
first looks these up in the master database. Don't use it for your own
code.

> @Page int,
> @RecsPerPage int
> AS SET NOCOUNT ON
>
> CREATE TABLE #TempItems
> (
> TaxPerPlaceID int IDENTITY,
> CountryID int,
> StateID int,
> Zip int,
> TaxPerPlace smallmoney,
> ParentTaxPerPlaceID int
> )
>
> INSERT INTO #TempItems
> (CountryID,StateID,Zip,TaxPerPlace,ParentTaxPerPlaceID) SELECT
> CountryID,StateID,Zip,TaxPerPlace,TaxPerPlaceID FROM TaxPerPlace

You should probably have an ORDER BY clause here, or else it gets kind
of meaningless.


> now before I insert into #TempItems as shown in my stored procedure, I
> would like to replace for example:
>
> CountryID = 1 to CountryID = Japan
> StateID = 1 to StateID = Osaka
>
> I have two tables. One table handles the country and the other table
> handles the state and can be accessed using the following sql statement:
>
> SELECT Name FROM Countries
> SELECT Name FROM States

Time to learn about joins!

SELECT c.Name, s.Name, t.TaxPerPlace, t,ParentTaxPerPlaceID
FROM #TempItems t
JOIN Countries c ON c.CountryID = t.CountryID
JOIN States s ON s.StateID = t.StateId
ORDER BY t.TaxPerPlaceId


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]