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

DougGifford (11/13/2009)

---

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 ?

bjvaishnani (11/11/2009)

---

DECLARE @SelectQuery NVARCHAR(MAX)
DECLARE @WhereQuery NVARCHAR(MAX)

SET @WhereQuery = ' WHERE COND1 and cond2'


SET @SelectQuery = ' SELECT Col1, col2,, From Table1 ' + @WhereQuery


SET @CountSQLQuery = N'SELECT COUNT(*)
FROM
dbo.Table1 ' + @WhereQuery

SET @TempPara = '@recordcount INT OUTPUT '

execute sp_executesql
@SQLQuery,
@TempPara,
@recordcount = @CountSQLQuery

----------------------------------------------------

As i am geting the err.---> Error converting data type nvarchar(max) to int.

When i try to run the sp...

SET @SQLQuery = ' SELECT Col1, col2,, From Table1 ' + @WhereQuery + ' 
SELECT @RCount = @@ROWCOUNT '
.....
execute sp_executesql 
				@SQLQuery,
				@TempPara,
				@recordcount = @RCount OUTPUT