March 12, 2010 at 4:33 am
Can i amend the following script to prompt a user for values when it is executed.
Declare @FROM_DATE datetime
Declare @TO_DATE datetime
Declare @ACAD_YEAR varchar(5)
Declare @COURSE varchar(10)
Declare @BLOCK varchar(1)
SELECT dbo.srs_sce.sce_ayrc, dbo.srs_sce.sce_crsc, dbo.srs_crs.crs_name, dbo.srs_sce.sce_blok, dbo.srs_sce.sce_occl, dbo.srs_sce.sce_scjc,
sab.sab_total, dbo.ins_stu.stu_surn, dbo.ins_stu.stu_fnm1, dbo.ins_stu.stu_fnm2, dbo.ins_stu.stu_fnm3
FROM dbo.srs_sce INNER JOIN
dbo.srs_crs ON dbo.srs_sce.sce_crsc = dbo.srs_crs.crs_code INNER JOIN
dbo.ins_stu ON dbo.srs_sce.sce_stuc = dbo.ins_stu.stu_code LEFT OUTER JOIN
(SELECT sab_stuc, COUNT(sab_stuc) AS sab_total
FROM dbo.srs_sab
WHERE (sab_begd >= @FROM_DATE) AND (sab_begd <= @TO_DATE)
GROUP BY sab_stuc) AS sab ON sab.sab_stuc = dbo.srs_sce.sce_stuc
WHERE (dbo.srs_sce.sce_ayrc = @ACAD_YEAR) AND (dbo.srs_sce.sce_crsc = @COURSE) AND (dbo.srs_sce.sce_blok = @BLOCK) AND
(sab.sab_total IS NOT NULL)
ORDER BY dbo.srs_sce.sce_blok, dbo.srs_sce.sce_occl, dbo.ins_stu.stu_surn, dbo.ins_stu.stu_fnm1, dbo.ins_stu.stu_fnm2, dbo.ins_stu.stu_fnm3:-P
March 12, 2010 at 4:50 am
Convert it to a stored procedure and pass the required parameters through it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 6:21 am
And when you make the query into a procedure as Paul says, make the parameters NOT NULL and the users will have to supply them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2010 at 9:06 am
Grant Fritchey (3/12/2010)
And when you make the query into a procedure as Paul says, make the parameters NOT NULL and the users will have to supply them.
Good point Grant - though I think you mean 'do not specify a default value'. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 9:18 am
Thanks guys i'll give that a try and let you know how i get on.
Oh! How i miss the ease of doing PL/SQL
March 12, 2010 at 9:23 am
icampbell (3/12/2010)
Oh! How i miss the ease of doing PL/SQL
Swings and roundabouts. Let's not go down that road eh?
BTW Excel can prompt for input :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 9:52 am
Paul White (3/12/2010)
Grant Fritchey (3/12/2010)
And when you make the query into a procedure as Paul says, make the parameters NOT NULL and the users will have to supply them.Good point Grant - though I think you mean 'do not specify a default value'. 🙂
Yeah, that's the much more correct way to phrase it, of course.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply