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 |
meadow0
Starting Member
6 Posts |
Posted - 2013-03-08 : 14:43:18
|
Hi Guys,I'm a new SQL user... about 5 days in. I've come across an issue when attempting to write a query. I'm looking to define a parameter (@TC) as either a BUY or SELL or CONTRIBUTION (all of these values can be found in column TransCode)I have a functioning query that allows me to set @TC to either BUY, SELL, or CONTRIBUTION, but not a combination of both.I'm hoping that I can define @TC = (BUY,SELL) and have it return both BUY's and SELL's...The query is as follows:declare @idnum varcharselect @idnum = 1declare @TC varchar(50)select @TC = 'BUY,SELL,CONTRIBUTION'IF @TC = 'ALL'BEGIN SELECT B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID FROM ERTutTransactions C INNER JOIN FinalGroupDetail B ON C.PortfolioID = B.PortfolioID AND B.GroupId = @idnum ORDER BY SecIDEND ELSESELECT B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID FROM ERTutTransactions C INNER JOIN FinalGroupDetail B ON C.PortfolioID = B.PortfolioID AND B.GroupId = @idnum WHERE TransCode = @TCENDCurrently when I run this screen, I will return no results for obvious reasons. Any idea on what I can do? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-08 : 15:55:02
|
T-SQL syntax, unfortunately, does not allow for multiple values used in that context. What you can do is to use the LIKE clause like shown below:...WHERE ','+REPLACE(@TC,' ','')+',' LIKE '%,'+TransCode+',%' |
|
|
meadow0
Starting Member
6 Posts |
Posted - 2013-03-08 : 15:59:28
|
Hi James,This is working! Thanks for the help... can you just kind of give me a general idea of what exactly it is doing? I'd like to be able to conceptually understand it as well. Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-09 : 00:37:46
|
quote: Originally posted by meadow0 Hi James,This is working! Thanks for the help... can you just kind of give me a general idea of what exactly it is doing? I'd like to be able to conceptually understand it as well. Thanks!
It will convert the parameter @TC into a comma separated format like,BUY,SELL,CONTRIBUTION,then it will compare it to each of fields in your table after enclosing them with ,ie ,Value1,,value2,etcso wherever value is one of that included in TC it will matchsay for ex: ,BUY, as it finds pattern inside your above comma separated string.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
meadow0
Starting Member
6 Posts |
Posted - 2013-03-11 : 09:06:22
|
This has all be extremely helpful, however, what exactly are the + signs doing?I can't seem to find anything about them online. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-11 : 11:30:00
|
The plus sign when used with strings does string concatenation. So, if @TC is "BUY,SELL", then ','+@TC+',' yields ",BUY,SELL,". Similarly, '%,'+TransCode+',%' would yield "%,SELL,%" if TransCode were "SELL"Plus when used with numeric data types (int, float etc.) would perform arithmetic addition.http://msdn.microsoft.com/en-us/library/ms190301.aspx |
|
|
|
|
|
|
|