<p>I cannot find an elegant way to get the return value from a stored procedure when using TableAdapters.</p>

<p>It appears the TableAdapter does not support SQL stored procedure return values when using a non-scalar stored procedure call. You'd expect the return value from the auto-generated function would be the return value from the stored procedure but it isn't (it is actually the number of rows affected). Although possible to use 'out' parameters and pass a variable as a ref to the auto generated functions it isn't a very clean solution.</p>

<p>I have seen some ugly hacks on the web to solve this, but no decent solution. Any help would be appreciated.</p>

[{'answer_id': 81971, 'author': 'Mike Dimmick', 'author_id': 6970, 'author_profile': 'https://Stackoverflow.com/users/6970', 'pm_score': 1, 'selected': False, 'text': '<p>The way to get the return value is to use a SqlParameter on the SqlCommand object which has its Direction set to ParameterDirection.ReturnValue. You should check the SelectCommand property of the TableAdapter after calling Fill.</p>\n'}, {'answer_id': 156145, 'author': 'Ty.', 'author_id': 8873, 'author_profile': 'https://Stackoverflow.com/users/8873', 'pm_score': 0, 'selected': False, 'text': '<p>I cannot say for certain because I have do not use TableAdapters, but you might need to look at your stored procedure and include the following around your procedure.</p>\n\n<pre><code>SET ROWCOUNT OFF\n\nBEGIN\n&lt;Procedure Content&gt;\nEND\n\nSET ROWCOUNT ON\n</code></pre>\n'}, {'answer_id': 158819, 'author': 'sharvell', 'author_id': 23095, 'author_profile': 'https://Stackoverflow.com/users/23095', 'pm_score': 1, 'selected': False, 'text': '<p><em>NOTE</em>: The way to go is using a SqlParameter where the Direction = ParameterDirection.ReturnValue</p>\n\n<p>With that said, as someone already mentioned SqlParameters, here is a dynamic method alternate using a DataSet. (if thats how you ride):</p>\n\n<p>Example SQL statement and C# as fallows:</p>\n\n<pre><code>string sql = @"DECLARE @ret int \n            EXEC @ret = SP_DoStuff \'parm1\', \'parm2\'\n            SELECT @ret as ret";\n\nDataSet ds = GetDatasetFromSQL(sql); //your sql to dataset code here...\n\nint resultCode = -1;\nint.TryParse(ds.Tables[ds.Tables.Count-1].Rows[0][0].ToString(), out resultCode); \n</code></pre>\n\n<p>The stored procedure results are loaded into a DataSet and will have as many DataTables as return select statements in the stored procedure.</p>\n\n<p>The last DataTable in the DataSet will have 1 row and 1 column that will contain the stored procedure return value.</p>\n'}, {'answer_id': 1049670, 'author': 'Chris Driver', 'author_id': 5217, 'author_profile': 'https://Stackoverflow.com/users/5217', 'pm_score': 1, 'selected': True, 'text': "<p>Closing this question as it appears return values aren't supported and there is no elegant workaround!</p>\n"}, {'answer_id': 1075006, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://blogs.msdn.com/smartclientdata/archive/2006/08/09/693113.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/smartclientdata/archive/2006/08/09/693113.aspx</a></p>\n'}, {'answer_id': 6301191, 'author': 'Eric Braun', 'author_id': 791980, 'author_profile': 'https://Stackoverflow.com/users/791980', 'pm_score': 0, 'selected': False, 'text': '<p>Actually, all you need to do is to return your final value from the stored procedure using a SELECT statement, not a RETURN statement. The result of the SELECT will be the return value. For example, if you simply make your sp exit statement "SELECT 1" then you\'ll get back a 1. Now just SELECT the actual scalar you want returned.</p>\n'}]
['https://Stackoverflow.com/questions/81360', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5217/']