Set Variable - SQL Server Forums

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

Set Variable

Author

Topic

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 15:02:43

I would like to set a variable at the top of my code somewhere but I'm not sure how to do it. See code in red below. I would like the variable to be the month # and this query to run based on that variable.

SELECT H.MTH,
       H.VENDOR#,
       H.VENDOR_NAME,
       H.CATEGORY,
       CASE WHEN P.ETA_HIT_YES IS NULL THEN 0 ELSE P.ETA_HIT_YES END ETA_YES,
       CASE WHEN P.TOTAL_ETA_OPPS IS NULL THEN 0 ELSE P.TOTAL_ETA_OPPS END TOTAL_ETA_OPS
FROM COLBI.EDISP_TOWERS_WITH_CAT H
LEFT OUTER JOIN SC_9_ETA_TARG_MTH P 
ON H.VENDOR# = P.VENDOR#
AND P.MON = CAST(H.MTH AS INT)
AND P.CATEGORY = H.CATEGORY
WHERE (CAST(H.MTH AS INT)<= 4)ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:08:25

[code]
DECLARE @MonthNo int

SET @MonthNo = 4

SELECT H.MTH,
H.VENDOR#,
H.VENDOR_NAME,
H.CATEGORY,
CASE WHEN P.ETA_HIT_YES IS NULL THEN 0 ELSE P.ETA_HIT_YES END ETA_YES,
CASE WHEN P.TOTAL_ETA_OPPS IS NULL THEN 0 ELSE P.TOTAL_ETA_OPPS END TOTAL_ETA_OPS
FROM COLBI.EDISP_TOWERS_WITH_CAT H
LEFT OUTER JOIN SC_9_ETA_TARG_MTH P
ON H.VENDOR# = P.VENDOR#
AND P.MON = CAST(H.MTH AS INT)
AND P.CATEGORY = H.CATEGORY
WHERE (CAST(H.MTH AS INT)<= @MonthNo )ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;
[/code]

a much better approach would be to make it as a stored procedure with parameter added for passing values like

[code]
CREATE PROCEDURE GetVendorData
@MonthNo int
AS

SELECT H.MTH,
H.VENDOR#,
H.VENDOR_NAME,
H.CATEGORY,
CASE WHEN P.ETA_HIT_YES IS NULL THEN 0 ELSE P.ETA_HIT_YES END ETA_YES,
CASE WHEN P.TOTAL_ETA_OPPS IS NULL THEN 0 ELSE P.TOTAL_ETA_OPPS END TOTAL_ETA_OPS
FROM COLBI.EDISP_TOWERS_WITH_CAT H
LEFT OUTER JOIN SC_9_ETA_TARG_MTH P
ON H.VENDOR# = P.VENDOR#
AND P.MON = CAST(H.MTH AS INT)
AND P.CATEGORY = H.CATEGORY
WHERE (CAST(H.MTH AS INT)<= @MonthNo )ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;
GO

then call it as

EXEC GetVendorData @MonthNo = 4
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shilpash
Posting Yak Master

103 Posts

Posted - 2012-06-12 : 15:28:44

DECLARE @MonthNo INT
SELECT H.MTH
,H.VENDOR#
,H.VENDOR_NAME
,H.CATEGORY
,ISNULL(p.ETA_HIT_YES,0)
,ISNULL(p.TOTAL_ETA_OPS,0)
FROM COLBI.EDISP_TOWERS_WITH_CAT H
LEFT OUTER JOIN SC_9_ETA_TARG_MTH P
ON H.VENDOR# = P.VENDOR#
AND P.MON = CAST(H.MTH AS INT)
AND P.CATEGORY = H.CATEGORY
WHERE H.MTH = @MonthNo
ORDER BY H.VENDOR#
,H.MTH
,H.CATEGORY

also you dont have to use case statement in that condition,,u could use isnull

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:40:32

or even COALESCE()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 15:45:55

I tried using

DECLARE @MonthNo int
SET @MonthNo = 4

However, I get an error. ORA-06550 - Encountered the symbol "@" when expecting....

Help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:53:14

quote:
Originally posted by ertweety

I tried using

DECLARE @MonthNo int
SET @MonthNo = 4

However, I get an error. ORA-06550 - Encountered the symbol "@" when expecting....

Help!

So you're using Oracle
Then you're in wrong forum!
This is MS SQL Server forum and we deal with Transact sql which is sql server implementation of query language.
Please try your luck at oracle forums like www.orafaq.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 16:22:45

oh! merci

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources