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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using multiple variables within a parameter? Noob!

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 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
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+',%'

Go to Top of Page

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!
Go to Top of Page

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,
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/

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -