question

bigweed avatar image
bigweed asked

SSRS 2005 - Wildcard with multi value parameter??

Posted - 12/15/2010 : 04:12:54 -------------------------------------------------------------------------------- Hi all I want to use a filter in my dataset to return site records. This filter is going to use values stored in a multi value report parameter. So far, so good. I would ordinarily put the following in the Filter box in the dataset page in VS2005: IN (@Site) However, I dont want to filter for just exact matches for the above values, I also want to return all records where Site number BEGINS WITH the values stored in @Site parameter. I have tried entering: IN (@Site)* and IN (@Site*) but I got errors in both cases. I tried the following but they didnt work either: IN (@Site + N'%') and IN (@Site) + '%' The following works, but only with parameters which are NOT multi value: projitemtrans.siteid LIKE @Site + '%' Can someone please help me with this? Thanks
sql-server-2005ssrsparameterswildcard
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
One way to resolve this is to add a 'false' parameter option. Something like "All Values", with a value that wont ever appear in your data. You then handle that value differently in the SQL WHERE clause: WHERE ColumnX in (@Site) OR 'All Values' = @Site
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack - I think what the OP really wanted was multiple LIKEs, because the IN will not allow wildcards. I think it may only be possible to do this with dynamic sql, so that the multiple value paramater is split and dynamic sql builds it into multiple LIKEs.
1 Like 1 ·
WilliamD, what you are saying seems to make sense to me. How do I create this dynamic SQL code? Im a beginner, so dont have much knowledge with SQL yet!
0 Likes 0 ·
oops, my bad - rushed through reading the question while waiting for a download to complete and hammered out the answer I thought was needed. :(
0 Likes 0 ·
Leo avatar image
Leo answered
You can do - Select * From Table1 Where Column1 like @Site + '%' Declare @Site nvarchar(xxx) Select * From projitemtrans Where siteid like 'SiteNumber' + '%' + @Site + '%'
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

My query is currently: SELECT * FROM PROJITEMTRANS WHERE (PROJITEMTRANS.PROJID LIKE @Site + N'%') Leo, are you saying I need to change this to: SELECT * FROM PROJITEMTRANS WHERE (PROJITEMTRANS.PROJID LIKE @Site + N'%') Declare @Site nvarchar(20) Select * From projitemtrans Where siteid like 'SiteNumber' + '%' + @Site + '%' I am not sure what you are suggesting. Do I replace where it says 'SiteNumber' with something else? I have tried the above as it is, and I get an error
0 Likes 0 ·
@Bigweed - May be I misunderstood. So far your code is fine. Let say you are calling the Stored Procedure from VB .NET- What I normally do is .CommandText = "Your Stored Procedure Name" .Parameters.AddWithValue("site", "value from drop down box") .ExecuteNonQuery() Then that will passed your **site** it into your query. That will be fine. I hope you know how to call your stored procedure with passing parameter, do you?
0 Likes 0 ·
Im very sorry, I am not even sure how to create a stored procedure. I am VERY new to VS2005/SQL2005, ie 2 months in! The SQL code I have at the moment, is in Visual Studio 2005, inside a Reporting Project, against a dataset. How can I use the multi value parameter against a query in there, and use wildcard with it? Sorry if I sound really dumb!
0 Likes 0 ·
@Bigweed - I think you got what you need from Will and myself. Is that correct?
0 Likes 0 ·
Sorry Leo, but it isnt resolved yet. I am trying to use the code that Scott Hauder attached, but not sure how to make it work.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
Bigweed - to elaborate on my dynamic SQL suggestion, you may try something like the example below. It is ***not*** a nice implementation, but fulfills the need here. It will require some changes to how you offer up the parameter, but should be doable. /* Test table with test data */ CREATE TABLE dbo.TestTable (Col1 varchar(100)) ; INSERT INTO dbo.TestTable SELECT 'Site1' UNION SELECT 'Site2' ; /* Create multivalue parameter */ DECLARE @Sites varchar(100) /* Proof that one value in the parameter works with IN*/ SELECT @Sites = 'Site1' SELECT Col1 FROM dbo.TestTable WHERE Col1 IN (@Sites) ; /* Proof that one value in the parameter works with LIKE*/ SELECT @Sites = 'Site1' SELECT Col1 FROM dbo.TestTable WHERE Col1 LIKE @Sites + '%' /* Proof that mulitple values in the parameter doesn't work*/ SELECT @Sites = 'Site1,Site2' SELECT Col1 FROM dbo.TestTable WHERE Col1 IN (@Sites) ; SELECT Col1 FROM dbo.TestTable WHERE Col1 LIKE @Sites + '%' /* Possible Solution - NOT NICE, but will work. You need a Tally or Numbers table / table function : http://www.sqlservercentral.com/articles/T-SQL/62867/ */ SELECT @Sites = ','+@Sites+',' -- notice here, you need to encapsulate your values with commas - this makes the string split work DECLARE @sql varchar(max) SELECT @sql= ' SELECT Col1 FROM dbo.TestTable WHERE ' ; WITH ValueSplit -- This CTE does the string plit, it uses the Table Value Function Nums, which is similar to the tally table in the link above AS (SELECT Value = 'Col1 LIKE ' + QUOTENAME(SUBSTRING(@Sites, N + 1, CHARINDEX(',', @Sites, N + 1) - N - 1) + '%','''')+ ' OR ' FROM dbo.Nums(LEN(@Sites) - 1) WHERE SUBSTRING(@Sites, N, 1) = ',' ) SELECT @Sql = @Sql + Value -- This concatenates the strings into multiple LIKEs FROM ValueSplit SELECT @sql = STUFF(@sql,LEN(@Sql)-2,4,'') -- This cleans up the string that you want to execute --PRINT @SQL -- This will print the query (un)comment as required EXEC(@sql) -- This runs the query (un)comment as required ; /* Cleanup example */ DROP TABLE dbo.TestTable
10 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you don't have a nums table, you can take a look at my answer to this question : http://ask.sqlservercentral.com/questions/15724/get-days-from-the-given-start-date-and-end-date You will find a lightning fast table valued function to give you a nums table with any range and any increment you like.
1 Like 1 ·
@bigweed - exactly so. My code was an example to prove the point. You can change the table Testable to whatever you would need. I also suggest you comment out the `EXEC (@SQL)` and run the PRINT command instead, so that you can see what the t-sql will look like. You will need to make some changes so that the column list and the column being checked with the `LIKE` command are correct. The Numbers table will need creating, either use the example that is linked in my code, or Hakan's from his link. Both a good, Hakan's is just a little better in that no table is required, so no physical I/O will be needed (symantics when you are working with small datasets, but still valid). You can take this code and talk to the devs about it if you like. They shouldn't have a problem with a numbers table/function - they may know of their existence, they may not. I suggest that you read through the article I pointed to, regardless of whether you implement a numbers table - Jeff Moden writes very clearly and explains some complex topics in an easy to understand way. P.S. No apologies needed. You cam asking questions, we aim to provide answers. Would be a poorly named site otherwise :)
1 Like 1 ·
Wow, thats some code!! Im in awe! Thanks so much for your help, much appreciated! I have just now copied and pasted the above into a SQL query window, and get the following: Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 42 Must declare the scalar variable "@Sql". Msg 137, Level 15, State 2, Line 44 Must declare the scalar variable "@sql". Msg 137, Level 15, State 2, Line 46 Must declare the scalar variable "@sql". Any suggestions?
0 Likes 0 ·
I copied the whole of the above in its entorety, not sure if I am supposed to delete or change any of it beforehand? Especially the bit at the end where you say to uncomment lines?
0 Likes 0 ·
bigweed - sorry, I used T-SQL Syntax that only works on SQL 2008 to assign the variable @SQL near the bottom - I have changed that to declare and then set the variable in two steps. I have fixed that now in the code above. Just copy and paste again.
0 Likes 0 ·
Show more comments
Scot Hauder avatar image
Scot Hauder answered
DECLARE @PROJITEMTRANS TABLE(PROJID varchar(50)) INSERT @PROJITEMTRANS SELECT '12344' UNION ALL SELECT '3222' UNION ALL SELECT '32' UNION ALL SELECT '855' UNION ALL SELECT '964' UNION ALL SELECT '967643' DECLARE @param varchar(8000) SET @param = '1,32,45,9645' -- place this in a stored procedure SET @param = ',' + @param + ',' ;WITH cte AS ( SELECT CAST(SUBSTRING(@param, 2, CHARINDEX(',',@param,2)-2) AS varchar(max))[site] ,',' + CAST(REPLACE(@param, SUBSTRING(@param, 1, CHARINDEX(',', @param,2)), '') AS varchar(max))[x] UNION ALL SELECT SUBSTRING(x, 2, CHARINDEX(',', x,2)-2) ,',' + REPLACE(x, SUBSTRING(x, 1, CHARINDEX(',',x,2)),'')[x] FROM cte WHERE LEN(x) > 1 ) SELECT PROJID FROM cte JOIN @PROJITEMTRANS ON (PROJID LIKE [site] + '%')
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Excellent - that will give bigweed the results without the hassle of setting up the numbers table.
0 Likes 0 ·
Hi Scott, thanks for the reply I am not sure what you mean when you say to copy the above into a stored procedure - how do I do this? And which bit do you mean? Also, am I supposed to change any of the names for any of the items in your code to my field/parameter names?
0 Likes 0 ·
I have been looking into stored procedures, do you mean I should go into SQL Server, in the Object Explorer I go to my database, Programmability, Stored Procedures, and then add a new stored procedure there? Or is this something I am supposed to do in Visual Studio somewhere?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.