Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using variables in an IF statement? Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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
Post #1428002
Posted Thursday, March 7, 2013 8:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
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.
Post #1428020
Posted Thursday, March 7, 2013 10:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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
Post #1428139
Posted Thursday, March 7, 2013 11:06 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
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/
Post #1428150
Posted Thursday, March 7, 2013 11:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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
Post #1428158
Posted Thursday, March 7, 2013 11:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
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)
Post #1428162
Posted Thursday, March 7, 2013 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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)
Post #1428164
Posted Thursday, March 7, 2013 11:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
That clarified matters. Standby...
Post #1428165
Posted Thursday, March 7, 2013 11:43 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
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
Post #1428174
Posted Thursday, March 7, 2013 11:51 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 647, Visits: 3,792
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
Post #1428176
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse