November 3, 2010 at 10:35 am
Does anyone know of a way so that when a stored proceedure is executed the user is asked to enter a parameter that is used in the underlying script.
Similar to using the Like [enter birth date ] in MS Access
for example the below would return rows with a date of birth enetered by user
select ID, dateofbirth
from table
where dateof birth = '[enter date of birth]'
Many thanks in advance
November 3, 2010 at 11:23 am
They're called "Input Parameters". You could have one like this:
create procedure dbo.IDsByDOB
(@DateOfBirth_in datetime)
as
select ID, DateOfBirth
from dbo.MyTable
where DateOfBirth = @DateOfBirth_in;
Then, of course, the application has to be built to run that proc, and that's where they'd actually enter the data. If you right-click a stored procedure and choose "Script as" then "Execute", it will list the parameters for you, and you can fill in the values.
There's more to it, but if you look up "T-SQL input parameters" online, you'll get what you need.
As a side note, it's often a bad idea to compare dates using an equality statement. Quite often, dates in SQL Server have times stored with them, and when they do, you need to compare a range of values, usually using greater-than-or-equal-to midnight of the selected date, and less than midnight of the next date. (Days start with midnight in SQL, instead of ending with it.) Make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 6:24 am
CELKO (11/3/2010)
No. SQL is not an application language; it is a data retrieval and management language.What you want is to return to the old days when data (i.e. files) and code were in a monolithic program. Your front end can ask the user to provide arguments for a procedure call. Please read a book on tiered architectures.
Okay, Joe, I'll bite. How is that different than what I already wrote, which says, "Then, of course, the application has to be built to run that proc, and that's where they'd actually enter the data."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply