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

user_5889833 0
Viewed 72

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.

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.

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

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

sql-server sql
3 Answers

You can simply insert these records directly to your ProjectTable! like:

INSERT INTO [ProjectTable] (...)
SELECT proposalPackageID 
FROM [proposalReviewAction]
WHERE approvalTypeID  = 100

If you need a loop anyway for other reasons you can use a cursor instead of a WHILE. Using a cursor helps you have results of a query in variables record by record and process each record separately :

DECLARE @proposalPackageID INT

DECLARE product_cursor CURSOR FOR

    SELECT proposalPackageID 
    FROM dbo.proposalReviewAction 
    WHERE approvalTypeID = 100

OPEN product_cursor  
FETCH NEXT FROM product_cursor INTO @proposalPackageID
WHILE @@FETCH_STATUS = 0
BEGIN

  -- Insert directly into projectTable or call external process  
  INSERT INTO [projectTable] (proposalPackageID ...)
  SELECT @proposalPackageID


  FETCH NEXT FROM product_cursor INTO @proposalPackageID 
END  
CLOSE product_cursor  
DEALLOCATE product_cursor

Hope this will help

user_68767100

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

SELECT * FROM dbo.proposalReviewAction WHERE approvalTypeID = 100;

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

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;
user_44112970 
select column 
from table 
where columns_name 
  in ( select column from table where columns_name = 'value');

NOTE: If we use sub query and it return one value in this case we can use (=,!=,<>,<,>....) Or more than one values, in this case we should use (in,any,all,some )

user_79825480
Related