Using variables in an IF statement?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server Newbies » Using variables in an IF statement?

Add to briefcase

11 posts, Page 1 of 2

1 2 »»

Using variables in an IF statement?

Expand / Collapse

Author

Message

Posted Thursday, March 07, 2013 8:00 AM

Grasshopper

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 07, 2013 8:28 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

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 07, 2013 10:51 AM

Grasshopper

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 07, 2013 11:06 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

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 07, 2013 11:16 AM

Grasshopper

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 07, 2013 11:25 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

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 07, 2013 11:28 AM

Grasshopper

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 07, 2013 11:29 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

That clarified matters. Standby...

Post #1428165

Posted Thursday, March 07, 2013 11:43 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

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 07, 2013 11:51 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 552, Visits: 2,993

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

11 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse