January 6, 2008 at 5:24 pm
I have cracked my brains out to to create a stored procedure with out no luck or help.
Am not looking for answers but guidance that is clear and straight to the point.
I want to create a stored procedure that will return the records from a column within a table, when a user uses certain keywords that are associated with the names within the searched table column, based on a scalar variable.
Can anyone help?, also how do i assign scalar variable to a parameter, all resources i have searched are not very well detailed.
Thanks
January 6, 2008 at 5:55 pm
Since you have cracked your brains out, can you post your attempts thus far?
PS This has "homework" written all over it. While there is nothing wrong with that, if you acknowledge that that is the case, we can help you accomplish it in a manner in which you'll not only reach the goal, but learn along the way.
January 6, 2008 at 9:52 pm
Agree with DAvid. Show us some work, some examples, DDL, data, etc. along with what you've tried and why it doesn't work. We're happy to help point you in the right direction.
January 7, 2008 at 1:41 am
This is what I have done so far, and if you see its the declaration of variables am problems with.
use master
create database test
use test
create table company
(company_id int, company_name varchar (100),premise varchar (10),
street varchar (30),town varchar (30),postcode char(7),x_loc int,y_loc int,
primary key (company_id))
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(2,'Tesco Express','Northing','85 Test Avenue','Manchester','M1 3TR',40,30)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(3,'Cash Point','Northing','20 Test Road','London','N16 3NH',51,30)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(4,'Sainsbury','Easting','05 Test Street','London','E1 7YT',45,60)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(5,'Tesco Express','Easting','58 Alpha Avenue','London','E3 8IU',40,55)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(6,'Cashpoint','Easting','57 Beta Avenue','London','SW5 6YR',55,45)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(7,'Sainsbury Local','Northing','30 Gamma Avenue','London','E8 3RT',10,25)
insert into company
(company_id,company_name,premise,street,town,postcode,x_loc,y_loc)
values
(8,'Tesco','Easting','15 SQL Avenue','London','W4 7UY',25,15);
use test
create table company_keyword
(company_id_keyword varchar (255),distance int,
primary key (company_id_keyword,distance))
insert into
company_keyword(company_id_keyword,distance)
values ('Tesco',40)
insert into company_keyword (company_id_keyword,distance)
values
('Sainsbury',30)
insert into company_keyword (company_id_keyword,distance)
values ('Cash Point',20)
insert into company_keyword (company_id_keyword,distance)
values
('Tesco Express',10)
insert into company_keyword (company_id_keyword,distance)
values
('Tesco',35)
insert into company_keyword (company_id_keyword,distance)
values
('Cash Point',25)
insert into company_keyword (company_id_keyword,distance)
values
('Sainsbury',15);
select * from company_keyword
go
select * from company
use test
CREATE FUNCTION dbo.icddistance
--===== Declare the input parameters (order sensitive)
(
@X1 FLOAT, --X component of coordinate pair 1
@Y1 FLOAT, --Y component of coordinate pair 1
@X2 FLOAT, --X component of coordinate pair 2
@Y2 FLOAT --Y component of coordinate pair 2
)
RETURNS FLOAT
AS
BEGIN --Pythagorean's formula for length of hypothenuse
--with Donald Projection modifier for conversion to miles.
RETURN (SELECT SQRT((SQUARE(@X1-@X2) + SQUARE(@Y1-@Y2))/10))
END
use test
create procedure dbo.sp_proximity_search
@search_expression varchar(255),
@x_locint,
@y_locint,
@max_distanceint,
@max_recordsint
as
declare @search_expression = select company_name from company
declare @max_distance = dbo.icddistance
January 7, 2008 at 3:25 am
Hi b-boy
What do you want to achieve with this code?
declare @search_expression = select company_name from company
declare @max_distance = dbo.icddistance
You should use someting like
select @search_condition = company_name from company
second line - you have to provide parameters to function, like.
set @max_distance = dbo.icddistance(.022, 0.131, 143.3, 15)
read more about variables and setting their values in BOL.
First line will return last company name in table as it is stored in physical order (unless you create a clusterd index), so you have to have some criteria for selecting right company. Anyway, @search_condition seems to be an input parameter, so verify if assigning company name to it is the right way to go.
HTH
Piotr
...and your only reply is slàinte mhath
January 7, 2008 at 3:47 am
Thanks very much
The distance will be based on the user input from the search option on the front end application
I also get this error message
Msg 111, Level 15, State 1, Line 2
'CREATE FUNCTION' must be the first statement in a query batch.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@X1".
January 7, 2008 at 5:24 am
Hi,
Create this "dbo.icddistance" function sepatatly.
After that create one more procedure like:
ALTER PROCEDURE dbo.sp_CheckDistance
(
@X1 FLOAT,
@X2 FLOAT,
@X3 FLOAT,
@X4 FLOAT
)
AS
DECLARE @Temp FLOAT
SET @Temp = dbo.icddistance (@X1,@X2,@X3,@X4)
SELECT @Temp
After that execute this.
EXEC dbo.sp_CheckDistance .022, 0.131, 143.3, 13
🙂
January 7, 2008 at 6:59 am
The error is because CREATE PROCEDURE needs to be the first thing in the batch. after the "Use Test", you need a "GO" to break the batches.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply