1

I have a number of queries which are checking for a record in a table and if there is no such a record it is added. There are 2 queries involved in this process:

1) select id from table where <conditions>

if there is any ids corresponding to I do nothing(or update records with ids as I want), if there are no ids I executing second query:

2) insert into table(<columns>) values(<values>)

Of course <conditions>, <columns> and <values> are correct strings for their context. What I want is to join these 2 queries into one:

insert into table(<values>) select <parameter list> where not exists(select id from table where <conditions>)

So there will be only one query involved instead of 2. The example of this query is:

insert into persons(name) select @Name where not exists (select id from persons where name = @Name)

The problem is that I use parameters for queries and when I execute this composite query I get an exception saying "A parameter is not allowed in this location. Ensure that the '@' sign is in a valid location or that parameters are valid at all in this SQL statement." Is there a way to bypass parameters and don't get an exception for this query? I'm using C# and SqlCe-related classes.

Blablablaster
  • 3,238
  • 3
  • 31
  • 33

1 Answers1

0

What's the benefit of merging the queries into a composite? If the query is supported (and I actually doubt that the SQL Compact Query Parser supports this) the sql engine is still going to have to essentially do both queries anyway. I find the two separate queries more readable.

As separate queries, I'm also willing to bet you could make it substantially faster than the composite query anyway by using TableDirect. Take a look at the answer to this question and benchmark it in your app. It's likely 10x faster than your existing query pair becasue it foregoes the query parser altogether.

Community
  • 1
  • 1
ctacke
  • 66,480
  • 18
  • 94
  • 155
  • There are dozens or hundreds of such queries, by compositing them I can at least shorten the code. And I'll definitely give a try for TableDirect, never used it before – Blablablaster Oct 25 '11 at 14:21
  • While I'm a huge fan of decreasing LOC, shorter but less readable code is not necessarily a win. And be careful with TableDirect. Once you use it, you may never want to write another SQL query again. – ctacke Oct 25 '11 at 14:32