SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using variables in an IF statement?


Using variables in an IF statement?

Author
Message
meadow0
meadow0
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 48
Hi Guys!

I'm attempting to write a query that is using two variables, one will specify an ID to pull back, and that seems to be working fine...

The second variable allows the user to either specify a column TransType: 'BUY' 'SELL' or 'ALL'

Obviously if the user specifies 'BUY' I'd like only the 'BUY' from that column to be returned and vice versa...

I've attempted to do this by creating temp tables to solve this query... it however is not functional, any suggestions?


Begin

declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'BUY'

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
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
END
END
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
What do you mean by not functional?

I am not sure you need the temp tables. Does this help?

IF (@TC = 'BUY')
BEGIN
UPDATE....WHERE A.TransType = 'BUY'
END

IF (@TC = 'SELL')
BEGIN
UPDATE....WHERE A.TransType = 'SELL'
END

IF (@TC = 'ALL')
BEGIN
UPDATE....WHERE A.TransType IN ('BUY','SELL')
END

Before you start updating data please test with SELECT statements first to validate.
meadow0
meadow0
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 48
That query was not functioning properly.

The below query is actually pulling back the proper data. However, when I use 'ALL' as my variable, it is running the query twice (the way I have my IF statement setup) any ideas on how to beat this?

Begin

declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'BUY'

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
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
END
END
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
What are the possible values for field transcode?

1) Buy or sell; or
2) Buy, Sell or All

Is 'All' actually a transcode or does all indicate that you want both buys and sells.

Can you can post your question according to these guidelines?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
meadow0
meadow0
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 48
Chrissy,

I took a look at the guidelines, and I can't seem to figure out exactly how to post the data the way they've shown. I've done my best to make it presentable and easy to read. To answer your question; 'ALL' is not a field within TransCode, there are several different data including 'BUY' and 'SELL'. When I use 'ALL' as a variable, I would just like the column to return everything back. (i.e - BUY, SELL, HOLD, CONSTRAIN, ON ACCOUNT, etc.)
BEGIN

DECLARE @idnum varchar
SELECT @idnum = 1


DECLARE @TC varchar(50)
SELECT @TC = 'BUY'

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
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
END



[EDIT] P.S - None of my formatting is actually showing up, my apologies
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
Is the purpose of the @TC parameter to:

1) Control the number of records returned, or
2) Control the appearance of the data returned (the number of records returned will always be the same no matter what the parameter value is)
meadow0
meadow0
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 48
The parameter is to limit the data.

If 'BUY' is entered for the parameter, only buys would be returned

If 'SELL' is entered, only sells would be returned

however, if you enter 'ALL' a combination of BUY, SELL, CONSTRAIN, HOLD, ON ACCOUNT, etc. would be returned (thus being the entire column)
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
That clarified matters. Standby...
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
Below is the simplest way.

There are alternative ways where you don't have to repeat the SELECT statement. I would go tend to use the simpler way unless the SELECT statement is highly complicated and I don't want to repeat it.


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')

DECLARE @TC varchar(50)
--SELECT @TC = 'BUY'
SELECT @TC = 'ALL'

IF @TC = 'BUY' OR @TC = 'SELL'
BEGIN
SELECT
*
FROM #Test
WHERE
TranCode = @TC
END
ELSE
BEGIN
SELECT
*
FROM #Test

END


DROP TABLE #Test
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3022 Visits: 4793
Alternate way below. If you can confirm that we have clarified the problem and that the solution works maybe one of the more experienced posters can speak to best practices in dealing with this particular issue...


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')

DECLARE @TC varchar(50)

--SELECT @TC = 'BUY'
--SELECT @TC = 'SELL'
SELECT @TC = 'ALL'

IF @TC = 'ALL' SET @TC = NULL

SELECT
*
FROM #Test
WHERE
TranCode = @TC OR @TC IS NULL

DROP TABLE #test
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search