sql procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql procedure help

Author

Topic

reekdog
Starting Member

5 Posts

Posted - 2008-11-12 : 17:29:34

Hey, i'm really stuck on an SQL assignment i have, would really appreciate any help as i'm stuck on how to continue.

In advance, ill post my assignment q'ns so it hopefully makes a bit more sense what I'm doing.

Qn 1. Create a function named location_name_exists that takes a single VARCHAR parameter. The function should return the number of rows in the location table with a name equal to the value of the parameter (ie. city name). If there are no rows with that value, the function should return the number zero (0).

My Code:

HTML Code:
DELIMITER //

DROP FUNCTION IF EXISTS location_name_exists
CREATE FUNCTION location_name_exists(id VARCHAR)
RETURN location_rows
BEGIN
DECLARE location_rows INT(20);
DECLARE rows VARCHAR(20);

SELECT COUNT (rows)
FROM location
INTO location_rows
WHERE id = rows;

IF rows > 0 THEN
RETURN rows;
END IF;
END
//
Delimiter;
Q'n 2. Create a procedure named add_location that adds a new row to the location table. This procedure takes two parameters. The first parameter is the id of the new location, and the second parameter is the name of the new location to add.

If there is already a row in the location table with the given name, then the procedure should output a suitable error message.

If there are no rows with that name, then the procedure should add a new row, using the parameters as appropriate, and output a suitable confirmation message.

Hint: use your function from Question 1.

All messages must be inserted into a table created for the purpose, eg:
CREATE TABLE messages (Message VARCHAR(80));

Hint: use the concat function to form one string.

Your script then must SELECT * FROM messages to display the messages.

My Code so far:

HTML Code:
DELIMITER $$

DROP PROCEDURE IF EXISTS add_location $$
CREATE PROCEDURE add_location (newlocation_id (DECIMAL (*), newlocation_name (VARCHAR ) )
BEGIN

DECLARE location_id DECIMAL(5);
DECLARE location_name VARCHAR(20);

SELECT COUNT (location_name)
INTO newlocation_name
FROM location
WHERE location_name = newlocation_name;

IF (newlocation_name > 1) THEN
SELECT CONCAT(‘The Location’,newlocation_name, ‘already exists’) AS ‘ERROR;
ELSE
INSERT INTO location VALUES (newlocation_id, newlocation_name);
SELECT CONCAT ('New location has been added’) AS 'SUCCESSFUL';
END IF;

END add_location;
END $$

DELIMITER ;

I'm really stuck after the second paragraph of the second question. The hint of using the function from Question 1 really has me confused!

Would really appreciate any help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-12 : 17:51:24

You code does not appear to be valid Microsoft SQL Server TSQL.

You best bet would be to ask your treacher for help.

CODO ERGO SUM

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-11-12 : 18:45:15

Im not sure if I should try to make this complete for you as you have mentioned clearly that this is an assignment. You can try on the following lines & see what you get.
Good luck,
Anil Kumar.

CREATE FUNCTION [dbo].[GetLocation] (@LocationId NUMERIC(29))
RETURNS NUMERIC(29)
AS
BEGIN
DECLARE @Locations as INT
-- get count of records for the specific location id
SELECT @Locations = count(*) from Location WHERE Locationid = @LocationId
RETURN @Locations
END

To run the function, run the following -

select [dbo].[GetLocation]('283071539059')

reekdog
Starting Member

5 Posts

Posted - 2008-11-12 : 19:17:40

Thanks for the replies.

Kumar_Anil was that code you wrote for the initial procedure? I thought i had mine working fine, i just am unsure how to call upon that function within the procedure. Any other help would be appreciated, i'm also not sure what '@', i don't think we've covered that yet.

reekdog
Starting Member

5 Posts

Posted - 2008-11-12 : 19:18:37

sorry, i meant to say initial question (function), not procedure.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-11-12 : 20:25:47

What I wrote in here was a Function that you can use by passing a parameter to it & that parameter in this case is the Id.

Here is my test data and if you run everything that I wrote in here, you will see what Im talking about.

regards,
Anil.

---From here
create table dbo.Location (Location varchar(100), ID Int)
insert into dbo.Location
select 'ILEYM4', 12 union
select 'Russol', 13 union
select 'kraus', 14 union
select 'wangw2', 22 union
select 'cassam1', 122 union
select 'gascok', 11 union
select 'INTCOLMJ',20 union
select 'malirr',26 union
select 'zzddr', 25

CREATE FUNCTION [dbo].[GetLocation] (@LocationId NUMERIC(29))
RETURNS NUMERIC(29)
AS
BEGIN
DECLARE @Locations as INT
-- get count of records for the specific location id
SELECT @Locations = count(*) from Location WHERE Id = @LocationId
RETURN @Locations
END

select [dbo].[GetLocation]('22')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:02:52

AnilKumar
as per your sample data, the function should be as follows


CREATE FUNCTION [dbo].[GetLocation] (@LocationId int) 
RETURNS int
AS
BEGIN
DECLARE @Locations as INT
-- get count of records for the specific location id
SELECT @Locations = count(*) from Location WHERE Id = @LocationId 
RETURN @Locations
END

and call it as follows

select [dbo].[GetLocation] 22

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:04:52

and reekdog, i dont think you're using sql server seeing the synatx of code.

reekdog
Starting Member

5 Posts

Posted - 2008-11-13 : 00:18:38

oh ok, I'm not sure :S I'm new to SQL I'm not familiar with different kinds of SQL. I apologize if I'm posting in the wrong place.

I was wondering for my procedure, can i get this code working to call my function: If there are no rows with that name, then the procedure should add a new row, using the parameters as appropriate, and output a suitable confirmation message.

IF (location_name_exists(location_name) = 0) THEN
INSERT INTO location VALUES (inLocation_id, inLocation_name)
END IF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 01:40:28

in sql server, the way to do this is

IF NOT EXISTS (SELECT 1 FROM location WHERE location_name = 0)
INSERT INTO location VALUES (inLocation_id, inLocation_name)

reekdog
Starting Member

5 Posts

Posted - 2008-11-13 : 02:20:06

Whats the way I'm doing it?? :S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 02:53:28

quote:
Originally posted by reekdog

Whats the way I'm doing it?? :S

yours syntax wont work in SQL server. thats what i meant

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 04:17:35

If you use MYSQL, post your questions at www.mysql.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources