DougGifford (11/13/2009)
Markus Jägerskogh (11/12/2009)
If you need to do it by dynamic code you are boulding the parameters for sp_executesql incorrectly.Short example:
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @CountSQLQuery varchar(30);
SET @SQLString = N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES';
SET @ParmDefinition = N'@result varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @result=@CountSQLQuery OUTPUT;
SELECT CAST(@CountSQLQuery as int);
Good luck!
/Markus
Question: Why
"N'SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' "
and not
" SELECT @result = COUNT(*) FROM INFORMATION_SCHEMA.TABLES' "
What is the N' that preceeds the SELECT ?
Sorry... found my answer at:
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
Has to do with Unicode strings and some stored proceedures like sq_executeSQL.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"