Why my string containing query does not work in stored procedure

Question

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 paste it on storedprocedure and run it.

My query is this :

select acString from account_string where bstatus=1 and  (dbo.getElementFromString(1,acstring) between 1000 and 4587)

(I copied it after running my code by debugging as it is genrated dynaimcally).

The same query I send to stored procedure through parameters like this :

ALTER PROCEDURE [dbo].[sp_shekharSheetDisplay]                          
@action varchar(50)='',                                                                                                                                                                
@query varchar(max)=''   //here  i send my query through parameter
AS        
BEGIN                   
 SET NOCOUNT ON;            
BEGIN TRY     
  if(@action='sheet')                      
 begin              
     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                   

 end   
END TRY        
BEGIN CATCH          
    EXECUTE sp_ErrorDB_AddNew          
 SELECT 3,'SQL Exception'          
END CATCH        
END

It does not work while running. It does not send any data to my dataset (in ds below):

    Cls_budget objBudget = new Cls_budget();
    DataSet ds = new DataSet();
    objBudget.Action = "sheet";
    objBudget.Query = query;
    ds = objBudget.ManageSheet();// I found no data in ds here in my table

But when I try that query statically by copying and replacing the @query in line below : from tbltransaction_detail where accountstring in (@query)

Then I get all table displayed with data (please see this http://prntscr.com/ashf14)

Why it does not work when I use @query?

People seem to come up with magical beliefs about SQL that, somehow, strings are going to magically turn into executed code, or into multiple separate values. It doesn't work like that in SQL. But what surprises me is that, it doesn't work like that in most other mainstream languages. If you call a method in C# that expects to receive a string, and you pass it the literal `"System.Environment.MachineName"`, do you expect that method to receive that literal string or the name of your machine? Now look at your above SQL. Which of the two are you expecting to happen there? *Why*? — Damien_The_Unbeliever, Apr 15 '16 at 09:24
[Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) Similiar case. **`actual values` != `string/subquery`** — Lukasz Szozda, Apr 15 '16 at 09:34

score 1 · Accepted Answer · answered Apr 15 '16 at 09:16

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)

Why my string containing query does not work in stored procedure

1 Answers1

Linked