Stored procedure logic

Transact-SQL
1

Hello,

I need help in how to build a stored procedure.
I have the following three tables:
Category (CatID,Category) --> Values: ({1,1},{2,2}{3,3},{4,4},{5,5}),
Applications (Idno,Category)--> Values: ({123,1}, {56,5})
Restrictions (Category, CategoryRestr) --> Values: ({1,3})

Parameters: @idno

What I want to get as a result is the eligible categories by omitting the categories that are already in Applications for a particular idno and the ones that have restrictions with the ones already inserted.

The result should be:
@idno=123 -->(2,4,5)
@idno=56 -->(1,2,3,4)
@idno=987 -->(1,2,3,4,5)

Thank you in advance.

2

Please provide the table definitions as CREATE TABLE statements and the values as INSERT into statements. Also, please at least write the boiler plate for your stored procedure. Otherwise, we'll have to do all that work for you.

3

Thank you for your answer. Here's the CREATE/INSERT query

CREATE TABLE CATEGORIES
(
CATID INT NOT NULL,
CATEGORY INT,
CONSTRAINT PK_CATID PRIMARY KEY CLUSTERED (CATID)  
)

INSERT INTO CATEGORY (CATID,CATEGORY)
VALUES(1,1)
INSERT INTO CATEGORIES (CATID,CATEGORY)
VALUES(2,2)
INSERT INTO CATEGORIES (CATID,CATEGORY)
VALUES(3,3)
INSERT INTO CATEGORIES (CATID,CATEGORY)
VALUES(4,4)
INSERT INTO CATEGORIES (CATID,CATEGORY)
VALUES(5,5)

CREATE TABLE APPLICATIONS(
IDNO INT NOT NULL,
CATEGORY INT,
CONSTRAINT PK_IDNO PRIMARY KEY CLUSTERED (IDNO)  
)

INSERT INTO APPLICATIONS (IDNO,CATEGORY)
VALUES (123,1)
INSERT INTO APPLICATIONS (IDNO,CATEGORY)
VALUES (56,5)

CREATE TABLE RESTRICTIONS
(
CATEGORY INT,
CATEGORY_RESTR INT
)

INSERT INTO RESTRICTIONS (CATEGORY,CATEGORY_RESTR)
VALUES(1,3)

This is the query I wrote but it doesn't work

SELECT CATEGORY FROM CATEGORIES WHERE CATEGORY NOT IN (
SELECT CATEGORY FROM APPLICATIONS WHERE IDNO=@idno
UNION
SELECT CATEGORY_RESTR FROM APPLICATIONS LEFT OUTER JOIN RESTRICTIONS
ON APPLICATIONS.CATEGORY=RESTRICTIONS.CATEGORY
WHERE IDNO=@idno
)
4

I found the error, I'm giving the correct query below:

SELECT CATEGORY FROM CATEGORIES WHERE CATEGORY NOT IN (
SELECT CATEGORY FROM APPLICATIONS WHERE IDNO=@idno
UNION
SELECT CATEGORY_RESTR FROM APPLICATIONS INNER JOIN RESTRICTIONS
ON APPLICATIONS.CATEGORY=RESTRICTIONS.CATEGORY
WHERE IDNO=@idno
)