March 14, 2008 at 11:51 am
Thanks to this site, I've been exposed to sp_executesql. I'm tyring to utilize it in something I'm doing and have run into a problem trying to use a parameter in a where clause. I've streamlined the example so this code can be copied and pasted in it's entirety to show what I'm trying to do. I've google searched, BOL searched as well as searched this site and having a hard time finding an example of what I'm trying to do (perhaps I CAN'T do it this way??).
-- Set up a dummy table for example purposes
if object_id('tempdb..#temp') is not null
drop table #temp
go
create table #temp
(EmplID int,
LastName varchar(30),
FirstName varchar(20),
EmailAddress varchar(50))
insert into #temp values (100, 'Doe', 'Jane', 'jane.doe@address.com')
insert into #temp values (101, 'Smith', 'John', 'john.smith@address.com')
insert into #temp values (102, 'Jones', 'Sally', 'sally.jones@address.com')
insert into #temp values (103, 'Williams', 'Tom', 'tom.williams@address.com')
-- Declare variables
declare @sql nvarchar(max)
declare @params nvarchar(max)
declare @criteria varchar(100)
-- Example 1
-- Single item in where clause
set @criteria = 'jane.doe@address.com'
set @sql = N'select EmplID, '
set @sql = @sql + N' LastName + '' '' + FirstName as Associate '
set @sql = @sql + N' from #temp '
set @sql = @sql + N' where EmailAddress = @assocCriteria '
set @params = N'@assocCriteria varchar(1000)'
exec sp_executesql @sql, @params, @AssocCriteria = @criteria
-- Example 2 - This is what I WANT to be able to do!!
-- Trying to use "in" in where clause
set @criteria = '''jane.doe@address.com'', ''tom.williams@address.com'''
set @sql = N'select EmplID, '
set @sql = @sql + N' LastName + '' '' + FirstName as Associate '
set @sql = @sql + N' from #temp '
set @sql = @sql + N' where EmailAddress in (@assocCriteria) '
set @params = N'@assocCriteria varchar(1000)'
exec sp_executesql @sql, @params, @AssocCriteria = @criteria
-- Example 3
-- Doesn't utilize @params, but works, so is this "wrong"??
-- Seems to be defeating the purpose??
set @criteria = '''jane.doe@address.com'', ''tom.williams@address.com'''
set @sql = N'select EmplID, '
set @sql = @sql + N' LastName + '' '' + FirstName as Associate '
set @sql = @sql + N' from #temp '
set @sql = @sql + N' where EmailAddress in (' + @criteria + ') '
exec sp_executesql @sql
Thank you -
Lisa
March 14, 2008 at 12:06 pm
There are two ways I can think of to get that to work.
The first would be to insert the criteria into a temp table created in the calling procedure/script, then use the temp table in the "IN" clause.
The second would be to use a string-parsing function in the dynamic script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 12:12 pm
IN does not play well with variables. When you use variable for your list Sql sees it as 1 value not the list you expect.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2008 at 12:24 pm
Thank you Jack and GSquared. I will try what you suggested GSquared. Is my Example 3 a bad idea? Should I just avoid that path? If there is no loophole or "gotcha" with that option, I may just try that.
Thanks for the feedback!!
Lisa
March 14, 2008 at 12:43 pm
Option 3 is okay for what you want to do. The only issue, and this is true of any dynamic SQL, is that you need to beware of and prepare for SQL Injection. So you need to check that there are no SQL keywords etc. For example in option 3 I could put "; Delete From users;" and potentially wipe out a table. This may not be a great example, but I think it gives you the idea. Search for SQL Injection on this site or google and you will find plenty of resources.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2008 at 12:50 pm
Yep, that was my fear Jack.
Thanks again to all. This site is the best.
Lisa
March 14, 2008 at 1:21 pm
There is absolutely no reason to use Dynamic SQL for what you want to accomplish. There are plenty of other options available. All you need to do is pass in a delimited string of email addresses such as 'email1@email.com,email2@email.com.' You can then use XML to break the delimited string into a tables using the nodes method. This can be directly joined to the table in question. IMO whenever you need to use an IN clause chances are you should be joining that set of data not using IN.
The code:
-- Set up a dummy table for example purposes
if object_id('tempdb..#temp') is not null
drop table #temp
go
create table #temp
(EmplID int,
LastName varchar(30),
FirstName varchar(20),
EmailAddress varchar(50))
insert into #temp values (100, 'Doe', 'Jane', 'jane.doe@address.com')
insert into #temp values (101, 'Smith', 'John', 'john.smith@address.com')
insert into #temp values (102, 'Jones', 'Sally', 'sally.jones@address.com')
insert into #temp values (103, 'Williams', 'Tom', 'tom.williams@address.com')
-- Declare variables
declare @criteria varchar(100),
@x XML
set @criteria = 'jane.doe@address.com, john.smith@address.com'
SET @x = '<i>' + REPLACE( @Criteria, ',', '</i><i>') + '</i>'
SELECT a.EmplID,
a.LastName + ' ' + a.FirstName as [Associate]
FROM #temp a
INNER JOIN(
SELECT x.i.value('.', 'VARCHAR(50)') AS [Email]
FROM @x.nodes('//i') x(i)
) AS b
ON a.EmailAddress = LTRIM(b.Email)
Need help creating a string of email accounts based on certian criteria use XML.
DECLARE @EmailAddresses VARCHAR(MAX)
SET @EmailAddresses =
(SELECT CASE WHEN Row_Number() over(order by emailaddress) <> 1 then
',' + LTRIM(RTRIM(EmailAddress))
else
LTRIM(RTRIM(EmailAddress))
end
FROM #temp
--Where LastName like 'Will%'
FOR XML PATH(''))
select @EmailAddresses
Edit to fix XML tags
March 14, 2008 at 1:30 pm
Wow.... that's great! I will bag the sp_executesql (perhaps it will be applicable in a future effort) and go with the XML datatype (something I've not had a chance to use yet as well and have wanted to).
THANKS!!
March 14, 2008 at 1:39 pm
I posted the code to create a delimited string, but you should be aware of some things. In some cases, using a function that concatenats the string using a select statement against a given table, can be better for performance. A select statement that concatenates the string for you is usually better under the following circustance: You need to return a column and a delmited string per that column.
For example, say you wanted to return a list of companies (company id) and a string of employees for that company (adam,john,mary).
Your return would look like this:
1 adam,bill, jeff
2 james, john, mike
The XML method would not work as well in this case becuase XML has to be called for every distinct row of the group by versus one time. The performance difference on a small subset of data is unnoticable to the human eye, but on large tables, it can be a few hundred MS. I am not saying under that circumstance a function is always better, but I am saying that it has been proven to be better under certain circumstances.
Since all you need is a delmited string of email addresses, XML will be the best performing option.
refer to this thread for test code and more info
http://www.sqlservercentral.com/Forums/Topic465637-149-1.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply