?
Solved

SQL - Pass a variable in an INSERT subquery

Posted on 2011-10-17
6
Medium Priority
?
513 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:I_s
6 Comments
 
LVL 9

Expert Comment

by:TonyReba
ID: 36979352
Try declaring a variable and then using that as a condition in your query..
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 36979366
seems like there are multiple IDs with [capitalNumber] = @capNum ; so you have 2 options ,
 if you want to insert the last value of ID
,(SELECT MAX([id]) FROM [CapitalRequest] WHERE [capitalNumber] = @capNum))
or if you want to insert all the records
INSERT INTO [ExpenseItem]           ([itemCategory]           ,[itemDescription]           ,[itemCost]           ,[itemQuantity]           ,[capitalId])  
select @iCat,@iDesc,@iCost,@iQnty,[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum
0
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36979393
INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
     VALUES
           (@iCat,@iDesc,@iCost,@iQnty,[id] FROM [CapitalRequest] WHERE [capitalNumber] = @capNum)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 9

Expert Comment

by:TonyReba
ID: 36979400
INSERT INTO [ExpenseItem]
           ([itemCategory]
           ,[itemDescription]
           ,[itemCost]
           ,[itemQuantity]
           ,[capitalId])
SELECT

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

Expert Comment

by:nishant joshi
ID: 36979438
 //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

0
 

Author Comment

by:I_s
ID: 36979440
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question