I want to assign the number of rows to "variable"

Question

I want to assign the total number of rows to a variable named "@row_count".

DECLARE @row_count int
SET @row_count = SELECT COUNT(*) FROM information

I know. I'm doing wrong. But I haven't found anything on how to do it.

I want to do something like that:

CREATE PROC add_person
(
    @id tinyint,
    @name nvarchar(max),
    @surname nvarchar(max),
    @salary int,
    @job nvarchar(max)
)
AS
BEGIN
    INSERT INTO information
    VALUES(@id,@name,@surname,@salary,@job)
END

DECLARE @row_count nvarchar(max)
SET @row_count = SELECT COUNT(*) FROM information

BEGIN TRAN
add_person 34,'asdf','asdf',3000,'asdf'
IF @row_count > 33
    ROLLBACK TRAN
ELSE
    COMMIT TRAN
GO;

My goal: to prevent the addition of new people if the number of people exceeds thirty-four.

Rahul · Answer 1 · 2019-04-21T18:38:43.083

1

You can try like below

DECLARE @row_count int;
SELECT @row_count = COUNT(*) FROM information;

edited Apr 21 '19 at 18:38

answered Apr 21 '19 at 18:35

Rahul

73,987
13
62
116

Error *Must declare the scalar variable "@row_count".* – Apr 21 '19 at 18:38
1

@Codeblogger isn't that obvious. Anyways see edit in answer – Rahul Apr 21 '19 at 18:39

score 1 · Answer 2 · answered Apr 21 '19 at 18:36

1

You need to use SELECT or a sub query:

SELECT @row_count = COUNT(*)
FROM information;
--or
SET @row_count = (SELECT COUNT(*) FROM information);

answered Apr 21 '19 at 18:36

Larnu

76,706
10
34
63

Sebastian Brosch · Accepted Answer · 2019-04-21T18:53:12.453

You don't need a variable in this case. You can solve it without using a variable. You can check the result of the query directly on the IF like the following:

BEGIN TRAN

EXEC add_person 34,'asdf','asdf',3000,'asdf'

IF (SELECT COUNT(*) FROM information) > 33
    ROLLBACK TRAN
ELSE
    COMMIT TRAN
END

In case you need to assign the result of COUNT(*) into a variable have a look at the answers here or have a look at the following question on StackOverflow:

Saving a select count(*) value to an integer (SQL Server)

I want to assign the number of rows to "variable"

3 Answers3