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

Connecting to a HiTechnic prototype board to an Arduino

Connecting to a HiTechnic prototype board to an Arduino. If you are thinking to yourself, “ Wouldn't it be fun to take a prototype board from Hitechnic and connect it to my Arduino? I wonder if it is possible...” Well I am here to tell you that Yes, indeed it is possible, it is not only possible it works rather nicely, of course - HiTechnic doesn’t really support this and the NXT documentation doesn’t have a section called “Cutting a cable in half to connect it your Arduino” so it took a little research to make it it happen which is why I thought I would share this information with the world. Step one - the cable  I took a cable from my mindstorms kit and chopped one end off, I then took some nice stiff jumper wires and soldered them onto the ends so I had something that I could plug into the Arduino.  Step two - What goes where.  So the big question was what pins to plug it into on the Arduino. I have an Uno which means that pins A4 and A5 a...
Post a Comment
Read more

Making Qunit show passing tests all the time.

So I have recently started testing my JavaScript with Qunit. Yeah! I looked at Jasmine but I just liked QUnit better except for one thing. I wanted to see all the passing tests all the time. I mean you work hard to get all you code properly tested and structured you want some positive feedback right? Qunit doesn’t show your tests unless you fail – bah! If you grab Qunit from GIT you can see that there is a config section at like, line 570 and this look like a great spot to have an option that would let you always show the passing tests in expanded form or whatever but there isn’t an option for this so I you need to add a hack for it. At (or near) line 210 you will find the following. That's it really - just save and you will have a screen full of happy positive feedback all the time. I mean if you have like fatty 500+ scripts it might start to get to be too much but I just wanted people to know who to do this if the wanted the option. Yeah!
Post a Comment
Read more

Copying items to a output directory using post-build events

Certain times you are going to need to move things to some sort output directory after they are built, for whatever reason - maybe your program is looking for a list of modules that it will load when it fires up (you can do this with prism)? It's easy to do, just go to the "Properties" for your project and select "Build Events". in the box titled "Post-Build event command line" enter your xcopy command. Somthing like this. xcopy "$(TargetDir)PARTSFinderModule.dll" "$(SolutionDir)\MyProject\bin\$(PlatformName)\$(ConfigurationName)\DirectoryModules" /Y That's it.
Post a Comment
Read more