Passing a Variable in SQL statements

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

SQL Server 2005 Forums

Transact-SQL (2005)

Passing a Variable in SQL statements

Author

Topic

ravishwor
Starting Member

20 Posts

Posted - 2008-08-04 : 01:30:25

Hi All,
I want to build a dynamic SP/SQL that will get the Current Year & Month and use it as the Variable in the SQL statements.

This is my code so far. It fails to work.
What i want to achieve is to be able to Pass the Current Month & Year as a Variable in the SQL statements. Note that my Table Column name is 'Jan' when the @Mth=1 and so on till when @Mth=12 it is Dec.

Set DateFormat dmy
Declare @Mth as char(4)
Declare @Year as int
Declare @MyString varchar(max)
Set @Mth = DATEPART(MM, getdate())
IF @Mth = '12'
begin
set @Year = Year(getdate())
End
ELSE
begin
set @Year = Year(getdate())-1
End

IF @Mth = '1' Begin
SELECT SUM(Jan) AS MthBgt, 'Perth' as Location FROM dbo.Rep_Budget_Perth where (Year([Year Start]) = @year) GROUP BY Year([Year Start])
union all
SELECT SUM(Jan) AS MthBgt, 'Darwin' as Location FROM dbo.Rep_Budget_Darwin where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Adelaide' as Location FROM dbo.Rep_Budget_Adelaide where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Brisbane' as Location FROM dbo.Rep_Budget_Brisbane where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Export' as Location FROM dbo.Rep_Budget_Export where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'ICC' as Location FROM dbo.Rep_Budget_Inter_Company where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Hobart' as Location FROM dbo.Rep_Budget_Launceston where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Majors' as Location FROM dbo.Rep_Budget_Major_Account where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(@Mth) AS MthBgt, 'Melbourne' as Location FROM dbo.Rep_Budget_Melbourne where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
SELECT SUM(Jan) AS MthBgt, 'Sydney' as Location FROM dbo.Rep_Budget_Sydney where (Year([Year Start]) = @year) GROUP BY [Year Start]
union all
select Jan as MthBgt, State as Location FROM State_Sales_Budget where (Year([Year Start]) = @year)
ORDER BY Location;
End
Else
begin
print @Year
end

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-04 : 02:49:26

The data type of your @Mth may be the problem. Try using int & removing the quotes. from @Mth ='1'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 05:54:04

Frist of all, 2 bad design.
1. Should not keep the budget on a 12 mth column
2. Should not have a table for each of the location

Change the design if you can

take a look at UNPIVOT, use it to unpivot the budget. You might also want to build a view to combined all of the tables into one view.

If you have a normalized design, the query can be just simply


select MthBdg, Location
from   Rep_Budget
where  Year([Year Start]) = @Year
and    Mth                = @Mth

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources