Return Results From Select Statement and Store Count In Variable

Question

I am wanting to execute my select statement, but also store the returned row count into a variable. This is how my DDL is set-up, how can I store the row count from my Select statement in my variable@returnedrows

Declare @returnedrows int;
Insert Into pinkpurplegreen (field1, field2, field3, field4, field5)
Select field1, field2, field3, field4, field5
FROM blue
Select * FROM pinkpurplegreen

Answer 1

You would need to select the @@ROWCOUNT variable

CREATE TABLE dba_152082 (field1 int NOT NULL);
INSERT INTO dba_152082 (field1) VALUES (1),(1), (2);
DECLARE @numberofrows int;
SELECT * FROM dba_152082;
SELECT @numberofrows = @@ROWCOUNT;
PRINT @numberofrows;
DROP TABLE dba_152082;

You should be able to verify the value of your variable in the messages tab after you run that.