multiple parameters in one.. - 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

Development Tools

Reporting Services Development

multiple parameters in one..

Author

Topic

jhermiz

3564 Posts

Posted - 2004-11-12 : 10:27:21

Can I do this:

Setup a parameter that allows a user to type in:

100,200,300,400,900, etc...

In one field and then in my Stored Procedure do a

....WHERE Blah IN (@Param)

Where @Param = the numbers entered by the end user above ?

Basically I want an "IN" inside of the WHERE.

Jon

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 10:55:49

great article:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

jhermiz

3564 Posts

Posted - 2004-11-12 : 11:45:36

Hmm...

So I tried it although I want it to convert the types to varchar since it is a varchar field.
Although I still need to change the name of the functions...so I did this:


Create Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
	(IntValue varchar)
AS
begin

	declare @separator char(1)
	set @separator = ','

	declare @separator_position int 
	declare @array_value varchar(1000) 
	
	set @array = @array + ','
	
	while patindex('%,%' , @array) <> 0 
	begin
	
	  select @separator_position =  patindex('%,%' , @array)
	  select @array_value = left(@array, @separator_position - 1)
	
		Insert @IntTable
		Values (Cast(@array_value as varchar))

	  select @array = stuff(@array, 1, @separator_position, '')
	end

	return
end

And inside my stored procedure I have


....WHERE ... AND(@WBS IS NULL OR dbo.IssueWBSElements.WBS IN (dbo.CSVToInt(@WBS)))

The stored procedure checks out fine...
when i run the report I get:

An error has occurred during the report processing.
Query execution failed for data set dsIssueActions
Invalid object name dbo.CSVToInt.

jhermiz

3564 Posts

Posted - 2004-11-12 : 11:47:42

I also tried...
...AND(@WBS IS NULL OR dbo.IssueWBSElements.WBS IN (SELECT IntValue FROM dbo.CSVToInt(@WBS)))

No luck there

jhermiz

3564 Posts

Posted - 2004-11-12 : 12:36:58

Wow nevermind this did work...
really nice Graz!

Jeff thanks for the link...

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources