CODEWITHSUNDEEP
c#databasestored-procedures
Bastardo
Bastardo

Reputation: 4152

Retrieving data from Sql Stored Procedures

How can I retrieve a set of data, a whole column of a table, by using an sql stored procedure?

When I execute the below procedure it gives me only the last ID of the ID column.

ALTER PROCEDURE [dbo].[GetCariIdleri] @don int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @don = ID
FROM TBLP1CARI
END

TBLCARI has a column named ID and I want to take all the values in that column but the result gives only the last value in that column.

Upvotes: 1

Views: 722

Answers (4)

manji
manji

Reputation: 47978

that @don parameter retains only the last id and is returned alone remove it, or if you need it add another select:

ALTER PROCEDURE [dbo].[GetCariIdleri]  

AS BEGIN SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT ID
FROM TBLP1CARI

END

or

ALTER PROCEDURE [dbo].[GetCariIdleri]  @don int OUTPUT

AS BEGIN SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @don = ID
FROM TBLP1CARI

SELECT ID
FROM TBLP1CARI

END

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29619

Try this:

ALTER PROCEDURE [dbo].[GetCariIdleri]

AS BEGIN SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT ID
FROM TBLP1CARI

END

Your version of the proc assigns ID to an output variable - that can only ever have one value.

Upvotes: 4

Gratzy
Gratzy

Reputation: 9389

Your procedure is setting a variable @don if you want all the rows just return the results of the select.

Select ID
FROM TBLP1CARI
where  <criteria>

What actually gets returned technically depends on your RDBMS but I think its what you are looking for.

Upvotes: 3

Marc Gravell
Marc Gravell

Reputation: 1062550

to read a grid of data, you should use ExecuteReader(), i.e.

using(var reader = cmd.ExecuteReader()) {
    while(reader.Read()) {
        // the first column, assuming [n][var]char()
        Console.WriteLine(reader.GetString(0));
    }
}

With a SELECT statement like:

SELECT ID
FROM TBLP1CARI

You can't select all those cells into a single output parameter since you have multiple rows; SELECT is the way to go instead.

Upvotes: 2

Related Questions