November 7, 2006 at 4:30 pm
OK, I think I've gone braindead here.
I'm writing a new stored proc. In this stored proc, I want to INSERT some records into a table.
I'm getting the records from another table.
I was planning to use syntax like:
INSERT INTO table1(COLA, COLB, COLC)
SELECT a, b, c From Table2
Then I realized that the SELECT I needed was quite complicated and was satisifed by a stored proc I had already written which returned a rowset. It has input parameters and returns more columns than I need. I don't want to write another version of it.
What's the syntax I could use, or is it even possible? (If not what could I do?)
For instance:
INSERT INTO table1(COLA, COLB, COLC)
SELECT a, b, c From (EXEC StoredProc1 'parm1')
(of course, the above gives me a syntax error).
Ideas? Phil
November 7, 2006 at 4:42 pm
November 7, 2006 at 4:44 pm
You can use an INSERT INTO #tmpTable(col1, col2, etc) SELECT EXEC sp_stored_procedure @parameter1, @parameter2, @etc
There are some pros/cons you may want to know regarding this approach that I do not have time to get into here. Sorry. You should be able to search SSC for INSERT INTO EXEC and find some good info. I think the main CON is that you could change your SELECT stored procedure and forget about all of the INSERT INTO EXEC calls you have to it resulting in broken code. Good luck.
November 7, 2006 at 4:48 pm
Consider using a view instead of SP.
_____________
Code for TallyGenerator
November 7, 2006 at 5:00 pm
I should have read the question in more detail, I'm sorry. INSERT INTO...EXEC Isn't going to do it because you only want specific columns.
You could insert your results into a temp table using the insert into...exec method (and then select the columns you want), but that's a very ugly hack (and inefficent)
Just how gnarly is the select in this SP?
SQL guy and Houston Magician
November 7, 2006 at 8:13 pm
The proc I want to use is a variation of the asp.net membership proc that determines what members are in what roles ('aspnet_UsersInRoles_GetUsersInRoles'), and the complication is that the roles and app ids are in characters and the actual roles and actual app ids are GUIDs so the proc goes off and gets them first, then does a SELECT.
I am intrigued by the idea that a view might do it, but even so, I'm adding code that I should be refactoring.
I was thinking I was just being stupid. I guess the stupid thing is that you can't SELECT a,b,c FROM (a derived table resulting from an EXEC).
So I looked at the stored proc and concluded it was badly written, then further mangled by me, and I should just have decided to write a SELECT with joins and put it into the new stored proc, as suggested by some of you.
I can post the proc, and you would see what I mean, but the real point of all this is there is no simple way to SELECT from the EXEC.
So I could Insert the EXEC recordset into temp tables and live with the demons of temp tables and their performance issues.
I could write a view with only the columns I want, by copying and rewriting the stored proc.
But I have decided to clean up the messy 'inside' stored proc and include the code directly in my new one.
Thanks for the prompt responses. I'll be back if I get stuck!!
Phil
November 7, 2006 at 8:39 pm
If it's gonna be "SELECT with joins" you must put it into view.
View is much more effective and much more flexible.
_____________
Code for TallyGenerator
November 7, 2006 at 9:03 pm
Why is a view much more effective?? (I know, that's off topic)
In addition, I need to parameterize it since I do not always seek out the same 'role' that the stored proc is returning the users for.
This is getting complicated... I started out to jam a bunch of data from SQL Server into an empty Access Database, and have spent the day investigating linked servers, permissions for mdbs, and now stored procs within stored procs... arrrgggh.
But I guess this is how you learn.
November 7, 2006 at 9:30 pm
View is precompiled set of joins.
You may use in your query any subset of column list from the view.
You may apply your parameters in WHERE clause. If there are appropriate indexes on background tables they gonna be used by optimiser.
You don't need to use temporary table to store recordset returned by view. You may use view itself. You may join it to another table, include it into subquery, etc.
BTW, there is "parametrised view", they name it "table function".
But overall performance of view will be better in most cases.
_____________
Code for TallyGenerator
November 7, 2006 at 9:51 pm
Thanks. I don't think I really consider using Views enough. I will reconsider using them. Especially in cases like this. I'm not clear on how to getthe parameter to the WHERE clause... do I apply the WHERE clause to the view as if it were a table??
November 7, 2006 at 10:00 pm
Yes. You may forget it's a view and treat it as a table.
The only thing you cannot do is modifying data in view.
_____________
Code for TallyGenerator
November 8, 2006 at 7:05 am
Hi,
You got to create a function which returns a table.
CREATE FUNCTION test_function
(@p1 int, @p2 char)
RETURNS @table_var TABLE
(c1 int, c2 int)
AS
BEGIN
INSERT into @table_var (c1 , c2)
SELECT 1, 2
RETURN
END
GO
Insert into your_table
SELECT * FROM dbo.test_function(10,'a')
GO
November 8, 2006 at 7:25 am
Something like this :
CREATE FUNCTION [dbo].[fnvwTempsTechniciens] (@DateDebut AS datetime, @DateFin AS datetime, @Technicien AS VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT
T.NoTech
, TTR.Technicien1
, BT.[Date]
, C.Nom AS [Nom Client]
, TTR.Simple1
, TTR.Demi1
, TTR.Double1
, TTR.stot1
, TTR.tot1
, TTR.[T dÉbut1]
, TTR.CCQ1
, KM.Pourcent
, TTR.repas1
, TTR.cach1 AS Cacher
FROM dbo._Temps_tech_repas TTR INNER JOIN
dbo.[Bon de travail] BT ON TTR.[N° Bon de travail] = BT.[N° Bon de travail] INNER JOIN
dbo.CLIENT C ON BT.SociÉtÉ = C.[no client] INNER JOIN
dbo.KmVille KM ON C.Ville = KM.Ville INNER JOIN
dbo.Techniciens T ON T.Technicien = TTR.Technicien1
WHERE BT.[Date] BETWEEN @DateDebut AND @DateFin
AND (TTR.Technicien1 = @Technicien OR @Technicien IS NULL)
AND TTR.Technicien1 > '0'
)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply