declare @SQLSTRING NVARCHAR(MAX);
set @EMP_NAME = ''''+replace(ltrim(rtrim(@EMP_NAME)),' / ',''',''')+'''';
SET @SQLSTRING = N'SELECT EMAIL FROM YOURTABLE WHERE NAME IN ('+@SQLSTRING+')';
EXECUTE SP_EXECUTESQL @SQLSTRING;

quote:

---

Originally posted by sushant17284

Guys , I am facing a small problem to which I just can't seem to find any solution.I am stuck for the last 2 days please help.
Here is the scenario.
I have a user table which has 3 columns : id,name and email.
Now I have an SP in which another SP is called which returns a name of the user on the basis of some criteria.My problem is to find the email ID of that User.
I used this following SQL which is very simple.
Select email from User where name=@EMP_NAME
Here @EMP_NAME is the name returned by the stored proc which is being called within the stored proc which finds this email Id.This works perfectly fine when @EMP_NAME returns only one name like "John,Powell"
Till now everything is fine.The problem happened when my client changed their Stored Proc to return multiple names in the @EMP_NAME separated with a delimiter.
Now I get the value of @EMP_NAME as "John ,Powell / Gary, Mak "
Note that the actual delimiter is "/". Commas are already inserted in the name field.
Now since I get multiple names here , my existing select query fails and return null because there is no name with that value of @EMP_NAME.

I would like anyone of you to help me on how to resolve this.Now I want to retrieve the email id of both the name within the @EMP_NAME.How is it possible ??
The other condition is I can't change client's SP in anyway, So would have to deal with it within my SP.
I don;t know I am stuck I tried using Like as well as split but no luck.I don't want to hardcode the splitted names as well because there can be more than two names also in the @EMP_NAME field in the future.
PLEASE HELP ME !!!!!!!!!!! You guys have always helped me out.I will really appreciate your help.
It looks like it should be a small modification in the query but I just can't seem to get it done. :(
Btw , I am using MS SQL server

---

quote:

---

Originally posted by Lamprey

the first response above will not be able to use an index do to the use of a function

---

quote:

---

Originally posted by malpashaa

quote:

---

Originally posted by Lamprey

the first response above will not be able to use an index do to the use of a function

---

You need to revise your informations regarding index usage when functions used, because not all functions prevent index usage, and in this specific case the optimizer will be able use the index if it exists.

---

quote:

---

Originally posted by hanbingl

1st of all, BAD BAD client! I feel your pain.
you can loop each name or you can generate a nvarchar statement string and use sqlexecute to run it.

declare @SQLSTRING NVARCHAR(MAX);
set @EMP_NAME = ''''+replace(ltrim(rtrim(@EMP_NAME)),' / ',''',''')+'''';
SET @SQLSTRING = N'SELECT EMAIL FROM YOURTABLE WHERE NAME IN ('+@SQLSTRING+')';
EXECUTE SP_EXECUTESQL @SQLSTRING;

quote:

---

Originally posted by sushant17284

Guys , I am facing a small problem to which I just can't seem to find any solution.I am stuck for the last 2 days please help.
Here is the scenario.
I have a user table which has 3 columns : id,name and email.
Now I have an SP in which another SP is called which returns a name of the user on the basis of some criteria.My problem is to find the email ID of that User.
I used this following SQL which is very simple.
Select email from User where name=@EMP_NAME
Here @EMP_NAME is the name returned by the stored proc which is being called within the stored proc which finds this email Id.This works perfectly fine when @EMP_NAME returns only one name like "John,Powell"
Till now everything is fine.The problem happened when my client changed their Stored Proc to return multiple names in the @EMP_NAME separated with a delimiter.
Now I get the value of @EMP_NAME as "John ,Powell / Gary, Mak "
Note that the actual delimiter is "/". Commas are already inserted in the name field.
Now since I get multiple names here , my existing select query fails and return null because there is no name with that value of @EMP_NAME.

I would like anyone of you to help me on how to resolve this.Now I want to retrieve the email id of both the name within the @EMP_NAME.How is it possible ??
The other condition is I can't change client's SP in anyway, So would have to deal with it within my SP.
I don;t know I am stuck I tried using Like as well as split but no luck.I don't want to hardcode the splitted names as well because there can be more than two names also in the @EMP_NAME field in the future.
PLEASE HELP ME !!!!!!!!!!! You guys have always helped me out.I will really appreciate your help.
It looks like it should be a small modification in the query but I just can't seem to get it done. :(
Btw , I am using MS SQL server

---

---

DECLARE @EMP_NAME varchar(1000)
SET @EMP_NAME = 'John ,Powell/Gary, Mak/'');PRINT ''''''SQL Injection Attack!!''''''--'


SELECT	*
INTO	#MyData
FROM 
(
	SELECT 'John ,Powell' AS [name], 'johnp@company.com' as [email]
	UNION ALL SELECT 'Gary, Mak', 'garym@company.com'
	UNION ALL SELECT 'Gary, MakXXX', 'garym3@company.com'
	UNION ALL SELECT 'Gary, Ma', 'garym2@company.com'
) AS T


PRINT ''
PRINT 'Method 1'
SELECT	email
FROM	#MyData
WHERE CHARINDEX(name, @EMP_NAME) > 0

PRINT ''
PRINT 'Method 2'
declare @SQLSTRING NVARCHAR(MAX);
set @EMP_NAME = ''''+replace(ltrim(rtrim(@EMP_NAME)),'/',''',''')+'''';
SET @SQLSTRING = N'SELECT EMAIL FROM #MyData WHERE NAME IN ('+@EMP_NAME+')';
EXECUTE SP_EXECUTESQL @SQLSTRING;

PRINT ''
PRINT 'Method 3'
SELECT	email
FROM	#MyData
WHERE '/' + @EMP_NAME + '/' LIKE '%/' + [name] + '/%'

DROP TABLE #MyData