CODEWITHSUNDEEP
c#sqlsql-server
Bhavin
Bhavin

Reputation: 260

How to pass in sql "like" condition from c# with single quotes

I have string variable in c#

String Condition = " And LoginName like ''%"+txtLoginName.text+"%''";

I pass this condition variable to a stored procedure like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "GetAllUserMasterBySearch";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Condition ", Condition );

And my stored procedure is here:

create PROCEDURE [dbo].[GetAllUserMasterBySearch] (  
    @Condition nvarchar(max)  
    ) 
as
    declare @Query  nvarchar(max) 

    set @Query = 'SELECT
                      [UserMasterId], [LoginName], [UserName],
                      [UserType], [MobileNo], [Email],
                      [IsLogin], [IpAddress]
                  FROM  
                      UserMaster      
                  WHERE 
                      IsActive = 1 ' + @Condition

    print @query   

    EXEC sp_executesql @Query

How to pass "like" condition from C# ?

Upvotes: 0

Views: 1138

Answers (1)

IgorM
IgorM

Reputation: 1356

You can pass only variable and rewrite your SP as following

alter PROCEDURE [dbo].[GetAllUserMasterBySearch] (  
    @var nvarchar(max)  
) as

declare @Query  nvarchar(max) 
set @Query = 'SELECT
                  [UserMasterId]
                 ,[LoginName]
                 ,[UserName]
                 ,[UserType]
                 ,[MobileNo]
                 ,[Email]
                 ,[IsLogin]
                 ,[IpAddress]
              FROM  
                  UserMaster      
              WHERE 
                  IsActive=1 AND LoginName LIKE %' + @var + '%';

print @query   
EXEC sp_executesql @Query

Update: If you want to play with dynamic SQL then try ''' instead of ''. Honestly I haven't deal with dynamic SQL for a while since it it terrible and not secure approach (as it was already mentioned in comments)

Upvotes: 1

Related Questions