SQL Server statement with subselect is invalid

Question

Hey, I am using iBATIS with SQL Server Compact Edition 3.5 and try to do a subselect

INSERT INTO FORMINSTANCE (ID, ID_FORM) 
  SELECT #ID#, f.ID 
  FROM FORM f 
  WHERE ID_PROCESS='10804'

When I commit the transaction I get an SqlCeException (SSCE_M_QP_PARAMETERNOTALLOWED).

That the Symbol '@' is on the wrong place. I think this is the #ID# which is unpredicted in SELECT. #ID# is not the name of the column, it's the value that should be inserted into FORMINSTANCE How can i fix this?

ty

Answer 1

If the # part of the column name…

INSERT INTO FORMINSTANCE (ID, ID_FORM) 
SELECT [#ID#], f.ID 
FROM   FORM f 
WHERE  ID_PROCESS='10804'

If for some odd reason you wanted a dynamic column to be selected at position #1 (not that I really think that this is what you're up to, but anyway), you could get away with:

INSERT INTO FORMINSTANCE (ID, ID_FORM) 
SELECT 
  CASE 
    WHEN @ID = 'foo' THEN foo
    WHEN @ID = 'bar' THEN bar
    ELSE NULL
  END, 
  f.ID 
FROM
  FORM f 
WHERE
  ID_PROCESS='10804'

Answer 2

I don't think you can if #ID# isn't in the FORM table (ie, a parameter). Even if #ID# was in the standard parameter format of @ID, SQL Server CE doesn't support named parameters in that location.