| Author |
Topic |
|
oepirobo
Starting Member
36 Posts |
 Posted - 2002-10-29 : 08:57:20
|
| I just found out about the table data type and I want to use it in a stored procedure, so I looked it up and this is what i did:CREATE PROCEDURE CLI_VerCliente(@tblEmail table (EMA_IdEmail varchar(30),EMA_IdEmailType real) output)AS...goAnyway, the thing says there's " incorrect syntax near the word 'table' ". So how does it work then? Also, can I return more than one table from a stored procedure (as in completely separate tables - no joins)? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-29 : 09:03:49
|
| I know you can't pass a table variable to a stored procedure, and I don't think you can declare it as an output variable either. In truth, it makes no sense to do this in a stored procedure anyway...you'd just SELECT out the results when you execute the procedure. You can however create a user-defined function that returns a table. Books Online has an example under the CREATE FUNCTION statement. If you need to return multiple tables you'd need to write separate functions for them.What exactly are you trying to do anyway? |
 |
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2002-10-29 : 09:09:39
|
| I'm trying to return contact information for our clients, this includes emails, phone numbers and addresses, which is why I think I need separate tables for each. I can do this easy with a stored procedure for each one but I don't think it's the best way so I wanted it all in one. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-29 : 09:13:36
|
| Yes, setting them up in separate tables would be a good idea. It makes more sense to have 3 procedures to return those results separately as well; returning 3 sets of results in one procedure is possible, but you need to do some gymnastics on the client computer end to separate out those 3 sets. Doing them as 3 separate calls is a lot easier and will probably perform just as well as one call would. |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-29 : 10:28:06
|
| HiCan't you join the tables somehow or would that be too naive of me?This would be my architecture:app layer with calls to BL/task layer-------------------------------------BL task layer (specific Business Logic task) calls BL data access layer (calling 3 methods from the BL Data Access layer to get data)-------------------------------------BL Data Access layer (calls 3 sp's)-------------------------------------sp's in out beloved SQL ServerThis way you can build specific application tasks, reusing the data access components to retrieve / modify entities in a database.When the database changes only the Data Access layer is changed. But uh, might be overkill in your case, hehe.I would only return multiple recordsets if i'm one hundred percent sure there won't be any clients that don't support that. So I never use um. I think it's ugly yakkiebah as well.Jeroen. |
|
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2002-10-29 : 15:42:39
|
| That's exactly the architecture that I'm using, but since we're in the initial phases we were just testing the system with the case that I exposed before |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-10-30 : 09:01:56
|
| if there are not too many emails, numbers and addresses per client, perhaps you could create a cross tab of each table, returning one row per client, then join all three such that all the details are in separate columns (some/many of which would be null I suppose)----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
oepirobo
Starting Member
36 Posts |
Posted - 2002-10-31 : 07:55:43
|
| It wasn't really worth the trouble. I finally decided to use separate stored procedures for each table, but I still think it should be possible to return more than one record set per stored procedure. So is it completely out of the question for SQL Server 2000? if so, I wish it would be included in future versions. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 08:31:04
|
quote:
...but I still think it should be possible to return more than one record set per stored procedure.
Ummmm, did you miss this part earlier?quote:
...3 sets of results in one procedure is possible, but you need to do some gymnastics on the client computer end to separate out those 3 sets
You can have a stored procedure return thousands of separate sets of data. You can access those through an ADO Recordset object, using the NextRecordset method to move to the next set and process it. However, it's very easy to lose track of which recordset is currently active. There's very little benefit to this for 3 recordsets; it's much EASIER to create 3 different recordset objects, one for each table or procedure you want to query. That way you can name them EmailRecordset, PhoneRecordset, etc. and know exactly which one is which. You can also modify the stored procedures without affecting the ADO code that uses them. |
|
|
|
|
How to use table data type