July 18, 2006 at 9:44 pm
Hi all,
We all know that the results of a stored procedure can be added to a table with the statement
INSERT INTO #Table EXEC SPName
I have a problem where I need to pass the SP some variables so I go
INSERT INTO #Table EXEC SPName @var1, @Var2 which works fine, I get the results I need into #Table BUT it gets tricky when I also need the variable values on the SAME ROW in #Table.
I don't have the luxury of being able to edit the stored proc so I need those values to come from the INSERT INTO.
Any ideas?
Thx in advance
Danster
July 18, 2006 at 11:23 pm
INSERT INTO #Table EXEC SPName @var1, @Var2
Select @var1 as Var1, @Var2 as Var2, *
INTO #Table2
FROM #Table
_____________
Code for TallyGenerator
July 19, 2006 at 1:09 am
Ahhh.
I see you're using the 2nd table trick.
I was hoping to avoid that.
Any other suggestions out there?
July 19, 2006 at 1:21 am
ALTER TABLE #Table
ADD Variable1 <data type>
ADD Variable2 <Data Type>
UPDATE #Table
SET Variable1 = @Var1, Variable2 = @Var2
Is that what you're looking for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2006 at 4:50 am
If you test both approaches for performance you'll find out that yours is slower.
Relational databased are really bad on altering tables.
_____________
Code for TallyGenerator
July 19, 2006 at 5:08 am
I suggeested it as al alternative to a second table, which he said wasn't really what he was looking for.
A second thought, no need to do the update, can specify the variables as defaults on the new columns (will need dynamic SQL though)
I did a time test to see how much slower the alter was
CREATE
TABLE #test (
id int,
name sysname
)
INSERT
INTO #test
SELECT id, name FROM sysobjects
-- 5546 rows
GO
SELECT
getdate() --2006-07-19 13:00:00.927
SELECT
id, name, 1 AS var1
INTO #test2
FROM #test
SELECT
getdate() --2006-07-19 13:00:00.947
ALTER
TABLE #test
ADD Var1 tinyint DEFAULT 1
SELECT getdate() --2006-07-19 13:00:00.947
DROP
TABLE #test
DROP TABLE #test2
Net result: select into takes 20 ms, alter table takes 0. I ran a couple times and times are consistent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply