Skip to main content

using data from sub queries as an argument to an insert statement

I have run into this situation more than once, I need to take some piece of data supplied to a stored procedure, use it to get another piece of data from a different table and then take all of that and create and INSERT with it.

If you simply try and stick a sub select into your INSERT you get an error… This of course isn’t what you want.

You will see something like this when using MSSQL 2005.

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

The way to overcome this problem is to declare a variable and the use SET and a sub query to (not surprisingly) set its value. Once you have set the value of your variable you can then use it in your stored procedure just like any other variable.I tend to learn by example so take a look below.

Check out this example.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Kenn

-- Create date: 3/10/2009

-- Description: Adds an entry into the press portal for a document

--

-- =============================================

CREATE PROCEDURE InsertPressPortalDocument

-- Add the parameters for the stored procedure here

@studyIdNumber varchar(5),

@numericDirectory varchar(5)

AS

BEGIN

DECLARE @documentId int

SET @documentId = (SELECT Id FROM Document WHERE StudyIdNumber = @studyIdNumber and SectionNumber = 0)

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

INSERT INTO PressReleases

(DocumentId,

PressReleaseUri,

PressReleaseTypeId,

ReleaseDate,

WordUri,

PDFUri,

HTMLUri)

VALUES(@documentId,

@numericDirectory + '\' + @studyIdnumber + '.htm',

'Industry Study',

GETDATE(),

@numericDirectory + '\' + @studyIdnumber + '.doc',

@numericDirectory + '\' + @studyIdnumber + 'pr.pdf',

@numericDirectory + '\' + @studyIdnumber + '.htm')

END

GO

Can you see where I am declaring the variable documentId and then on the next line setting the value of it using a sub select? Once that is done you can see where I place the documentId variable in the INSERT. It is pretty simple…

Hopefully this helps someone in the same boat looking for an answer.

Comments

Post a Comment

Popular posts from this blog

Unit testing static methods when using Membership and ProfileBase in MVC 4

So you might be thinking that you want to use the Membership class and the ProfileBase class in Microsoft’s System.Web.Security and System.Web.Providers but then you also want to write unit tests as well so you decide to go stand in traffic instead once you are overcome by the plethora of static nonsense that is the design of these two classes. OK, so it isn’t that bad but it is frustrating however there are ways around it and I wanted to try and blog about some of those techniques in the hope that others won’t have to toil with as much frustration as I did when using these classes. Backgound Basically I wanted to find a clean simple way of making use of and extending Microsofts built in forms authentication. It’s easy to use, relatively secure and can save a lot of time and code, well sort of until you want to unit test. The ProfileBase class is great for extending profiles and adding custom properties so you don’t have to introduce a whole lot of redundant code. A good example of...
Post a Comment
Read more

Musings on using a module catalog with Prism

anyone using prism is probably familar with the bootstrapper, the documentation defines the bootstrapper as a class responsible for initialization of an application built using Prism and if you dig into the code for it you will see lots of virtual methods that you can override when setting up your application, one of those is the CreateModuleCatalog(). There are several ways to initialize your modules but using an xaml file is incredibly convenient especially if you only want certain parts of your application to load under certain conditions. Where I work we recently decided to employ this feature so that we could load a subset of the application in the warehouse and not have the sales and other modules loading up at run time, on the flip side the warehouse module doesn't load when the sales team loads the application. The great part for the developer is you don't end up with multiple code bases, your core and infrastructure is shared in one application and different people see...
Post a Comment
Read more

Styling the combox box in WPF

If you want to play with the style of the combo box in WPF the easiest thing to do is use expression blend, drag a combo box control onto you project then click, "Edit Template" -> "Edit a copy"... This will take the entire default template for the combox box and put it into your XAML file. Now go to the XAML and you can grab the entire style and do whatever you like with it... I usually put things like this into a main poject under the solution, something like "shared" is a good name for the project... One thing that is important to note though is that you have to include a reference to the PresentationFramework.Aero assembly cause you will need the Microsoft.Windows.Themes namespace.
Post a Comment
Read more