PetaPoco stored procedure error "Incorrect syntax near the keyword 'FROM'."}

Question

I'm using C# with TSQL and SQL Server 2005

I'm trying to use PetaPoco to return a dataset as a list of objects. this is the code I'm using just now

var s = PetaPoco.Sql.Builder.Append("USE [BI] EXEC [dbo].[TestProcedure2];");
            var result =  db.Query<dynamic>(s);

var result2 = db.Query<dynamic>("USE [BI] EXEC [dbo].[TestProcedure2];");

I think the error message is a generic sql error for when petaPoco fails.

At first I was using a stored procedure with paramaters and the @ character was causing a problem, once that was fixed with @@ i started getting this error so I made a stored procedure with a simple select statement. The procedure executes completely fine in Management Studio.

Using PetaPoco with select statements is fine and the data is mapped both to a dynamic or an object model completely fine. I created a garbage SQL string and it returned the same error which is where I'm getting the generic error idea from.

This is the select I'm using which works fine

var dynTest =
                db.Query<dynamic>(
                   "SELECT TOP 10 * FROM [BI].[dbo].[Managers] ORDER  BY [ConsecutiveDays] desc");

Answer 1

Its trying to append the select clause in front of it.
If you put a ";" at the start of your query it won't try to append it.