locked
Adding getdate value to stored procedure variable default value RRS feed

  • Question

  • Hi,

    i have a stored procedure, where i need to add file path as variable. Also the exported doc will be saved saved to that file path with the name given in the stored procedure. 

    Here is the declaration i am doing in the SP,

    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'

    Here I am getting error highlighted at the '+' symbol.

    Please let me know how could i add the current date value to the file name being as variable ?

    Thanks!


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    Monday, November 26, 2012 7:14 AM

Answers

  • I guess ur script must be some thin like this

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'
    )
    As 
    begin
    --------------
    end

    Why don't u assign the value for variable @FileName inside the procedure instaed of doing during parameter declareation. some thing as below 

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) 
    )
    As 
    begin

    Set @Filename  = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'

    --------------
    end


    Please have look on the comment

    • Proposed as answer by Santhosh H Monday, November 26, 2012 10:03 AM
    • Marked as answer by Radhai Monday, November 26, 2012 10:29 AM
    Monday, November 26, 2012 7:37 AM
  • You cannot have "/" in the file name, use instead "_":

    DECLARE @FilePath varchar(100) = N'C:\Input\';
    DECLARE @Filename varchar(100) = @FilePath + N'TestingXML_' + 
             replace(convert(varchar(20),Getdate(),111),'/','_')+'.xml';
    
    PRINT @Filename;
    -- C:\Input\TestingXML_2012_11_26.xml

    Related blogs:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    http://www.sqlusa.com/bestpractices2005/renamebackupfile/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Monday, November 26, 2012 7:49 AM
    Monday, November 26, 2012 7:46 AM

All replies

  • A small change,  I guess , is to declare both the variables separately instead of declaring them as comma separated.


    DECLARE @FilePath varchar(100) = N'C:\Input\' 
    DECLARE @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'


    Thanks and regards, Rishabh K

    Monday, November 26, 2012 7:19 AM
  • I guess ur script must be some thin like this

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'
    )
    As 
    begin
    --------------
    end

    Why don't u assign the value for variable @FileName inside the procedure instaed of doing during parameter declareation. some thing as below 

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) 
    )
    As 
    begin

    Set @Filename  = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'

    --------------
    end


    Please have look on the comment

    • Proposed as answer by Santhosh H Monday, November 26, 2012 10:03 AM
    • Marked as answer by Radhai Monday, November 26, 2012 10:29 AM
    Monday, November 26, 2012 7:37 AM
  • You cannot have "/" in the file name, use instead "_":

    DECLARE @FilePath varchar(100) = N'C:\Input\';
    DECLARE @Filename varchar(100) = @FilePath + N'TestingXML_' + 
             replace(convert(varchar(20),Getdate(),111),'/','_')+'.xml';
    
    PRINT @Filename;
    -- C:\Input\TestingXML_2012_11_26.xml

    Related blogs:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    http://www.sqlusa.com/bestpractices2005/renamebackupfile/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Monday, November 26, 2012 7:49 AM
    Monday, November 26, 2012 7:46 AM
  • You cannot add a default getdate() value as a parameter to the stored procedure.

    Create a local variable and assign  GETDATE() value to the variable.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, November 26, 2012 7:46 AM
    Answerer
  • You cannot have "/" in the file name, use instead "_":

    DECLARE @FilePath varchar(100) = N'C:\Input\';
    DECLARE @Filename varchar(100) = @FilePath + N'TestingXML_' + 
             replace(convert(varchar(20),Getdate(),111),'/','_')+'.xml';
    
    PRINT @Filename;
    -- C:\Input\TestingXML_2012_11_26.xml

    Related blogs:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    http://www.sqlusa.com/bestpractices2005/renamebackupfile/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thanks Kalman! it worked !

    Not able to mark answer 'Unexpected Error'


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------


    • Edited by Radhai Monday, November 26, 2012 8:59 AM Not able to mark answer 'Unexpected Error'
    Monday, November 26, 2012 8:58 AM
  • I guess ur script must be some thin like this

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'
    )
    As 
    begin
    --------------
    end

    Why don't u assign the value for variable @FileName inside the procedure instaed of doing during parameter declareation. some thing as below 

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) 
    )
    As 
    begin

    Set @Filename  = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'

    --------------
    end


    Please have look on the comment

    Thanks for your point, now i modified mine as you said..

    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    Monday, November 26, 2012 9:00 AM
  • I guess ur script must be some thin like this

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'
    )
    As 
    begin
    --------------
    end

    Why don't u assign the value for variable @FileName inside the procedure instaed of doing during parameter declareation. some thing as below 

    Create Proc sp
    (
    @FilePath varchar(100) = N'C:\Input\' ,
    @Filename varchar(100) 
    )
    As 
    begin

    Set @Filename  = @FilePath + N'TestingXML_' + convert(varchar(20),Getdate(),101)+'.xml'

    --------------
    end


    Please have look on the comment

    Thanks for your point, now i modified mine as you said..

    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    Please mark it as answer if it has worked.

    Please have look on the comment

    Monday, November 26, 2012 9:27 AM