execute sp_executesql COUNT(*) Output

Question

Post reply

execute sp_executesql COUNT(*) Output

bjvaishnani

SSC Veteran

Points: 257
More actions
November 11, 2009 at 11:11 pm

#96475

DECLARE @SelectQueryNVARCHAR(MAX)
DECLARE @WhereQueryNVARCHAR(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...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I think you need your @COuntSQLQUery to be this: "Select @recordcount = count(*) from dbo.table1"

The question now becomes, why are you using dynamic sql for this?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Hunterwood SSCrazy Points: 2074 More actions · Answer 2

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 SSCommitted Points: 1815 More actions · Answer 3

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 ?

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

The N signifies unicode and keeps a conversion from happening. sp_executesql expects nvarchar (Unicode) parameters so when assigning values to them you should use N'string'

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DougGifford SSCommitted Points: 1815 More actions · Answer 5

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"

Sergiy SSC Guru Points: 110206 More actions · Answer 6

bjvaishnani (11/11/2009)
DECLARE @SelectQueryNVARCHAR(MAX)
DECLARE @WhereQueryNVARCHAR(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

_____________
Code for TallyGenerator