SQLTeam.com | Weblogs | Forums

Function test question

Hi clever people!

The following is part of a TEST QUESTIONNAIRE. Although in the following two part question it
seems silly to create a function and there are other, better ways to do this the test questionnaire
is about CREATING A FUNCTION. Please bear that in mind.
The following are the two tables followed by the two questions the test poses:

CREATE TABLE TestTable1
(
ID INT,
Name VARCHAR(255)
)

INSERT INTO TestTable1
VALUES(1, 'Test1'),
(2, 'Test2'),
(3, 'Test3'),
(4, 'Test4'),
(5, 'Test5'),
(6, 'Test6')

CREATE TABLE TestTable2
(
ID INT,
JobDescription VARCHAR(255),
[Hours] INT
)

INSERT INTO TestTable2
VALUES (1, 'Developer', 10),
(2, 'Web Developer', 8),
(3, 'Architect', 12)

Here are the questions:

a) Create A FUNCTION that takes in a name as parameter and returns the ID for the
corresponding name from TestTable1.
(For the sake of the test the function is called TestTableFunction but feel free to use your own if you want)

b) How would yo optimize the following query taking into account that any text could be passed
as a parameter to the function created above, not just 'Test2'?

SELECT *
FROM TestTable1 a
JOIN TestTable2 b
ON b.ID = a.ID
WHERE a.ID = TestTableFunction('Test2')

I'm looking for answers that are better than mine so I'm asking you clever people for help.

Kind regards

hi

please google ..

there are lots of articles .. ideas .. solutions !!

what you are talking about is a very very common thing !!

:slight_smile:

in case you dont want to google please let me know .. i will do it ..

Hi

I have googled but just cannot find the right answer. Would you please? I would really appreciate it.

Kind regards

hi please see if this link helps !!

  1. create index on ID column ..

1 Like

Thanks. This is the first time on this site so not sure if I've given you the right uptick. If not please email me with instructions and I will do just that๐Ÿ˜

looooooooooks good .. thank you

:+1:

:slight_smile:

For completeness, would you mind pasting your code in here? That'll help someone if they stumble across this in the future