InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 00:14:52 GMT20http://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxThanks Steven. I will have a play. Great stuff.Many Thanks,Phil.Sun, 18 Nov 2012 07:49:18 GMT2Tallhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxCreate a stored procedure like this (I will assume you have already created the splitter function):[code="sql"]CREATE PROCEDURE dbo.GetTypeDesc @strTypes VARCHAR(255) ,@strDelimiter CHAR(1)ASBEGIN SELECT DISTINCT [Type] ,[TypeDesc] FROM #TempTable AS tt CROSS APPLY dbo.tvfDelimitedSplit8K(@strTypes,@strDelimiter) AS ds WHERE tt.[Type] = ds.ItemEND[/code]Then call the procedure by passing in your delimited string as the input parameter. Of course, instead of a temp table as used here for demo purposes you would probably have a more complex query against actual tables.[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [Type] CHAR(1) NULL, [TypeDesc] CHAR(1) NULL, PRIMARY KEY (ID), UNIQUE (ID))INSERT INTO #TempTableSELECT 'T','A'UNIONSELECT 'P','B'UNIONSELECT 'M','C'UNIONSELECT 'B','D'UNIONSELECT 'X','X'UNIONSELECT 'Y','Y'UNIONSELECT 'Z','Z'/* Display sample data */SELECT [Type] ,[TypeDesc]FROM #TempTable/* Display the filtered data */EXEC dbo.GetTypeDesc 'T,P,M,B',',' -- comma delimited--orEXEC dbo.GetTypeDesc 'T|P|M|B','|' -- pipe delimited[/code]The Splitter Function is a TABLE-VALUED FUNCTION. So the output of that function is a TABLE just like a view or temp table and thus you can use the output to join to any other table. This turns the entire query into a set-based query that doesn't require additional looping once the function has parsed the delimited string into individual rows.The input string 'T,P,M,B' could be the output from an HTML dropdown, for example, and then the keys represented in the delimited string are used to cross-filter a query to show only the related rows. Thu, 15 Nov 2012 10:07:27 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxHi Steven. I read 'Jeff's' post after Eugene's replied (I did a search of the forum). To be honest I have no idea how to implement. I never took to programming so when people start talking functions my brain switches off........Feel free to educate me with an example, you never know, the penny might just drop!At the moment I have a query that I use in a report data set. When users want to select multiple values I provide them with another data set to select values from using IN.How would I replace with a function, it all seems complicated :)Thanks,Phil.Thu, 15 Nov 2012 09:04:31 GMT2Tallhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxUse Jeff Moden's splitter function (copied below for convenience).[code="sql"]/* Sample Data */IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [Type] CHAR(1) NULL, [TypeDesc] CHAR(1) NULL, PRIMARY KEY (ID), UNIQUE (ID))INSERT INTO #TempTableSELECT 'T','A'UNIONSELECT 'P','B'UNIONSELECT 'M','C'UNIONSELECT 'B','D'UNIONSELECT 'X','X'UNIONSELECT 'Y','Y'UNIONSELECT 'Z','Z'/* Display sample data */SELECT [Type] ,[TypeDesc]FROM #TempTable/* Show just the rows that match the delimited string */DECLARE @strTypes VARCHAR(50)SET @strTypes = 'T,P,M,B'SELECT DISTINCT [Type] ,[TypeDesc]FROM #TempTable AS ttCROSS APPLY dbo.tvfDelimitedSplit8K(@strTypes,',') AS dsWHERE tt.[Type] = ds.Item[/code][code="sql"]CREATE FUNCTION [dbo].[tvfDelimitedSplit8K] ( @pString VARCHAR(8000) ,@pDelimiter CHAR(1) )RETURNS TABLEWITH SCHEMABINDINGASRETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS ( SELECT 1 FROM E1 a ,E1 b ), --10E+2 or 100 rows E4(N) AS ( SELECT 1 FROM E2 a ,E2 b ), --10E+4 or 10,000 rows max cteTally(N) AS ( SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS ( SELECT 1 UNION ALL SELECT t.N + 1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ), cteLen(N1,L1) AS ( SELECT s.N1 ,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000) FROM cteStart s ) SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1) ,Item = SUBSTRING(@pString,l.N1,l.L1) FROM cteLen l ;GO[/code]Wed, 14 Nov 2012 12:40:27 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxThanks for posting Eugene. The approach I have become used to implementing seems to work. I did search the forum many moons ago regards the approach you recommend but my grey matter couldn't get a handle on how to implement it :)In the appoach I have adopted the parameters list a set of values returned by the underlying query (that I want the user to see). They can select 1 or all values. So for instance if a product came in varying colours I might have a query that returned red, blue, yellow. The parameter uses this query and displays values as available selections. The user can select red,blue and yellow or just red. Data returned is filtered by their selection. I'm not trying to teach you how to suck eggs by the way, just explaining it as I see it! I am but a mere novice when it comes to the black art of writing code........... Thnaks,Phil.Tue, 13 Nov 2012 07:54:40 GMT2Tallhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxTo pass values as a comma separated string, you need to concatenate them into one comma separated string and pass it.To use it in you procedure, you can use "IN" clause with Dynamic-SQL, or use some string splitter functions (you can find good one on this forum) to split into table.And as I said before, "IN (@Paremeter)" is exactly the same as "= @Paremeter", and it is irrelevant of what you have inside of @Paremeter.Tue, 13 Nov 2012 07:43:28 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxHi. I have managed to get it working. The query has been written for a report. Let me explain. I was never able to workout how to pass values as a comma separated string so I have a fudge. I have a number of datasets that allow multi-value selection, this is where the IN (@Paremeter) comes in. Example:[quote]SELECT DISTINCT Type,CASE WHEN Type = 'T' THEN 'A'WHEN Type = 'P' THEN 'B'WHEN Type = 'M' THEN 'C'WHEN Type = 'B' THEN 'D'END AS TypeDescriptionFROM MyTable[/quote]The report parameter allows multi-value selection and values are derived from a query.So for my boolean values I have datasets that returns both true and false. The report parameters default to 'True & False'. So the AND clause remains. The user can select 'True' for one parameter then 'False' for the other (or what ever combination they want).I hope that makes sense!Phil.Tue, 13 Nov 2012 07:21:19 GMT2Tallhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspx[quote][b]2Tall (11/13/2012)[/b][hr]Hi to all. I haven't posted for a while and it is now time to wear my sql cap again :)I have a query which in brief has the following filter:[quote]WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc[/quote]The bit I am interested in is:[quote]AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc[/quote]The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.Can this be done?Kind Regards,Phil.[/quote]1st. What you are expecting to have in @Types? Is it comma separated string? If yes, then your "Type IN (@Types)" filter will never work as it is absolutely the same as "Type = @Types"2nd. You should really show what you have tried with OR.But I guess it should be like that : [code="sql"]AND ( ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq OR ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc )[/code]Tue, 13 Nov 2012 06:58:20 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1384036-338-1.aspxHi to all. I haven't posted for a while and it is now time to wear my sql cap again :)I have a query which in brief has the following filter:[quote]WHERE ProductId = @ProductId AND StructureVersion = @StructureVersion AND Type IN (@Types)AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc[/quote]The bit I am interested in is:[quote]AND ISNULL(CVProdFolder.CustomFieldValueBit,'False') = @ProdFolderReq AND ISNULL(CVProdAidDoc.CustomFieldValueBit,'False') = @ProdAidDoc[/quote]The filter currenty returns 1 row as both @ProdFolderReq and @ProdAidDoc = 1I would like to return 1 record if either or both return 1 (i.e. drop the AND). I tried OR but this returns multiple records.Can this be done?Kind Regards,Phil.Tue, 13 Nov 2012 05:09:02 GMT2Tall