locked
Providing Parameters to a SQL Query RRS feed

  • Question

  • OK, another question for the wizened sages out there;

    I need to take a date that is input from the keyboard, set that as the end date, subtract 24 months from it and set that as the start date.

    Then, use these two dates in a WHERE to ensure only those records in their range are grabbed by the SELECT.

    I was told that SQL Management Studio could handle keyboard inputs, but the explanation was not the clearest in the world. Hopefully, someone here will be able to make it a bit more understandable.

    Thanx!

    Wednesday, December 21, 2011 9:18 PM

Answers

  • You are expecting an input box from SSMS for entering your preferred date.

    No, this is not possible. You will need to hardcode the date or can create a parametrized SP.

     

    SSMS is not a front end application, you will need to create a separate GUI app for this.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Proposed as answer by Brian TkatchEditor Tuesday, December 27, 2011 2:51 PM
    • Marked as answer by Adam Quark Tuesday, December 27, 2011 5:44 PM
    Friday, December 23, 2011 9:01 AM

All replies

  • DECLARE @dt DATETIME
    SET @dt='20110101'
    SELECT * FROM tbl WHERE dt>=DATEADD(m,DATEDIFF(m,0,@dt)-24,0)
    AND dt<DATEADD(d,1,@dt)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 22, 2011 8:18 AM
  • Thank you, Uri, that's a big help.

    But, it doesn't prompt the user to input the baseline date - @dt - from the keyboard. Is that possible?

    Thursday, December 22, 2011 4:59 PM
  • You are expecting an input box from SSMS for entering your preferred date.

    No, this is not possible. You will need to hardcode the date or can create a parametrized SP.

     

    SSMS is not a front end application, you will need to create a separate GUI app for this.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Proposed as answer by Brian TkatchEditor Tuesday, December 27, 2011 2:51 PM
    • Marked as answer by Adam Quark Tuesday, December 27, 2011 5:44 PM
    Friday, December 23, 2011 9:01 AM
  • Hi

    Try to use template in this case the user  must specify parameters for SSMS but you must learn the user how to use it.

     

     

     

    If this is helpful please mark it as answered

     

    Elmozamil Elamir

     

    MyBlog

    Monday, December 26, 2011 2:03 PM
  • You are expecting an input box from SSMS for entering your preferred date.

    That is correct.

    No, this is not possible. You will need to hardcode the date or can create a parametrized SP.

     

    SSMS is not a front end application, you will need to create a separate GUI app for this.

    That's what I thought, but was told otherwise. Thanx, manoj. Now I can stop beating my head against the wall...
    Tuesday, December 27, 2011 5:50 PM
  • SQL Management studio is definitely not the optimal tool to achieve what you are trying here. I recommend using SQLCMD. The solution involves creating a .sql file, a .cmd find and then calling the .cmd file through the command prompt by passing in the date as input from the keyboard. I have over simplified the query for demonstration purposes, but you can extend the same concept to suit your needs;

    1. Create a test.sql file with the following TSQL code - focus on the $(inputdate) here;

    declare

    @inputdate datetime

    ;

    set

    @inputdate = '$(inputdate)'

    ;

    select

    * from dbo.t1 where c > @inputdate

    ;

    GO

    2. create a test.cmd file with the following code - This code calls the sqlcmd utility, note the use of Variables being passed to SQLCMD

    rem %1 database Server
    rem %2 database Name
    rem %3 unser input date

    SQLCMD -S %1 -d %2 -E -I -i "test.sql" -v inputdate="%3"

    3. keep the test.sql and test.cmd files in the same folder and navigate to the path using command prompt and run the following on command line;

    test.cmd chadbadev015\sql2008_CIS test 2011-12-26

    A detail description of the SQLCMD utility and its options/usages can be found here:

    http://msdn.microsoft.com/en-us/library/ms162773.aspx

    Please see my screenshot of a test run of the solution I described above;

    • Proposed as answer by Sanil Mhatre Tuesday, December 27, 2011 8:05 PM
    Tuesday, December 27, 2011 6:41 PM