Tutorial :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


Solution: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


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »