Why My String Containing Query Does Not Work In Stored Procedure

My situation is that I send a query to the stored procedure to run it though c# code. This query is generated through c# code and it runs successfully when I directly copy and pas

Solution 1:

You are trying to use your passed in query directly within an IN-clause. This will not work...

You might try something with dynamic SQL: This will create your statement as a string, fill in the passed in query as if it was written there directly, and then execute the command dynamically.

But I must admit, that I think it was better to pass in the parameters you use in your "internal" query and solve this without dynamic SQL...

DECLARE@cmd NVARCHAR(MAX)=           
 N'select accountstring,  
 isnull(sum(case when amttype=''dr'' then (amount) end),''0.00'') DR,  
 isnull(sum(case when amttype=''cr'' then (amount)  end),''0.00'') CR,  
 isnull(sum(case when amttype=''dr'' then (amount) end),''0.00'')-isnull(sum(case when amttype=''cr'' then (amount)  end),''0.00'') amt   
 from tbltransaction_detail where accountstring in ('+@query+')  
 group by accountstring';
 EXEC (@cmd)