Tuesday, March 10, 2009

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.

No comments:

Post a Comment

Followers