Is it possible to return a OUTPUT parameter AND a query from a stored proecedure? I seem to be able to do one or the other, but doing both isn't. Here is my sample...
Stored Procedure
ALTER PROCEDURE dbo.GetUsersAfter
(
@after DateTime,
@time DateTime OUTPUT
)
AS
BEGIN
SET @time = GetUTCDate();
SELECT
ID,
LastName,
FirstName
FROM Users
WHERE Stored > @after;
END
C# Code to Access
public List<User> GetUsersAfter(ref DateTime after)
{
using (SqlConnection con = MSSQLConnection)
{
using (SqlCommand cmd = new SqlCommand("GetUsersAfter", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@after", SqlDbType.DateTime, 0).Value = after;
SqlParameter rtn = cmd.Parameters.Add("@time", SqlDbType.DateTime, 0);
rtn.Direction = ParameterDirection.Output;
try
{
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
List<User> lst = ReadPositions(dr);
after = (DateTime)rtn.Value;</strong> <strong>// <=== FAILS HERE
return lst;
}
}
finally
{
con.Close();
}
}
}
}
Stepping through the code, it fails on the assignment of the output parameter. rtn.Value is equal to null.
Doing a search on the web or in this forum (so far...) have examples of one or the other.
Thanks for your help.
View Complete Post