A function that will return user input

Question

A function that will return user input

joshphillips7145

Right there with Babe

Points: 720
More actions
August 13, 2012 at 9:07 am

#279466

I need to use a function to return records for all classes with an enrollment of more than a particular number input by a specified user. As well as write a script that will accept a users input and invoke the function.
I am really unsure where I should start here!!
The columns in my table are like so
Course_ID
Ref_Number
Faculty_ID
Term
Enrollment
TotRev

Viewing 10 posts - 1 through 9 (of 9 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Are you sure you want your users accessing the DB from SSMS? A much safer approach in your case might be to write a report which allows your users to input the enrollment number then run the report based on the parameter they gave.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323518 More actions · Answer 2

Well lets think it through Josh;

an end user is using some sort of search function, is that the idea?

do you need to search all the columns in your table, or just1?

if it's a search screen, would there be 6 "search" fields, one for each field?

for example, would i looke for 'COM' in the Course_Id of your table? (ie

DECLARE @SearchParam varchar(30) = 'COM'

SELECT * FROM YourTable

WHERE

Course_ID LIKE SearchParam + '%'

joshphillips7145 (8/13/2012)
I need to use a function to return records for all classes with an enrollment of more than a particular number input by a specified user. As well as write a script that will accept a users input and invoke the function.
I am really unsure where I should start here!!
The columns in my table are like so
Course_ID
Ref_Number
Faculty_ID
Term
Enrollment
TotRev

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 3

joshphillips7145

Right there with Babe

Points: 720

August 13, 2012 at 9:32 am

#1525129

So the variable @SearchParam goes with the function?

Lowell SSC Guru Points: 323518 More actions · Answer 4

joshphillips7145 (8/13/2012)
So the variable @SearchParam goes with the function?

it could, yes; the requirements are still a bit vague;

if this is just "how to create a function( not a stored procedure), with input, that returns a table of data, then it's going to look a little like this:

CREATE FUNCTION GetSomeData

(

@SearchParam varchar(30)

)

RETURNS TABLE

as

RETURN

SELECT ColumnList FROM YourTable

WHERE

Course_ID LIKE SearchParam + '%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 5

I often wonder if it would be helpful to put in a TSQL Homework section on the forums so students looking for help with the same section of class can find the relevant help.

Erin

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 6

Now while this is not homework, it is related to a SQL class I am taking. But this is me studying for the final exam not doing hw, though the suggestion you made makes alot of sense.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 7

I wasn't trying to be sarcastic or anything. I think that there are several classes that encourage you to find the solution or get help online. Since we're going to see a lot of the same questions we could classify them by class/textbook or something that allows them to search that particular forum subsection more easily.

Erin

joshphillips7145 Right there with Babe Points: 720 More actions · Answer 8

Oh I didn't mean to sound that way either, just wanted to reiterate that anyone who helped me would not be doing my hw for me you know, and That is exactly my class, professor goes over a power point and says read the book, do the exercises and use the forums for help.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

I think it's more a "waiting on you". You've asked a generic question, and Lowell gave you an approximate answer. The question would be : what can you do with what he gave you? does it address your question or no?

It's hard to tell how to help clarify further with no more info than what you provided. Depending on what you actually need - a function may or may not be the right approach (functions are limited in a lot of ways).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?