prompt user for values with t-sql

Question

Post reply

prompt user for values with t-sql

icampbell

Right there with Babe

Points: 770
More actions
March 12, 2010 at 4:33 am

#143519

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

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Convert it to a stored procedure and pass the required parameters through it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 2

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

Paul White SSC Guru Points: 150467 More actions · Answer 3

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

icampbell Right there with Babe Points: 770 More actions · Answer 4

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

Paul White SSC Guru Points: 150467 More actions · Answer 5

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 6

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