where in() Parameters - SQL Server Forums

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)

where in() Parameters

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-11-06 : 14:09:42

Hello there How can i pass more than one number to where in()?
Like this where in(123,321).



code procedure sp_upd_AppLabSenderQueueHST_Inst

@instrumentId as int,
@companyId as int,
@facilityId as int,
@sampleNumber as int


as
begin
set nocount on
	begin transaction
				update AppLabSenderQueueHST  set 
				InstrumentId = @instrumentId							
				where CompanyId = @companyId and FacilityId = @facilityId and SampleNumber in(@sampleNumber) 					

		if @@ERROR = 0 
				 begin
					commit transaction
				 end
			else
				begin
					rollback transaction
					
				end					
end

When i run this:

sp_upd_AppLabSenderQueueHST_Inst 2,1,1,123,321 = too many parameters!.

sp_upd_AppLabSenderQueueHST_Inst 2,1,1,'123,321' = Cant convert varchar to int.

How can i pass 123,321 to SampleNumber in(123,321)? Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-06 : 14:19:53

You can't. You can make a table-valued function, tho. Look up the various Parsing routines on this site. I believe one is fnParseValues

Jim

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-06 : 14:41:26

You can if you choose to use a dynamic query.



Declare @instrumentId as int,@companyId as int,@facilityId as int,@sampleNumber as Varchar(100)
set @InstumentID = 1
set @ComanyID = 2
set @FacilityID = 4
set @SampleNumber = '1,2,4,5,6,7'

Declare @STRSql varchar(2000)
			set @StrSql = 'update AppLabSenderQueueHST  set 
				InstrumentId = ' + @instrumentId							
				+ ' where CompanyId = ' @companyId '+ and FacilityId = ' + @facilityId + ' and SampleNumber in(' + @sampleNumber + ') '
exec (@STRSql )

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

CVDpr
Starting Member

41 Posts

Posted - 2008-11-06 : 15:03:23

quote:
Originally posted by Vinnie881

You can if you choose to use a dynamic query.


create procedure sp_upd_AppLabSenderQueueHST_Inst 

@instrumentId as int,
@companyId as int,
@facilityId as int,
@sampleNumber as varchar(1000),
@cdmId as varchar(1000)

as
begin
set nocount on
	begin transaction
			Declare @STRSql varchar(2000)
			set @StrSql = 'update AppLabSenderQueueHST  set 
				InstrumentId = ' + @instrumentId							
				+ ' where CompanyId = ' + @companyId + ' and FacilityId = ' + @facilityId + ' and SampleNumber in(' + @sampleNumber + ')' + ' and CdmId in(' + @cdmid + ')'
exec (@STRSql )
				

		if @@ERROR = 0 
				 begin
					commit transaction
				 end
			else
				begin
					rollback transaction
					
				end					
end

When i run this:
sp_upd_AppLabSenderQueueHST_Inst 5,1,1,'3166525,3166530','16025,16056' = Syntax error converting the varchar value 'update AppLabSenderQueueHST set
InstrumentId = ' to a column of data type int.

Any clue?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 15:04:02

If you choose to use the dynamic query make sure to also include transactions inside the StrSQL.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 15:16:44

[code]set @StrSql = N'
begin transaction
update AppLabSenderQueueHST set
InstrumentId = '+ cast(@instrumentId as nvarchar(20))+'
where CompanyId = '+cast(@companyId as nvarchar(20))+' and FacilityId = '+cast(@facilityId as nvarchar(20))+'
and SampleNumber in('+@sampleNumber+')

if @@ERROR = 0
begin
commit transaction
end
else
begin
rollback transaction

end

end'[/code]

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-11-06 : 16:12:04

First, avoid dynamic SQL below is the way I would approach it.

1. Create the following Table Valued Function

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter CHAR(1) )
RETURNS @OutputTable TABLE ( [Value] SQL_VARIANT )
AS
BEGIN

DECLARE @Value SQL_VARIANT

WHILE LEN(@StringInput) > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
LEN(@StringInput)))))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [Value] )
VALUES ( @Value )
END

RETURN
END

2. In your procedure do the following

DECLARE @delimiter CHAR(1)
SET @delimiter = ','

update AppLabSenderQueueHST
set InstrumentId = @instrumentId
where CompanyId = @companyId
and FacilityId = @facilityId
and SampleNumber in (select CONVERT(INT,[Value]) FROM dbo.ufn_CSVToTable(@sampleNumber,@delimiter))

The function in step 1 is generic in that you can now convert to any output type you like for your list. Let me know if this works.

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-11-06 : 16:25:07

BTW make sure your variable for the list of numbers is a varchar and is large enough to hold the entire list. notice the function can get the maximum size string of 8000 characters.

CVDpr
Starting Member

41 Posts

Posted - 2008-11-06 : 16:35:02

The hanbingl code worked:


set @StrSql = N'
	begin transaction
				update AppLabSenderQueueHST  set 
				InstrumentId = '+ cast(@instrumentId as nvarchar(20))+'
				where CompanyId = '+cast(@companyId as nvarchar(20))+' and FacilityId = '+cast(@facilityId as nvarchar(20))+'
                                 and SampleNumber in('+@sampleNumber+')

		if @@ERROR = 0 
				 begin
					commit transaction
				 end
			else
				begin
					rollback transaction
					
				end

		end'

ddamico its there gonna be any problem in using the hanbingl code?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-06 : 16:36:58

Do not use dynamic SQL to solve this. Dynamic SQL should be rarely be used. Just use a parsing function. There are several of them here, just search for them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 17:34:05

[code]
CREATE FUNCTION dbo.Split

(

@String VARCHAR(200),

@Delimiter VARCHAR(5)

)

RETURNS @SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(200)

)

AS

BEGIN

DECLARE @SplitLength INT

WHILE LEN(@String) > 0

BEGIN

SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN

LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)

INSERT INTO @SplittedValues

SELECT SUBSTRING(@String,1,@SplitLength)

SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)

END

RETURN

END

--------------------------------------
create procedure sp_upd_AppLabSenderQueueHST_Inst

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

as
begin
set nocount on
begin transaction
update AppLabSenderQueueHST set
InstrumentId = @instrumentId
where CompanyId = @companyId and FacilityId = @facilityId and SampleNumber in(select splitvalue from dbo.split(@sampleNumber,','))

if @@ERROR = 0
begin
commit transaction
end
else
begin
rollback transaction

end
end
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-06 : 18:18:47

quote:
Originally posted by hanbingl

If you choose to use the dynamic query make sure to also include transactions inside the StrSQL.

Why would one need to do that? All Insert, Updates and Deletes are Atomic. Unless there is something special about executing dynamic sql that I don't know about?

EDIT: Oh, I see you are saying that to duplicate the logic of the origianal query. :) To The OP, the transaction handling is not needed unless you have other statements in there that are not shown.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources