We help IT Professionals succeed at work.

I want get the value from Exec(@sql) and assign to @Rowcount(int)

ebertsys
ebertsys asked
on
i have a query built in the Parameter@sql
and i Need the result in a variable

what i have:
Declare  @Table varchar(50)
declare @Rowcount int

set @table = 'Persons'

set @sql = N'(select  @Rowcount= count(*) as from ' + @Table + ')'
exec sp_executesql @sql,'@Rowcount int output', @Rowcount Output


i get the error
Die Prozedur erwartet den Parameter "@params" vom Typ "ntext/nchar/nvarchar".
the procedure expected a Parameter "@param" of type "ntext/nchar/nvarchar"
Comment
Watch Question

Database Expert
Awarded 2016
Top Expert 2016
Commented:
Working solution for you -

DECLARE @Rowcount as int
DECLARE @sql AS NVARCHAR(500)
DECLARE @def nvarchar(500);
DECLARE @Table Nvarchar(50)

set @table = N'Account'
set @sql = N' select @Rowcount = count(*) FROM ' + @Table;
SET @def = N'@Rowcount INT OUTPUT';

exec sp_executesql @sql,@def, @Rowcount=@Rowcount Output
SELECT @Rowcount

Open in new window


Output

(No column name)
4

Open in new window


Modified your code -

DECLARE @Rowcount as int
DECLARE @sql AS NVARCHAR(500)
DECLARE @def nvarchar(500);
DECLARE @Table Nvarchar(50)

set @table = N'Persons'
set @sql = N' select @Rowcount = count(*) FROM ' + @Table;
SET @def = N'@Rowcount INT OUTPUT';

exec sp_executesql @sql,@def, @Rowcount=@Rowcount Output
SELECT @Rowcount

Open in new window

Author

Commented:
tx for fast help
works fine
Thank you