User Prompts????

  • Hi guys

    I have been asked to develop a query which when run will prompt the user to enter the number of months before running the query.

    I know we can do this in MS Acess .

    can we do the similar sort of thing in SQL as  well??

    My query is

    SELECT

    DHBMappingTable.[DHB Name], Agency.DHB_service, PurchaseUnitMappingTable.PU, PurchaseUnitMappingTable.PU_name,

    SUM([NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, AVG(wies2.[0607_cwd_WIES11]) AS Expr2,

    AdmissionMappingTable

    .Admission, wies2.Admission_type

    FROM

    DomicileCodes

     

    In the above query, I need to multiply "0607_cwd_WIES11" with the no. of months

     

    Lets say if the user is being prompted to enter the no of months and he enters 3, then "0607_cwd_WIES11" should be multiplied by 3 and divided by 12.

    Do i have to use stored procedures(which I have got no knowledge of)

    What do I have to do??

    Pls help

     

    Thanks

    Mita

  • Can't do it in SQL Server. MS Access is both a front-end (application) and a back-end (database). SQL Server is only a back-end.

    Create a stored procedure on SQL Server that requires parameters. Then have your front-end (MS Access, Crystal Reports, whatever) execute the stored procedure with the user's inputs.

    -SQLBill

  • hi thanks for answering

    But what are parameter queries for in SQL?

  • You would create a stored procedure:

    CREATE PROCEDURE spGetClientAddress

    @FirstName VARCHAR(20),

    @LastName VARCHAR(50)

    AS

    SELECT Address,

    City,

    State,

    Zip,

    Phone

    FROM mytable

    WHERE ClientFName = @FirstName

    AND ClientLName = @LastName

    GO

    You would run that on SQL Server, it would create the procedure. You would then run the procedure:

    EXEC spGetClientAddress @FirstName = 'Jane', @LastName = 'Smith'

    You then can set up a front-end to ask for the firstname and lastname. Then add it to the procedure and 'submit' it to SQL Server.

    But the front-end client needs to have a login account on SQL Server to execute the procedure.

    -SQLBill

  • Hi thanks for your answer. I was just going through books online and found out that in these situations, we can also use parameter queries.

    Can I define this parameter in business intelligence because after running this query, I develop the final report using business intelligence only

  • Obviously, you can..

  • Could you help me in that??

    what should I write in the report parameter window??

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply