Help with query
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???
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???
ASKER
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.
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '1647,1648' to data type int.
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank for you help. This will work nicely.
then:
and SurveyDetailId in @SelectedItems