| Author |
 Topic  |
|
|
meadow0
Starting Member
USA
6 Posts |
 Posted - 03/08/2013 : 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 varchar
select @idnum = 1
declare @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 SecID
END
ELSE
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
WHERE TransCode = @TC
END
Currently when I run this screen, I will return no results for obvious reasons. Any idea on what I can do? |
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 03/08/2013 : 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
USA
6 Posts |
Posted - 03/08/2013 : 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
India
47097 Posts |
Posted - 03/09/2013 : 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,
etc
so wherever value is one of that included in TC it will match
say for ex: ,BUY, as it finds pattern inside your above comma separated string.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
meadow0
Starting Member
USA
6 Posts |
Posted - 03/11/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 03/11/2013 : 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 |
Edited by - James K on 03/11/2013 11:30:42 |
|
|
| |
Topic |
|
|
|
Using multiple variables within a parameter? Noob!
