Simple Stored Procedure Question

[ease20022002]

Hi,

(the following stored procedure can be placed in anyones query window and run b.c it does not have any relation to any tables. Only functions in 2005)

What I am trying to do is create a stored procedure that will take the value of the GetDate() function (Current Date) and manipulate the date and return a value in Varchar(10).

The stored procedure is set up to give me the monday date regardless of what day it is in the week. I ALREADY have a function that does this for me, but I also NEED to have this in a stored procedure. The preceding sentence is non-negotiable .

Unfortunately I can't get the stored procedure to accept the GetDate() value. The object is to get this stored procedure called from an SSIS ETL package.

The following is the SP:

ALTER PROCEDURE [dbo].[spCognosFileName] 
-- Add the parameters for the stored procedure here
	@CurrentDate DateTime
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	Declare @ConvertedFormat DateTime

	Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)
		+ dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)

Return
		
	Select Convert(Varchar(10),@ConvertedFormat,112) + '.txt'
	
END

Any help would be appreciated.
Thanks

 

[gmmastros]

There's likely to be several ways to solve this problem. I think the problem is the way in which you call the stored procedure. More specifically, the way you pass the parameter.

For example, if you open a query window and run this:

[tt][blue]Exec spCognosFileName GetDate()[/blue][/tt]

You will get an error:

[tt][red]Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.[/red][/tt]

But, if you create a variable first and pass that, it seems to work properly. Ex:

Declare @Now DateTime
Set @Now = GetDate()

exec [spCognosFileName] @Now

If you don't want to create a variable (I dunno, maybe this in non-negotiable too), then you could modify the stored slightly and still get this to work.

ALTER PROCEDURE [dbo].[spCognosFileName]
-- Add the parameters for the stored procedure here
    @CurrentDate DateTime
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    [!]If @CurrentDate Is NULL Set @CurrentDate = GetDate()[/!]
    
    Declare @ConvertedFormat DateTime

    Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)
        + dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)

    Select Convert(Varchar(10),@ConvertedFormat,112) + '.txt'
    
END

Then, if you want to use the current date, simply pass NULL in to the parameter.

exec [spCognosFileName] NULL

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

or why pass it in at all?

	ALTER PROCEDURE [dbo].[spCognosFileName]
	AS
	BEGIN    
	-- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    
	SET NOCOUNT ON;   
	Declare @CurrentDate DateTime   
	Set @CurrentDate = GetDate()        
	Declare @ConvertedFormat DateTime    
	Set @ConvertedFormat = dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate))-1900, 0)        + dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @CurrentDate),@CurrentDate)),0)    
	Select Convert(Varchar(10),@ConvertedFormat,112) + '.txt'    
	END

"NOTHING is more important in a database than integrity." ESquared

 

[PWise]

How About

Create Procedure spCognosFileName


As

Select convert(varchar(10),Dateadd(d,- Datepart(dw,getdate()),Getdate() +2 ),112)+ '.txt'

Return