CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Anand Ra » Wed, 18 Oct 2000 04:00:00



    How do we pass an array to a stored proc in SQL Server 2k. I know it
supports a TABLE DATATYPE, but how do i get it to work in passing to SP.

    Also generally how to pass n number of values to a stored proc, I know
in Oracle u could pass a Table as a parameter to a SP.

    Thanks  in Advance

 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Keith Kratochvi » Thu, 19 Oct 2000 04:00:00


You can.
I would ask WHY?
You negate many benefits of using stored procedures...

Keith


Quote:>     How do we pass an array to a stored proc in SQL Server 2k. I know it
> supports a TABLE DATATYPE, but how do i get it to work in passing to SP.

>     Also generally how to pass n number of values to a stored proc, I know
> in Oracle u could pass a Table as a parameter to a SP.

>     Thanks  in Advance


 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Anand Ram Chitu » Thu, 19 Oct 2000 04:00:00


1.) How do we do it, I mean the syntax or example
2.) Just as nobody limits the number of rows in a table, we would like the stored procedure to specify as much values the data loading programs gives it, so we don't know the no f parameters. Can we have an array construct here ?

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Keith Kratochvi » Thu, 19 Oct 2000 04:00:00




Quote:

> 1.) How do we do it, I mean the syntax or example

Against my better instinct...




--uncomment to view the string


Quote:> 2.) Just as nobody limits the number of rows in a table, we would like the

stored procedure to specify as much values the data loading programs gives
it, so we don't know the no f parameters. Can we have an array construct
here ?
What?
 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Anand Ram Chitu » Fri, 20 Oct 2000 04:00:00


Wonderful !!

How do we get to process the individual rows of the result set returned by


command.

Thanks

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Keith Kratochvi » Fri, 20 Oct 2000 04:00:00


I do not understand your question.
What do you mean by process?
The rows are returned to the calling application.  You would process the
data there.

Keith



> Wonderful !!

> How do we get to process the individual rows of the result set returned by


> command.

> Thanks

> * Sent from Devdex.com http://www.devdex.com The Web Developers Index *
> The world's largest index site for web developers.

 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Anand Ram Chitu » Fri, 20 Oct 2000 04:00:00


I want to look at the individual rows returned by


in the stored proc itself.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

 
 
 

CAN WE PASS A TABLE DATATYPE AS A PARAMETER TO STORED PROCS

Post by Keith Kratochvi » Sat, 21 Oct 2000 04:00:00


When you say "look at" do you mean "do something depending on what the value
of a field is?

If this is the case, you need to use a cursor.  I would also recommend
eliminating the dynamic sql.  Cursors are fully documented within Books
Online.

Keith



> I want to look at the individual rows returned by


> in the stored proc itself.

> * Sent from Devdex.com http://www.devdex.com The Web Developers Index *
> The world's largest index site for web developers.

 
 
 

1. Composite datatype parameters for stored procs?

We have a generic stored procedure interface that was developed using the
traditional ODBC 'C' v3.5 API. We recently stumbled across some Oracle
stored procedures that have output parameters that are not the normal
primitive datatypes but composite datatypes. We don't seem to be able to get
any information on these composite datatypes from the DB catalogue. We also
do not know what datatype to bind for these parameters in lieu of getting
this information from the catalogue. If we manage to successfully bind the
composite datatype parameter, we can never get the stored proc to
successfully execute.

Is there anyway to handle composite datatype parameters in stored procs
using the ODBC 'C' API?

Thanks, Sean

2. UK based SQL Server user group

3. passing larg parameter to Stored Procs from Delphi3

4. CDaoRecordset::Seek error on 2ndry index

5. Parameters passing in Isql / Stored Procs

6. US-NORTH EAST- ORACLE FINANCIALS IMPLEMENTATORS

7. External Procs...passing pl/sql tables as parameters to dll's

8. Q: VFP triggers

9. Q: Datatypes, parameter passing to stored procedures, ...

10. INSERT Statement/Temp Tables/Stored Procs with Parameters

11. pass a table datatype to stored procedure

12. Table Datatype as Stored Proc Parameter

13. Table datatype as parameter to stored procedure