Returning a "Table" type from a stored procedure

[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 ?

 

[David Browne]

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
___________

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

 

[Guest]

Just curious. What is a "cursor" type ? According to the MSDN library
definition, it's a reference to a cursor.

 

[Guest]

All rows are returned instead of those that are specified by the condition.
Is there anything wrong with my sql statements ?

 

[David Browne]

Just curious. What is a "cursor" type ? According to the MSDN library
definition, it's a reference to a cursor.

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

David

 

[Sericinus hunter]

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