Printable View
hi there
I was wondering, in C#, is there a way to obtain a datatable or dataset of results when using sp_executeSQL?
I can use normal SQL queries fine, but when using the stored proc sp_executeSQL, i do not know how to obtain the entire collection of results into a datatable or dataset.
any ideas?
eh eh look familiar?(the place,not me) I'm a BIG fan of the Royal Mile.PHP Code:using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
public class MyClass
{
public static void Main()
{
DataTable authors = new DataTable();
string connString="user id=userid;password=password;server=(local);database=pubs;";
using( SqlConnection cn = new SqlConnection( connString ) )
using( SqlCommand cmd = new SqlCommand( "sp_executesql", cn ) )
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( "@stmt", "select * from authors" );
using( SqlDataAdapter da = new SqlDataAdapter( cmd ) )
{
da.Fill( authors );
}
}
Debug.Assert( authors.Rows.Count > 0, "authors datatable contains rows" );
}
}
I had the same situations
And in my store proc. I had a PRINT command just before sp_executesql
PRINT @sql
exec sp_executesql @sql
And I just remove my PRINT command in my store procedure
and it's working fine
Weird ! but it's work for me
this is a thread dated 2004 :)
Not all that weird actually... ANYthing returned back by SQL Server is considered return output. So the PRINT becomes the first resultset, you have to move to the next resultset in order to get past it.Quote:
Originally Posted by HOASTONGE
-tg