problem when using more than one in where in(', ')

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

SQL Server 2005 Forums

Transact-SQL (2005)

problem when using more than one in where in(', ')

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2009-02-13 : 10:54:56

Hello there i have this procedure:


alter procedure sp_ProcedureX 

@instrumentId as int,
@companyId as int,
@facilityId as int,
@sampleNumber as varchar(4000)


as
begin
set nocount on
	begin transaction
			
	Declare @STRSql varchar(8000)
	set @StrSql = 'update xTable set InstrumentId = ' 
	+ cast(@instrumentId as varchar(20)) + ' where CompanyId = '    + cast(@companyId as varchar(20)) + ' and FacilityId = ' 
        + cast(@facilityId as varchar(20)) + ' and SampleNumber in(' + @sampleNumber + ')'  
						
exec (@STRSql)	
end

when i run this:


sp_ProcedureX 60,1,3,'883719'

it works, but if i add another @sampleNumber:


sp_ProcedureX 60,1,3,'883719,883724'

I ge this error :


Error converting data type varchar to bigint.

How to use more than one @sampleNumber in the (',')? thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 11:00:40

why use dynamic sql?


alter procedure sp_ProcedureX 

@instrumentId as int,
@companyId as int,
@facilityId as int,
@sampleNumber as varchar(4000)


as
begin
set nocount on
	begin transaction
			
	update xTable set InstrumentId =@instrumentId 
        where CompanyId =@companyId 
        and FacilityId = @facilityId 
        and ','+ @sampleNumber + ',' LIKE '%,' +CAST(SampleNumber AS varchar(20)) + ',%'  
						
end

CVDpr
Starting Member

41 Posts

Posted - 2009-02-13 : 13:56:43

quote:

Originally posted by visakh16

why use dynamic sql?


alter procedure sp_ProcedureX 

@instrumentId as int,
@companyId as int,
@facilityId as int,
@sampleNumber as varchar(4000)


as
begin
set nocount on
	begin transaction
			
	update xTable set InstrumentId =@instrumentId 
        where CompanyId =@companyId 
        and FacilityId = @facilityId 
        and ','+ @sampleNumber + ',' LIKE '%,' +CAST(SampleNumber AS varchar(20)) + ',%'  
						
end