November 13, 2006 at 1:47 pm
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
November 13, 2006 at 2:23 pm
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
November 13, 2006 at 2:49 pm
hi thanks for answering
But what are parameter queries for in SQL?
November 13, 2006 at 4:09 pm
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
November 13, 2006 at 5:37 pm
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
November 14, 2006 at 12:30 am
Obviously, you can..
November 14, 2006 at 12:08 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy