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.
| Author |
Topic |
|
johnstern
Yak Posting Veteran
67 Posts |
 Posted - 2007-04-26 : 19:45:22
|
| This SP has one variable that gets a comma separated string '1,2,4,5'this SP needs to get all the records with Ids that match each of those numbers I am currently doing it this way, but I would like to get some opinions regarding the quality and efficiency of this approach, specially the fact that I just make a join at the end with the temp table. Create PROCEDURE dbo.Glossary_Fetch_Search_List @ListofKeys varCHAR(200)ASSET NOCOUNT ON--parse comma separated varaible and put results in variable tableDeclare @Tbl_ParsedTerms TABLE ( TermID VARCHAR(200) ) DECLARE @intPos INT, @SubStr VARCHAR(200) -- Remove All Spaces SET @ListofKeys = REPLACE(@ListofKeys, ' ','') -- Find The First Comma SET @IntPos = CHARINDEX(',', @ListofKeys) -- Loop Until There Is Nothing Left Of @ListofKeys WHILE @IntPos > 0 BEGIN -- Extract The String SET @SubStr = SUBSTRING(@ListofKeys, 0, @IntPos) -- Insert The String Into The Table INSERT INTO @Tbl_ParsedTerms (TermID) VALUES (@SubStr) -- Remove The String & Comma Separator From The Original SET @ListofKeys = SUBSTRING(@ListofKeys, LEN(@SubStr) + 2, LEN(@ListofKeys) - LEN(@SubStr) + 1) -- Get The New Index To The String SET @IntPos = CHARINDEX(',', @ListofKeys) END -- Return The Last One INSERT INTO @Tbl_ParsedTerms (TermID) VALUES (@ListofKeys)---retrieve corresponding terms SELECT Glossary.TermID, Term, Definition, FROM Glossary JOIN @Tbl_ParsedTerms as tempP on Glossary.TermID = tempP.TermID |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
kasabb
Starting Member
8 Posts |
Posted - 2007-04-27 : 09:10:21
|
| This should work, however, I cannot get it to work if I have set @val = ','5A','5B',' (other than splitting the values into a table variable).declare @val nvarchar(15)set @val = ',1,2,3,4,5,'select * from table where charindex(',' + field + ',', @val) > 0 |
 |
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
SP getting a Comma separated var, better way ?