Help w/ writing Stored Procedure 1

[MTarkington]

Good afternoon,

I apologize for having to write this question...I'm sure it's been answered here before, but I didn't find it...

I am in need of help writing a stored procedure. I need to collect data from 3 tables for a report. I always thought that I could run a stored procedure w/o having to send the data to an actual table in the DB. Is this correct?

I have searched the web, looking for an example of this, but can find none. The help in MS SQL is pretty good, but I haven't found the example that could help me pull data from 3 different tables.

I am not looking for someone to write it for me, but I do need help w/ the logic and/or syntax.

I'm sure I'll need to supply more information, but I really don't know what to say, other than the websites devoted to MS SQL have not been very helpful as of yet...

TIA,
Mark

 

[dbomrrsm]

how do you mean without having to send the actual data to a table in the DB.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[MTarkington]

Everything I've seen so far about collecting data from tables speaks of creating a new table or inserting into an existing table, and I'm of the impression that I can use a stored procedure to retreive data for reports on an as-needed basis.

Does this make more sense?

Thanks,
Mark

 

[damber]

Do you mean that you just want to JOIN the data in these tables together ?

e.g.:

Tb1
Col1 Col2

Tb2
Col11 Col12

Tb3
Col21 Col22

would output:
Col1 Col2 Col11 Col12 Col21 Col22

??

You can do this with the usual SELECT statement - do a search in the help docs for SELECT and JOIN etc or let us know what your table relationships are and how you want the data to appear and we'll let you know what type of join to use.



A smile is worth a thousand kind words. So smile, it's easy!

 

[MTarkington]

What you described is about what I'm after.

I want to add filters to it so everything runs on the server instead of the workstation using the report.

As for the data, on the joins, everything that links to another table is a varchar (it was a string coming out of a Cache DB, however, when we do a data dump, it comes over as a varchar).

Thanks,
Mark

 

[SQLBill]

First you need to just write a script that returns the data you want. This is the important part and a stored procedure has nothing to do with it.

Once you have a script that does what you want, then you turn it into a stored procedure.

Basically, when you write a script, to run it you have to highlight the code and click on the run icon. That's a hassle if you run the same code frequently.

Instead you can put the code into a stored procedure and just run the procedure.

Bottom line....write the code that will return the data you want. You will need to SELECT the columns you want and JOIN the multiple tables in the FROM. Then add your WHERE clause to filter the records. Once that works, then you put it in a stored procedure.

-SQLBill

Posting advice: FAQ481-4875