Group By clause not working correctly with sp

sp_executesql is not working correctly with the group by clause and, I am debugging this.

I stripped out all parameters from sp_executesql, and have this simple batch:

declare @SQLStatement nvarchar(1000);
set @SQLStatement='select ' cast(count('phonenumbertypeid') as nvarchar(100))  ' from person.personphone 
group by phonenumbertypeid'
exec sp_executesql @SQLStatement

now, this gives me a result of 1, which is surely wrong.

I ran the same statement without sp_executesql and, it gives me the correct result of 736, which is correct:

select cast(COUNT(PhoneNumberTypeID) as nvarchar(100)) from person.PersonPhone 
group by PhoneNumberTypeID

I strongly believe that the problem is being caused by the cast that I am performing in the @SQLStatement:

set @SQLStatement='select ' cast(count('phonenumbertypeid') as nvarchar(100))  ' from person.personphone

How can I solve the issue that I am having with the group by clause, in the sp_executesql statement?

CodePudding user response：

If you execute this you will see the problem:

declare @SQLStatement nvarchar(1000);
set @SQLStatement='select ' cast(count('phonenumbertypeid') as nvarchar(100))  ' from person.personphone 
group by phonenumbertypeid'
select @SQLStatement

You are executing this:

select 1 from person.personphone group by phonenumbertypeid

Try this (not tested):

declare @SQLStatement nvarchar(1000);
set @SQLStatement='select cast(count(''phonenumbertypeid'') as nvarchar(100)) from person.personphone 
group by phonenumbertypeid'
exec sp_executesql @SQLStatement