Solved

Help with query

Posted on 2011-09-22
6
170 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 34
create an aggregate function 9 34
MS SQL + Insert Into Table - If Doesnt Exist 9 35
Query to capture 5 and 9 digit zip code? 4 20
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

773 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