0

Here is the problem I have full text search extension want to use contains method but the problem is that in stored procedure I have variable which comes from outside and I want this variable to have this (") from start to end and before the last (") I want it to have this (*). What can I do for it ? Is there a way to put variable inside the braces? If not is there any other way ? I am using adonet also I thought about this that lets make it come from c# like this ["[@Text]*"] but I could not find a way to put brace inside of string.

ALTER procedure [dbo].[SelectUsers_SP]
@Text nvarchar(50) = null
as 
begin 
        if (@Text is not null)
            begin
            select * from Users where contains(*, '"[@Text]*"') and IsDeleted = 0
            end
            
    select * 
    from Users 
    where IsDeleted = 0 
    return 0;
end

c#

_service.Select($"'"{text}*"'")
mfor 9
  • 3
  • 2
  • You *don't*. What are you actually trying to search for? If you want to append a wildcard to `@Text` just do it: `@Text + '*'`. `If you want to add extra strings, you'll have to add them the same way. – Panagiotis Kanavos Jun 23 '22 at 10:32
  • It's easier to do this in C# instead of T-SQL. Just make sure you properly escape double quotes, ie `$"\"{text}*\""`. Don't add extra single quotes unless you really want them – Panagiotis Kanavos Jun 23 '22 at 10:35
  • Well, contains doesnt search multiple values it only gets exact match for it to get multiple values just from typing one word u will need it to use like this contains(*, '"s*"') and with this it will get everything that starts with s. – mfor 9 Jun 23 '22 at 10:36
  • 1
    What are you actually trying to search? Post the actual string. It's easier to construct this in C# and use just `CONTAINS(*,@Text)` in SQL. If you want to use `"text*"`, the C# string interpolation expression is `$"\"{text}*\""`. No extra single quotes and the double quotes are escaped. – Panagiotis Kanavos Jun 23 '22 at 10:39
  • i dont know what to do now, as @PanagiotisKanavos answered that might work but should i do it? yes iam using sqlparameter at last to put it in use like this : _database.GetTable($"Select{GetPluralize(_objectName)}_SP", CommandType.StoredProcedure, new SqlParameter("@Text", text)); here is where i put my text AlwaysLearning is your suggestion to define variable like i want to in this ? – mfor 9 Jun 23 '22 at 10:40
  • @PanagiotisKanavos that works like i wanted to, thanks – mfor 9 Jun 23 '22 at 10:44
  • By the way, looks like you want an `else`, also `return 0` is the default – Charlieface Jun 23 '22 at 11:10

0 Answers0