Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

execute sp_executesql COUNT(*) Output Expand / Collapse
Author
Message
Posted Wednesday, November 11, 2009 11:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 12, 2009 10:42 PM
Points: 1, Visits: 2

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...
Post #817615
Posted Thursday, November 12, 2009 6:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 10,214, Visits: 13,161
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #817788
Posted Thursday, November 12, 2009 7:17 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 2:18 AM
Points: 610, Visits: 283
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
Post #817811
Posted Friday, November 13, 2009 10:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:29 PM
Points: 127, Visits: 860
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"
Post #818682
Posted Friday, November 13, 2009 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 10,214, Visits: 13,161
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #818692
Posted Friday, November 13, 2009 10:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:29 PM
Points: 127, Visits: 860
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"
Post #818693
Posted Monday, November 16, 2009 4:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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

Post #819721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse