Question SQL Job Exec with automated date

[robbydogg]

Hello,

I am trying to set up a job which executes some stored procedures with preset variables.

I am trying to get the preset variables to show last month's month number and current year, but when going into jan next year, for it to be december of the previous year.
below is the code i have used but it doesn't seem to work - can anyone help?

the code below, shoudl set the variables @PRD to 8 and @Year to 2010 and then 'send them into' the stored procedure called ASP.

declare @PRD int  
select case 
	when month(getdate()) = '1' then '12'
    else month(getdate())
end

declare @Year int  		 
select case 
	when month(getdate()) = '1' then year(getdate())-1
    else year(getdate())
end 

exec asp
@PRD = @PRD, @YEAR=@YEAR

thanks in advance

 

[kulrom]

Try this (i avoided SELECT CASE for better reading)

	DECLARE @Month int;
	IF month(GetDate()) = 1
		BEGIN 
			SET @Month = 12
		END
	ELSE
		BEGIN
			SET @Month = month(GetDate())
		END
		
	DECLARE @Year int;
	
	IF month(GetDate()) = 1
		BEGIN 
			SET @Year = year(GetDate())-1
		END
	ELSE
		BEGIN
			SET @Year = year(GetDate())
		END
	
PRINT @Year;
PRINT @Month;

EXEC Asp @Month, @Year

 

[robbydogg]

i think i solved it - because i'm a fool who forgot a line:

declare @PRD int  
select @PRD =
case 
	when month(getdate()) = '1' then '12'
    else month(getdate())-1
end

declare @Year int  		 
select @YEAR = case 
	when month(getdate()) = '1' then year(getdate())-1
    else year(getdate())
end 

exec asp
@PRD = @PRD, @YEAR=@YEAR

 

[robbydogg]

Thanks for the help Kulrom

 

[kulrom]

Unless you REALLY select a data from DB you can use the SET @local_variable (Transact-SQL)