Can someone clarify why only a single-statement can be executed in a command?

I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?

Have you tried it at all?

From my understanding alot of database servers limited the execution of multiple sql statements because you would end up with people doing stuff like "select * from tablename where column='" + value + "'" and then you would have people that would inject sql into value and they sould turn it into value ='"; DELETE * FROM TABLENAME ".

I think in most cases where multiple statements are executed - perhaps you can use joins? I'm really not sure. For the most part I try to use stored procedures where necessary - But that's just my personal preference. Hope this hielps.|||

I haven't tried, but the documentation (Mobile Edition Books Online) seems pretty clear:

The data provider for SQL Server Mobile has the following limitations:

No support for batch queries. Queries must be a single SQL statement. For example, the following statement is valid:

Copy Code

SELECT * FROM Customers

This statement is not valid:

Copy Code

SELECT * FROM Customers; SELECT * FROM Customers2

|||Right but its not the fact that you will use it - its the fact that many people wouldn't.

IMHO if you need to use batched sql statemnents that are reliant on a state of the prior sql statement you should use stored procedures. (unless of course it isn't supported)|||

Hi MarcD & Shaun,

I would like to clarify the positioning of SQL Server Everywhere Edition (SSEv) against other Microsoft SQL Server products such as SQL Server Standard/Enterprise Edition (SQL EE), SQL Server Express Edition (SQL XE).

SSEv is for small apps where you need basic data store and querying capabilities and not stored procs, sql batching ... etc. If you need these rich/advanced capabilities we would recommend you to use SQL XE.

SSEv is not a redundant product when compared to SQL XE. Each caters to different customer needs.

I hope I have not disappointed you by this post but thats the detail I can give for now :(

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation