September 16, 2009 at 4:13 pm
Hello,
I'm not exactly new to MS SQL... been using it off and on for a few years now since 2000. Most of my knowledge and background has been in Reporting & Data manipulation. So I can basically do some really great stuff with Views... the problem is that I'm very new to the world of advanced stored procedures(and by advanced I mean more than just writing a stored procedure that SELECTS a resultset to pass to an application).
I have a database I'm creating from scratch and am struggling with this next step. I've created a SPROC to insert a rowset into a table from an Application.
I need to use the data in some of the columns of the inserted table rowset to query against another table and then Insert THAT result into another table. I need to do it again, and I'm just curious as to how I should write something like that. I keep struggling with this and I make a little progress here and there but I keep getting stuck on it because I've written it wrong.
So the short version is "I need to know how to create a resultrowset from ONE stored procedure, and somehow use that resultset from another stored procedure to do additional querying..."
can somebody help me?
Thanks in advance.
September 16, 2009 at 8:20 pm
Provided I understand your goal..
Step 1 inserts data into some table, we'll call dbo.Table_1, you know how to do this I think..
Step 2, code to insert from a query that joins two tables into a third, we'll use dbo.Table_1 again and the existing table dbo.Table_2 and insert the data into dbo.Table_3.
INSERT dbo.Table_3
( FieldName1,
FieldName2,
FieldName3,
FieldName4,
FieldName5 )
SELECT t1.FieldName1,
t1.FieldName2,
t1.FieldName3,
t2.FieldNameA,
t2.FieldNameB
FROM dbo.Table_1 t1 INNER JOIN dbo.Table_2 t2
ON t1.KeyField1 = t2.KeyField1
Is that what you are looking for?
CEWII
September 17, 2009 at 9:06 am
No, but thanks for the response.
What I'm trying to do ... let me just explain it this way.
I have a Quoting system I'm building.
Quote data comes in as 1 row and inserts into a Quotes table.
From that Quotes record, a filter is applied with some calculation to another table that contains a very large block of data. This retrieves a single matching record according to the criteria - and it works really fast. The problem I have, or where I'm stuck, is after I have retrieved that single rowset and inserted it into a smaller table for additional calculation, I need to be able to run additional logic against that single resultset from another stored procedure, before the application which does the front end work executes the SELECT stored procedure to display the final product.
I think I have it "kind" of figured out now but I'm still a little unsure of myself and more feedback would be appreciated. The way I have it setup right now is on success of the INSERT, it EXECUTES an additional stored procedure which does some more logic... I don't know if I am supposed to daisy chain stored procedures to make things happen. I am not really experienced with the idea behind it yet.
I've done all kinds of cool stuff with views, but views are simple. You either can or you can't... stored procedures & UDF's are kind of another story because I don't completely know what my limits are... I think its sort of like being a goldfish in a small bowl for some 20 years and then one day being put into a Lake...more specifically Lake Superior... it's like "Whoa...".
September 17, 2009 at 9:27 am
this might help:
if you have a stored procedure that returns a resultset, wether one row or a million rows, you have to define a table(probably a temp table) to capture the results, so you can then manipulate it....so you MUST know the format of the results coming from a procedure.
insert into sometable(listofColumns)
EXECUTE YourProcedure Param1,param2....
here's a universal example:
--create a table to capture the results returned from a specific stored procedure:
CREATE TABLE #WHORESULTS (
[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL
)
--table now exists, insert some data
INSERT INTO #WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM #WHORESULTS WHERE SPIDINT < 50
--do more stuff to my results
Lowell
September 17, 2009 at 10:50 am
Right, But once the data is put into the table, how do you keep it going to manipulate it? Trigger? Daisy Chained SPROC(Nesting)? Functions? know what I mean? The Application isn't going to tell SQL to do the next step, SQL has to be able to keep the information transformation going on its own without creating a bunch of overhead.
Especially a Temp table. I honestly have not figured out how to insert a Rowset from a Temp Table into an Actual table yet ... or even how to pull information from a Temp Table from a stored procedure once the procedure containing the temp table information has finished processing.
September 17, 2009 at 11:55 am
uogmsmith (9/17/2009)
Right, But once the data is put into the table, how do you keep it going to manipulate it? Trigger? Daisy Chained SPROC(Nesting)? Functions? know what I mean? The Application isn't going to tell SQL to do the next step, SQL has to be able to keep the information transformation going on its own without creating a bunch of overhead.Especially a Temp table. I honestly have not figured out how to insert a Rowset from a Temp Table into an Actual table yet ... or even how to pull information from a Temp Table from a stored procedure once the procedure containing the temp table information has finished processing.
well typically you do all the work in one place or the other...either the application does all the grunt work, and puts the finished product in sql, or SQL does all teh grunt work, and returns the finished results to your application. i would not be doing any back and forth to the application;
I think we need concrete examples of what you want to do, and forget pseudocode and theoreticals.
a single stored procedure can be huge, and do thousands of things before it completes....so you can do all the work server side, same as you could in say a .NET Sub.
moving data from one table to another is not difficult, but i hesitate to put an exampel for fear of muddying the waters.
give us a specific step by step of what you want to accomplish, and let us help you get your mental arms around how to tackle the issue.
for example, show us the CREATE TABLE for the Quotes table...a typcal row that gets inserted...then the stored procedure that goes to the big table and returns one row...show us that.
finally, you said The way I have it setup right now is on success of the INSERT, it EXECUTES an additional stored procedure which does some more logic...
show us that too; the more details you give, the better suggestions we can make.
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply