2

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?

Vikram Singh
  • 435
  • 2
  • 10
  • 3
    Why are you using dynamic sql here? The reason your count is wrong is because in your dynamic sql you are grouping by the string literal 'phonenumbertypeid' not the value in the column. From what I can see using dynamic sql here is not needed. – Sean Lange Oct 07 '21 at 15:21
  • Use parameters instead of building string like this. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15 – Brad Oct 07 '21 at 15:21
  • If you `PRINT` that statement, which is the **easiest** way to debug a dynamic statement, you'd see the problem. (Hint: `SELECT 1`, returns `1`). As Sean said, however, there's is *literally* no need for a dynamic statement here though. – Thom A Oct 07 '21 at 15:23
  • @Larnu Yes, I agree. But earlier it had some parameters and I stripped it to the bare bones, for debugging... – Vikram Singh Oct 07 '21 at 15:33
  • 1
    Seems like the above isn't really representative of what you're trying to achieve, @VikramSingh . – Thom A Oct 07 '21 at 15:34

1 Answers1

2

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
Carlos
  • 1,638
  • 5
  • 21
  • 39
  • As the statement isn't dynamic though, why not just not use dynamic SQL at all? – Thom A Oct 07 '21 at 15:28
  • 2
    @Larnu I do not know what does he want to do exactly. I just answer about why sp_executesql works in a different way that executing the query directly. I think this is what he has asked. – Carlos Oct 07 '21 at 15:31
  • @Carlos Actually `sp_executesql` had some parameters initially. But when I added the group by clause, the results were not as expected. That is why I posted the question, because of the issue I was having with the `group by` clause... – Vikram Singh Oct 07 '21 at 15:48
  • 1
    @Carlos Forgot to mention - tried the solution in your answer, and it works perfectly! Thanks! – Vikram Singh Oct 07 '21 at 16:52