InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 18:16:55 GMT20http://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxSean is correct....--Create your typeCREATE TYPE TCType AS TABLE(TranCode varchar(20))--Create some sample data CREATE TABLE Test (TranCode varchar(20))INSERT INTO Test (TranCode) VALUES ('BUY')INSERT INTO Test (TranCode) VALUES ('SELL')INSERT INTO Test (TranCode) VALUES ('CONSTRAIN')INSERT INTO Test (TranCode) VALUES ('HOLD')INSERT INTO Test (TranCode) VALUES ('ON ACCOUNT')--Create your procedureCREATE PROCEDURE TestProc @TCType TCType READONLYASSELECT *FROM Test T INNER JOIN @TCType TC ON T.TranCode = TC.TranCodeGO--Decalre your variable populate it and pass it to the procedure DECLARE @TranCodes AS TCTypeINSERT INTO @TranCodes SELECT 'BUY' UNION SELECT 'HOLD'EXECUTE TestProc @TranCodesFri, 08 Mar 2013 14:11:04 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspx[quote][b]Chrissy321 (3/8/2013)[/b][hr]There are two approaches to this problem that I am aware of.You procedure is passed one string parameter like 'BUY','SELL','CONTRIBUTION' and then the string is split within the procedure into its individual components. You use a table valued parameter(TVP). Think of this as passing one parameter to the procedure but that parameter is a table containing multiple records. You take this TVP and insert into a temp table. Then you join your results to the temp table.This thread has some sample code I wrote that you may be able to adapt.http://www.sqlservercentral.com/Forums/Topic1382201-392-1.aspx#bm1382230Or search other articles on TVPs.[/quote]The TVP approach would work to. However you do not need to insert into a temp table. If you pass a TVP you can just join directly to it like you would any other table.Fri, 08 Mar 2013 13:32:01 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxThere are two approaches to this problem that I am aware of.You procedure is passed one string parameter like 'BUY','SELL','CONTRIBUTION' and then the string is split within the procedure into its individual components. You use a table valued parameter(TVP). Think of this as passing one parameter to the procedure but that parameter is a table containing multiple records. You take this TVP and insert into a temp table. Then you join your results to the temp table.This thread has some sample code I wrote that you may be able to adapt.http://www.sqlservercentral.com/Forums/Topic1382201-392-1.aspx#bm1382230Or search other articles on TVPs.Fri, 08 Mar 2013 13:17:41 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxMultiple values in a single parameter don't quite work like that. It is kind of a pain to deal with. However, there is really slick way to parse the value of your parameter into a table. In fact, it is quite useful anytime you need to parse a delimited string. Since you didn't post any ddl or sample data I created some based on some other data that I happened to have. This example uses name and State but the concept is much the same. This should do a decent job of demonstrating how this can work.[code]if object_id('tempdb..#Names') is not null drop table #Namescreate table #Names( FName varchar(25), ST char(2))insert #Namesselect 'Francisco', 'PA' union allselect 'Samuel', 'PA' union allselect 'Georgia', 'WA' union allselect 'James', 'MO' union allselect 'Martha', 'MO' union allselect 'Connie', 'PA' union allselect 'Carol', 'PA' union allselect 'Billy', 'WA' union allselect 'Ruby', 'WA' union allselect 'Steve', 'LA'--Now we have our example data so we can begin looking the actual problemdeclare @TC varchar(100) = 'PA,MO'select * from #Names ninner join dbo.DelimitedSplit8K(@TC, ',') s on n.ST = s.Item--now we only sent in a single valueset @TC = 'PA'select * from #Names njoin dbo.DelimitedSplit8K(@TC, ',') s on n.ST = s.Item[/code]Now this is NOT going to work on your system without a little effort on your part first. You can find the code for the DelimitedSplit8K function by following the link in my signature about splitting strings.Fri, 08 Mar 2013 13:10:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxSo I'm able to create three separate query's to pull BUY SELL and CONTRIBUTION from the string, but now that I've written these queries, how is it that they prove beneficial?Fri, 08 Mar 2013 11:51:20 GMTmeadow0http://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxI'm not sure -- just started using SQL about a week ago. I will research and see if that will work.Fri, 08 Mar 2013 10:40:20 GMTmeadow0http://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxCan you just look for TransCode being a substring of @TC?Fri, 08 Mar 2013 10:31:43 GMTRichard Warrhttp://www.sqlservercentral.com/Forums/Topic1428686-1292-1.aspxHello all,I've looked online and have found numerous articles pertaining to my issue here, I'm not exactly sure that it correlates perfectly, however.The below query is functional and works properly, however I want to be able to enter 'BUY,SELL' for @TC and have it output the data for both BUY and SELL with regards to column 'TransCode'when @TC = BUY it will spit out the proper data when TransCode = Buy when @TC = SELL it will also spit out the proper data when TransCode = SELLHowever, I'm looking for the ability to combine both 'BUY,SELL' for @TC and have it return both BUY and SELL.Essentially I'm looking for the ability to eventually have my WHERE clause read:WHERE @TC in ('BUY','SELL','CONTRIBUTION') and have it return data when multiples are selected There are many other variables in the TransCode field other than BUY and SELL (HOLD, CONTRIBUTION, ON ACCOUNT etc..) so bringing back the entire column is not an option... any ideas?declare @idnum varcharselect @idnum = 1declare @TC varchar(50)select @TC = 'BUY'IF @TC = 'ALL'BEGINSELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = 1ORDER BY SecIDEND ELSESELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioIDFROM ERTutTransactions CINNER JOIN FinalGroupDetail BON C.PortfolioID = B.PortfolioIDAND B.GroupId = 1WHERE TransCode = @TCORDER BY SecIDENDFri, 08 Mar 2013 10:15:31 GMTmeadow0