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