SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using multiple variables within a parameter? Noob!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meadow0
Starting Member

USA
6 Posts

Posted - 03/08/2013 :  14:43:18  Show Profile  Reply with Quote
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

3761 Posts

Posted - 03/08/2013 :  15:55:02  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 03/08/2013 :  15:59:28  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/09/2013 :  00:37:46  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 03/11/2013 :  09:06:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 03/11/2013 :  11:30:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000