I have a problem when i write procedure on sqlserver 2000.
[code]
DECLARE @intTotalRecord int;
DECLARE @strTables varchar(200);
DECLARE @strWhereDescs varchar(200);
DECLARE @strSQL varchar(500);
SET @strTables = 'MyDB.dbo.MyTable';
SET @strWhereDescs = ' name like ''%kane%''';
SET @strSQL = 'SELECT COUNT(*) FROM ' + @strTables + ' WHERE ' +
@strWhereDescs + ' ';
execute(@strSQL);
[/code]
above works well.
than i need store the result to @intTotalRecord, how can i do?
i have try below:
[code]
....
SET @strSQL = 'SELECT @intTotalRecord=COUNT(*) FROM ' + @strTables + ' WHERE
' + @strWhereDescs + ' ';
....
[/code]
but that is a error:
"
msg 137，level 15，stat 1，line 1
'@intTotalRecord' must be declared.
"
But i have DECLARE @intTotalRecord.
Why?

How about this??

DECLARE @intTotalRecord int
DECLARE @strTables varchar(200)
DECLARE @strWhereDescs varchar(200)
DECLARE @strSQL varchar(500)
DECLARE @strSQL2 nvarchar(500)
SET @strTables = 'information_schema.tables'
SET @strWhereDescs = ' table_name like ''%a%'''
SET @strSQL = 'SELECT COUNT(*) FROM ' + @strTables + ' WHERE ' +
@strWhereDescs + ' '
execute(@strSQL)
SET @strSQL2 = N'SELECT @intTotalRecord=COUNT(*) FROM ' + @strTables +
' WHERE ' + @strWhereDescs + ' '
EXECUTE sp_executesql @strSQL2,N'@intTotalRecord int
OUTPUT',@intTotalRecord OUTPUT
PRINT @intTotalRecord

---
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
---

THX!  It works!
Show quoteHide quote

Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?