Solved

Help with query

Posted on 2011-09-22
6
172 Views
Last Modified: 2012-08-13
Hello, I'm trying to do this:

declare @Surveynumber varchar(50) set @SurveyNumber = 'Survey - 100089'
declare @SelectedItems varchar(max) set @SelectedItems = ('1647,1648')
Select * from SurveyDetails
Where SurveyNumber = @SurveyNumber
and SurveyDetailId in(select @SelectedItems)

But getting this error:
Conversion failed when converting the varchar value '1647,1648' to data type int.


I'm attempting to pass into the stored procedure a string of surveydetailid in the @SelectedItems but the surveydetailid is integer..

How can I get this to work???

0
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 36581753
set @SelectedItems = '(1647,1648)'

then:

and SurveyDetailId in @SelectedItems

0
 

Author Comment

by:gogetsome
ID: 36581797
that thows this error:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '1647,1648' to data type int.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 36581861
ok, here's one way to do it, since there aren't integer arrays in sql

declare @Surveynumber varchar(50)
set @SurveyNumber = 'Survey - 100089'
 
create table #selecteditems (id int)

insert into #selecteditems (id) values (1647)
insert into #selecteditems (id) values (1648)

Select * from SurveyDetails
Where SurveyNumber = @SurveyNumber
and SurveyDetailId in (select id from #selecteditems)

drop table #selecteditems
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:gogetsome
ID: 36581892
The problem is that I do not know the surveydetailid as they are passed in a comma seperated list from the application.


This works but I'm not sure on the performance or security.

declare @Surveynumber varchar(50) set @SurveyNumber = 'Survey - 100089'
declare @SelectedItems varchar(max) set @SelectedItems = ('1647,1648')

execute('Select * from SurveyDetails where SurveyDetailId in ('+@SelectedItems+')')

I'm thinking since all of this will be done by the application in code there will be no chance of a SQL injections as there will be no user inputed data. Do you think it would be safe to do it this way? Would there be performance issues?
0
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 36581942
Look into the split function to convert the comma delimited list into the temp table

http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
0
 

Author Closing Comment

by:gogetsome
ID: 36582354
Thank for you help. This will work nicely.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question