Using a list in a variable in a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a list in a variable in a SP

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-26 : 13:16:36

I have a stored procedure that will run with inputs. Two of the inputs (@locationlist and @deparmentlist) may contain 1 integer value but they may also contain more than 1 integer value. My current SP runs fine when these values are only 1 integer. But if I try to load more than 1 it says "Msg 245, Level 16, State 1, Line 51
Conversion failed when converting the nvarchar value '332, 102' to data type int."

Any help here? Hopefully this will be a small tweak and not a major rewrite.

Thanks. Below is the code.



DECLARE @LOCATIONLIST NVARCHAR(50)
DECLARE @DEPARTMENTLIST NVARCHAR(50)
DECLARE @SUBSIDIARYID INT
DECLARE @LANGUAGEID INT

SET @LOCATIONLIST = '332, 102' 
SET @DEPARTMENTLIST = '1, 3' 
SET @SUBSIDIARYID = '102'
SET @LANGUAGEID = '2'

-- MAIN

SET NOCOUNT ON

SELECT 
	jd.jobID,
	jd.position_title,
	jd.status,
	jd.report_to,
	jd.job_summary,
	jd.duties,
	cg.full_name as subsidiaryName,
	jd.appURL,
	loc.name as locationName,
	dept.department as departmentName,
	qualifications

FROM jobDescription jd
	inner join ****..company_group cg on cg.ident = @subsidiaryID
	inner join [****].[dbo].[location] loc on loc.id = jd.locationID
	inner join language lang on lang.langID = @languageID
	inner join department dept on dept.deptID = jd.departmentID
WHERE 
	dept.localeID = lang.localeID and 
	jd.locationID in (@locationList) and
	jd.departmentID in (@departmentList) 

SET NOCOUNT OFF

Craig Greenwood

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 13:36:31

You need dynamic SQL of some sort. The list you populate is not treated as a list of values, it is treated as a literal string.

So for @locationlist SQL sees


jd.locationID in ('332, 102')

Instead of your intended


jd.locationID in (332,102)

This would be a dynamic correction to what you have--essentially building the whole query using your variables in the build, then executing the string.


Select @SQL = 'SELECT 
	jd.jobID,
	jd.position_title,
	jd.status,
	jd.report_to,
	jd.job_summary,
	jd.duties,
	cg.full_name as subsidiaryName,
	jd.appURL,
	loc.name as locationName,
	dept.department as departmentName,
	qualifications

FROM jobDescription jd
	inner join ****..company_group cg on cg.ident =' +  @subsidiaryID +'
	inner join [****].[dbo].[location] loc on loc.id = jd.locationID
	inner join language lang on lang.langID = '+ @languageID + '
	inner join department dept on dept.deptID = jd.departmentID
WHERE 
	dept.localeID = lang.localeID and 
	jd.locationID in (' + @locationList +') and
	jd.departmentID in (' + @departmentList + ')'

exec (@SQL)

Poor planning on your part does not constitute an emergency on my part.

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-26 : 13:47:26

I have done a bit of dynamic queries, but not much. I tried your suggestion but I'm getting caught on a few things. I declared @SQL as nvarchar(max) along with my other variable. The error returned is:

Conversion failed when converting the varchar value '
SELECT
jd.jobID,
jd.position_title,
jd.status,
jd.report_to,
jd.job_summary,
jd.duties,
cg.full_name as subsidiaryName,
jd.appURL,
loc.name as locationName,
dept.department as departmentName,
qualifications

FROM jobDescription jd
inner join CCMS..company_group cg on cg.ident =' to data type int.

I'm trying to play with the apostrophes but no luck yet. Am I on the right track?

Here is my full current code:



DECLARE @LOCATIONLIST NVARCHAR(50)
declare @DEPARTMENTLIST NVARCHAR(50)
DECLARE @SUBSIDIARYID INT
DECLARE @LANGUAGEID INT
DECLARE @SQL nvarchar(max)

SET @LOCATIONLIST = '332,102' --332,102
SET @DEPARTMENTLIST = '1, 3'   --1,3
SET @SUBSIDIARYID = 102
SET @LANGUAGEID = 2

-- MAIN
Select @SQL = '
SELECT 
	jd.jobID,
	jd.position_title,
	jd.status,
	jd.report_to,
	jd.job_summary,
	jd.duties,
	cg.full_name as subsidiaryName,
	jd.appURL,
	loc.name as locationName,
	dept.department as departmentName,
	qualifications

FROM jobDescription jd
	inner join chicken..company_group cg on cg.ident =' +  @subsidiaryID +'
	inner join [chicken].[dbo].[location] loc on loc.id = jd.locationID
	inner join language lang on lang.langID = '+ @languageID + '
	inner join department dept on dept.deptID = jd.departmentID
WHERE 
	dept.localeID = lang.localeID and 
	jd.locationID in (' + @locationList +') and
	jd.departmentID in (' + @departmentList + ')'

exec (@SQL)

Craig Greenwood

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 13:59:42

Sorry..forgot to notice that the two variables were numeric have to convert them to strings for the dynamic SQL to create a string..since they are numbers it tries to convert the whole string to a number


SET @SQL = 'SELECT 
	jd.jobID,
	jd.position_title,
	jd.status,
	jd.report_to,
	jd.job_summary,
	jd.duties,
	cg.full_name as subsidiaryName,
	jd.appURL,
	loc.name as locationName,
	dept.department as departmentName,
	qualifications

FROM jobDescription jd
	inner join chicken..company_group cg on cg.ident =' +  convert(varchar,@subsidiaryID) +'
	inner join [chicken].[dbo].[location] loc on loc.id = jd.locationID
	inner join language lang on lang.langID = '+ convert(varchar,@languageID) + '
	inner join department dept on dept.deptID = jd.departmentID
WHERE 
	dept.localeID = lang.localeID and 
	jd.locationID in (' + @locationList +') and
	jd.departmentID in (' + @departmentList + ')'

Poor planning on your part does not constitute an emergency on my part.

craigwg
Posting Yak Master

154 Posts

Posted - 2010-11-26 : 14:05:03

That's awesome. Full Workage. You've made me a hero. I owe you 1 coke!

Craig Greenwood

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 01:52:31

actually you can do this without dynamic sql. jsut make where conditions like


WHERE 
	dept.localeID = lang.localeID and 
	','+ @locationList + ',' like '%,' + CAST(jd.locationID AS varchar(10)) + ',%' and
	',' + @departmentList + ',' like '%,'+ CAST(jd.departmentID AS varchar(10)) + ',%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-11-27 : 06:12:53

which is better for security (no chance of sql injection but worse for performance (because there is no way to use an index on jd.locationID etc.

Better would be to parse the list presented into a table of numbers and then either join on that or do an IN ( SELECT num from numbers).

There are lots of posts on this.

Also -- please read this:
http://www.sommarskog.se/dynamic_sql.html

what you are doing at the moment (the dynamic sql posted here) is potentially unsafe

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-11-27 : 06:48:50

Of use a Splitter Function


WHERE 
	dept.localeID = lang.localeID and 
	jd.locationID in (SELECT SplitterValue FROM dbo.MySplitterFunction(@locationList)) and
	jd.departmentID in (SELECT SplitterValue FROM dbo.MySplitterFunction(@departmentList))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources