December 18, 2012 at 2:09 pm
I need some help please. I have 1 table of Lots and 1 table of Owners (see atchs 1 & 2 for table designs). I am trying to write a proc that I call with 1 parameter Lot nvarchar(5) Example: '309' and have the result put into a tempory table which will then contain the LotID and the OwnerID. The LotID is used to select the data for a particular Lot and the OwnerID is used to select the data for the Owner of THAT PARTICULAR LotID. If I can get this to work I will use an INNER JOIN to get the results for the Owner. This proc will have a dual output pane, one below the other. My code for the proc is:
-- spGetLotInformation
-- Call this proc with a lot number 233 through 352
use roe6
go
create proc spGetLotInformation
@LotNumnvarchar(5)-- Examples: '2461', '270', '309', '3101'
as
select LotID, OwnerID
from Lots
where LotID = @LotNum;
-- into #LotID_OwnerID
My code to call the proc
use roe6
go
exec spGetLotInformation '309' -- This calls the proc with parameter 309
-- LotID is nvarchar(5)
select LotID, OwnerID
from Lots
into #LotID_OwnerID -- Create a Temporary Table with both
-- LotID and OwnerID
/* If I can get the above to place its output into a tempory table then I
** can write a select statement with an inner join to output the contents
** of the corresponding Owners Table.
/*
-- Now get the Owner Information
select *
from Owners
where OwnerID = #LotID_OwnerID
*/
Thanks for any help. JRichards54
December 18, 2012 at 2:50 pm
What are you intending to do with the resultant #temptable? The syntax issue you're running into is:
SELECT whatever INTO #table FROM tablexyz.
Alternatively CREATE TABLE #table INSERT INTO #table SELECT whatever FROM tablexyz.
However, it looks like you're hoping to use the #table outside of the build proc, which gets intricate to do properly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2012 at 3:05 pm
Thank you Sir. I will ponder your words of wisdom and maybe I will be able to do it. But then I have a work around that appears to do the job. My code for that one is:
/* This Query will find both the Lot and Owner information for that lot. Just replace
** the Lot Number (nvarchar(5)) (in red) with the Lot # you want information on and
** execute. Lots are numbered from 233 through 352. The following lots have been
** re-platted:246 to 2461 and contains 247
**258 to 2581 and contains 259
**310 to 3101 and contains 311 & 312
*/
use roe6
declare @LOTID nvarchar(5)
set @LOTID = '310'
/* This section checks for lots that have been re-platted and have a new Lot Number.
** Since the original lot number will no longer work because that Lot Number does not
** exist anymore. This section changes the @LOTID variable to the new re-platted number.
** There are no lots numbered 247, 259, 311, or 312.
*/
if @LOTID = '246'
begin
set @LOTID = '2461'
end
if @LOTID = '258'
begin
set @LOTID = '2581'
end
if @LOTID = '310'
begin
set @LOTID = '3101'
end
-- Get the Info for Lot # 309 and put it in a tempory table @LOTID. It contains the
-- OwnerID that we need for the next select statement.
select *
into #OWNERID
from Lots
where LotID = @LOTID
select *
from Lots
where LotID = @LOTID
select *
from Owners o
join #OWNERID t
on o.OwnerID = t.OwnerID
DROP TABLE #OWNERID
Is this a better approach than the temporary table? I know that it does the job and I only have to enter the lot number once and I get two output panes. One for Lots and the other just below it for Owners. JRichards54 🙂
December 18, 2012 at 3:14 pm
Oh, such fun, lot rezonings. Used to work in land appraising. Brings back memories. ... errr... I'm digressing.
My apologies for the initial confusion. I thought you were trying to build a stored procedure for return to a front end, and were nesting them. I misread some of your earlier code apparently after I saw the problem with the syntax.
Do your users actually use SSMS as a direct interface? That's unusual. Anyway, what you're doing is fine, but I personally wouldn't bother with the overhead of a #table.
I'd do something like this:
SELECT
*
FROM
Lots
WHERE
LotID = @LotID
SELECT
*
FROM
Owners AS o
JOIN
Lots AS l
ONo.OwnerID = l.OwnerID
WHERE
l.LotID = @LotID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2012 at 4:36 pm
Thank you Evil Craig. I like your reduced code version. Haave a good day and a Happy Holiday Season.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply