Sql Stored Procedures

Question

I'm trying to write a Stored Procedure this is what I have so far

Create procedure sp_Create_order
@P_nafn varchar(50),
@P_fj int,
@P_sótt datetime,
@F_kt varchar(10),
@V_nr int,
@L_id int

as 
begin
    set nocount on

if exists(
   select * from Lotur
   where L_id=@L_id and
   @P_sótt between L_hefst and L_pfrest
)
INSERT INTO Pantar(P_nafn, P_fj, P_sótt, F_kt, V_nr, L_id) 
VALUES (@P_nafn, @P_fj, @P_sótt, @F_kt, @V_nr, @L_id)
end

but I am getting these errors

Msg 102, Level 15, State 1, Procedure sp_Create_order, Line 14 Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure sp_Create_order, Line 15 Incorrect syntax near ' '.

on these lines

select * from Lotur
where L_id=@L_id

and

@P_sótt, L_hefst and L_pfrest are all dates and I am tryng to put a condition on saying that nothing should be Inserted unless @P_sótt is equal to or between L_hefst and L_pfrest

Answer 1

• Please use meaningful names for your variables
• Do not create sp for every thing like the one above
• Modify the query to have SELECT L_ID NOT SELECT *
• As for the error, probably you have mistyped something

Answer 2

First of all, I wouldn't recommend prefixing your Stored Procedure with sp_. Performance is at least one reason why it's a bad idea.

Your stored procedure compiled ok for me. Another recommendation would be to use

if exists (
   select 1 
   from 
       Lotur
   where 
       L_id= @L_id 
       and @P_sótt between L_hefst and L_pfrest
)

as opposed to SELECT *. Did you get the error when compiling or trying to use it?

EDIT:

In response to your post about raising errors, you might want to look at RAISERROR. Here is an example of how to use it in Stored procedures

Answer 3

Ok thanks I've got it working now do you have any ideas on how to throw an error if @P_sótt is not between L_hefst and L_pfrest