asked on

SQL - Pass a variable in an INSERT subquery

Hello Experts!

I am trying to insert some data into a SQL Server table from a C# application. Normally I would have no trouble doing this, but I am running into an error when I try to pass a variable in the insert subquery.

I have a table [CapitalRequest] where a [RequestNumber] is assigned an [ID]. I want to select that [ID] where [RequestNumber] = @capNum and INSERT it into [ExpenseItem].

Here is the error message:

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

Thanks in advance!

Here is my SQL code:

INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
     VALUES
           (@iCat,@iDesc,@iCost,@iQnty,(SELECT [id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))

Open in new window

TonyReba

Try declaring a variable and then using that as a condition in your query..

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Umar Topia

INSERT INTO [ExpenseItem]
([itemCategory]
,[itemDescription]
,[itemCost]
,[itemQuantity]
,[capitalId])
VALUES
(@iCat,@iDesc,@iCost,@iQnty,[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum)

TonyReba

INSERT INTO [ExpenseItem]
([itemCategory]
,[itemDescription]
,[itemCost]
,[itemQuantity]
,[capitalId])
SELECT

(@iCat,@iDesc,@iCost,@iQnty,@id FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))

nishant joshi

 //best way to use sql command and prevention from sql injection
            SqlCommand cmd=new SqlCommand("INSERT INTO ExpenseItem(itemCategory,itemDescription,itemCost,itemQuantity,capitalId)VALUES(@iCat,@iDesc,@iCost,@iQnty,(SELECT [id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))");
            cmd.Parameters.Add("@iCat", SqlDbType.Int);//add parameters
            cmd.Parameters["@iCat"].Value = " ";//set value here

Open in new window

I_s

ASKER

Thanks guys,

@aneeshattingal

Exactly what I needed!

INSERT INTO [ExpenseItem]
([itemCategory]
,[itemDescription]
,[itemCost]
,[itemQuantity]
,[capitalId])
SELECT @iCat,
@iDesc,
@iCost,
@iQnty,
[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum

CapitalNumber and ID are unique in the CapitalRequest table, so I should be able to use this query.

Thanks for your quick reply!