14

I have just installed Sql Server Compact Edition. To my surprise, we can't use stored procedure in sql server CE. Do I have any alternative of Stored Procedure in Sql Server CE. I am strongly obsessed with stored procedure, I can'nt think of an application without stored procedures.

Please help, Thanks in advance.

Edit: Can I use Managed Stored Procedures.

Drew
  • 29,895
  • 7
  • 74
  • 104
Vaibhav Jain
  • 33,887
  • 46
  • 110
  • 163
  • 8
    *"I am strongly obseesed with stored procedure"* - that made me laugh :) – D'Arcy Rittich Jan 16 '10 at 19:39
  • 2
    thats right, I tend to write all my application logic in sp. I don't know if it is a good programming practice or not. Can't help it. :) – Vaibhav Jain Jan 16 '10 at 19:42
  • 1
    It is a good programming practice depending on the technology and implementation. If you are new to SqlCe you must read Steve Lasker's whitepaper. – AMissico Jan 16 '10 at 19:47

4 Answers4

18

SqlCe is a local database for use by an application. There is no need for stored procedures since the database is just a local data store, and the business logic is in the application. It is not an engine. If you need an engine then use SqlExpress or its big brother. See Steve Lasker's Comparing SQL Server Express and Compact Editions Whitepaper at http://download.microsoft.com/download/A/4/7/A47B7B0E-976D-4F49-B15D-F02ADE638EBE/Compact_Express_Comparison.doc. It explains everything you need to know.

No, you cannot use managed stored procedures. SqlCe is in-process.

Also, you might find Data Storage Architecture with SQL Server 2005 Compact Edition at http://msdn.microsoft.com/en-us/library/bb380177(SQL.90).aspx helpful.

AMissico
  • 21,470
  • 7
  • 78
  • 106
2

This blog from Steve Lasker describes how you can implement something similar to sprocs on SQL Server Compact: http://blogs.msdn.com/stevelasker/archive/2008/02/11/stored-procedures-and-sql-server-compact-the-great-debate.aspx

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
1

You will have to use inline sql in your application. Use SqlCeCommand.CommandText Property to specify sql text.

Giorgi
  • 30,270
  • 13
  • 89
  • 125
0

I don't really know what is supported by SQL Server CE, but UDFs can often be substituted for stored procedures. If that doesn't help, try calculated columns and/or indexed views. If those are not available as options, then I think you are stuck with putting the logic in your application.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • None of these things are supported in SQL CE. Think of it almost as an XML file with enforceable relationships. – Aaronaught Jan 16 '10 at 19:51
  • Ah, "think of it as a persisted DataSet" would be more accurate with "writable tables". – AMissico Jan 16 '10 at 19:54
  • @Aaronaught: and some indexes, I would hope? – D'Arcy Rittich Jan 16 '10 at 21:17
  • Limited indexes - no include columns, similar to what we got in SQL 2K. AMissico is right, it's more like a persisted `DataSet` than XML. It's really the bare minimum you could possibly expect from a database. – Aaronaught Jan 16 '10 at 22:04