Returning a "Table" type from a stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a stored procedure that has both input and output
parameters. The output is a set of rows from a table and is to be populated
into the dataset. I'm not sure if the following is correct.

Create Procedure ProcName @inputA varchar(50), @ds TABLE OUTPUT
As
SELECT * FROM Table1
WHERE Table1.inputA = @inputA
RETURN @ds
_________________________________

Should I declare the column definitions for the output parameter as what the
compiler had prompted. If yes, is the following correct ?

Create Procedure ProcName @inputA varchar(50), @ds TABLE OUTPUT
As
DECLARE @ds (title varchar(50), Language varchar(50))
SELECT * FROM Table1
WHERE Table1.inputA = @inputA
RETURN ds
___________________________________

"Language" is a column in the table but VS.NET treats it as being used by
the Microsoft Scripting Language for transformation (not sure what it's for).
There are also other columns in the table with names like "File Size" and I'm
getting the same highlights as the "Language" property. How should they be
declared ?
 
Kim said:
I am trying to create a stored procedure that has both input and output
parameters. The output is a set of rows from a table and is to be
populated
into the dataset. I'm not sure if the following is correct.

Create Procedure ProcName @inputA varchar(50), @ds TABLE OUTPUT
As
SELECT * FROM Table1
WHERE Table1.inputA = @inputA
RETURN @ds
___________
Click to expand...

You don't need an output parameter to return rows from a SQL Server stored
procedure. Just run a SELECT in the body of the procedure and the rows will
be returned to the cliene.



Create Procedure ProcName @inputA varchar(50)
As
SELECT * FROM Table1
WHERE Table1.inputA = @inputA

David
 
Just curious. What is a "cursor" type ? According to the MSDN library
definition, it's a reference to a cursor.
 
All rows are returned instead of those that are specified by the condition.
Is there anything wrong with my sql statements ?
 
Kim said:
Just curious. What is a "cursor" type ? According to the MSDN library
definition, it's a reference to a cursor.
Click to expand...

You can pass a cursor from one stored procedure to another, but not back to
the client.

David
 
Things just do not work this way.

1. Table variables are designed to be returned from a table valued
function, not to be used as a parameter.
2. Return type for a stored procedure is always integer
3. There is no sense to return output parameter via RETURN
4. Table @ds does not have column definition
5. Table @ds is not a part of any select statement in your code
 
Back
Top