CODEWITHSUNDEEP
sqlsql-servert-sql
fuaaark
fuaaark

Reputation: 551

Selecting a value into a variable

I'm playing around with t-sql and would like to be able to select into some variable.

Currently I just want to get a count(*) for every table in the database and print this.

When I try the following:

EXEC('SELECT COUNT(*) FROM '+ @table_name)

This gives the counts for all rows, now, I'd like to store the COUNT(*) into a variable. To achieve this i've declared. I've tried a few different approaches, amongst others this one:

EXEC('SELECT ' + @row_count +' = COUNT(*) FROM '+ @table_name)

Which yields an error (Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='.).

Any idea how to correctly express this?

Upvotes: 0

Views: 223

Answers (5)

Rahul
Rahul

Reputation: 77846

Probably do like this:

declare @rowcount int  
declare @table_name nvarchar(10)
declare @sql nvarchar(max);
set @table_name = N'test';
set @sql = N'SELECT COUNT(*) FROM '+@table_name;
execute sp_executesql @sql,N'@rowcount int output',@rowcount = @rowcount output; 
select @rowcount;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

There is a more efficient way to get the number of rows in a table, by looking at the database metadata. Here is an alternative solution:

declare @NumRows int;
select @NumRows = sum(rows)
from <database>.sys.partitions P join
     <database>.sys.tables T
      on T.[object_id] = P.[object_id]
where index_id IN (0,1) AND T.[type] = 'U' and t.name = '<table>'

Upvotes: 1

Chandra
Chandra

Reputation: 333

Variable has to be declared in the appropriate context. try

declare @table_name varchar(50)

set @table_name ='users'
EXEC('declare @rcnt int;SELECT @rcnt = COUNT(*) FROM '+ @table_name+';select @rcnt ')

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You need to provide output variable from a query and specify that it is for output:

declare @table_name nvarchar(100)
declare @row_count int
declare @sql nvarchar(max)

set @table_name = 'YOUR TABLE NAME'

set @sql = N'SELECT @row_count = COUNT(*) FROM '+ @table_name
exec sp_executesql @sql, N'@row_count int output', @row_count = @row_count output
print @row_count

Upvotes: 1

aF.
aF.

Reputation: 66687

You'll have to use sp_executesql.

Check this article for more info.

Upvotes: 2

Related Questions