Insert with select subquery using SqlCommand

Question

I am having a problem when using an INSERT statement with a nested select. The query works when executing it in the SQLManagement Studio but returns an error when executing it in code.

The query looks like :

INSERT INTO [Projects] 
     VALUES ('1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',
  (SELECT status.statusID from status where name = 'STOPPED')

)

The error returned :

Subqueries are not allowed in this context. Only scalar expressions are allowed

Is there an explanation for this and how will go to solve this issue as I do not know what the id of the Status is, besides executing a separate select query?

Answer 1

You could try this...

INSERT INTO [Projects] 
SELECT '1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',
status.statusID from status where name = 'STOPPED'

The error makes sense because you could have multiple rows from the sub query. In my suggestion, you could also end up with multiple rows too. Should there be more to the filter?

Answer 2

The best way is to create Stored Procedure.

declare @statusID int;

SELECT @statusID=status.statusID from status where name = 'STOPPED'

INSERT INTO [Projects] 
     VALUES ('1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0, 'n/a', 'n/a', 'no', 'A3', 'no', 'Blnk', 'aa',@statusID);