CODEWITHSUNDEEP
sql-server
Shekh Adnan
Shekh Adnan

Reputation: 73

How to implement my query in Stored Procedure?

I used this query to make a table NewAddData 

select (
    FirstName +' '+
    coalesce(MiddleInitial + '''', '') +
    ' ' +
    Lastname
) as Name
into NewAddData
from c_client c;

I'm getting the following output if I do

Select * from NewAddData

enter image description here

I want to get name which has apostrophe present in it. The Select Statement is working as

Select Name from AddData where Name like '%Paige%' and Name like '%''%'

And I'm getting the output as Paige A'Harrell

ALTER PROCEDURE GetName @ClientName Varchar(max)
AS
BEGIN
SET NOCOUNT ON;
Select Name from AddData where Name like '%@ClientName%' and Name like '%''%'
END
GO

I'm trying to do this in Stored Procedure, but it is not working. I want to pass any part of the name as parameter and if it has apostrophe, I want to get that name as output. If that name doesn't have apostrophe, it should show that name is not present. Please help.

Upvotes: 1

Views: 51

Answers (3)

Chanukya
Chanukya

Reputation: 5893

ALTER PROCEDURE GetName @ClientName Varchar(max)
AS
BEGIN
           SELECT NAME FROM ADDDATA WHERE  NAME IN
       (
        SELECT TOKEN
        FROM UDF_SPLITSTRING(@CLIENTNAME, ',')
        )
END
GO

Upvotes: 0

Ravi
Ravi

Reputation: 1172

write '%'+@ClientName+'%' inplace '%@ClientName%'

ALTER PROCEDURE GetName @ClientName Varchar(max)
AS
BEGIN
SET NOCOUNT ON;
Select Name from AddData where Name like '%'+@ClientName+'%' and Name like '%''%'
END
GO

Upvotes: 1

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can do it like below :

ALTER PROCEDURE GetName @ClientName varchar(100)
AS
BEGIN
SET NOCOUNT ON;

if exists(select [name] from AddData where [name] LIKE concat('%', @ClientName, '%') and [name] like '%''%')
        begin
            select @ClientName
        end
    else
        begin 
            select @ClientName + ' is not found'
        end
END

Upvotes: 0

Related Questions