Clash Royale CLAN TAG#URR8PPP

How do I create a dataset in SQL Server and process each record?

I have the following table:

CREATE TABLE [dbo].[proposalReviewAction]
(
[proposalReviewActionID] [INT] IDENTITY(1,1) NOT NULL,
[proposalPackageID] [INT] NULL,
[approvalTypeID] [INT] NULL,
[comments] [VARCHAR](2000) NULL,
[reviewedByID] [INT] NULL,
[reviewedDate] [DATETIME] NULL
) ON [PRIMARY]


I want to return each record that has an approvalTypeID = 100 and use the proposalPackageID in another process that moves the data from the proposal table to the project table.

approvalTypeID = 100

proposalPackageID

I considered using a WHILE loop, but everyone seems to favor processing a dataset but I cannot find any way to do this in SQL/T-SQL.

WHILE

I found an example of a WHILE loops and modified it ...

WHILE

WHILE (SELECT[approvalTypeID] FROM [proposalReviewAction]) = 100
BEGIN
DECLARE @ppID AS INT ;

SELECT [proposalPackageID] AS ppID, [approvalTypeID] AS atID
FROM [proposalReviewAction]

IF (SELECT [approvalTypeID] AS atID FROM [proposalReviewAction]) = 100
PRINT @ppID
ELSE
BREAK
END

PRINT 'I got to the print statement';


... but I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Then, it prints the "PRINT" statement.

Any help is appreciated...

Bob

ppID

1 Answer
1

Given this table:

CREATE TABLE [dbo].[proposalReviewAction]
(
[proposalReviewActionID] [INT] IDENTITY(1,1) NOT NULL,
[proposalPackageID] [INT] NULL,
[approvalTypeID] [INT] NULL,
[comments] [VARCHAR](2000) NULL,
[reviewedByID] [INT] NULL,
[reviewedDate] [DATETIME] NULL
) ON [PRIMARY]


You can select all records with approvalTypeID = 100 by using a simple SELECT statement

approvalTypeID = 100

SELECT * FROM dbo.proposalReviewAction WHERE approvalTypeID = 100;


If you only need proposalPackageID then you can SELECT just it using the QUERY

proposalPackageID

SELECT proposalPackageID FROM dbo.proposalReviewAction WHERE approvalTypeID = 100;


Depending on the processing you could insert directly from here to the 'project table' as well

INSERT INTO [projectTable] (proposalPackageID ...)
SELECT proposalPackageID, ...
FROM dbo.proposalReviewAction WHERE approvalTypeID = 100;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.