SQL stored procedure

Takaharu · Oct 9, 2010, 02:40 PM

This is driving me up the freaking wall

I'm trying to make a stored procedure that checks to see if the values entered already exist. However, it keeps telling me: Incorrect syntax near the keyword 'if'.
Utilising the auto syntax checker I find: "Must declare the scalar variable" on each of the variables.

Code:

create procedure sp_import(@desc as nvarchar(max), @amount as nvarchar(50), @type as nvarchar(50), @accid as int, @business as int, @proc as int)

  if not exists(select * from Transactions where Description = @desc and Amount = @amount and Type = @type and accountid = @accid and Business = @business and Processed = @proc)

    begin
      insert into Transactions (description,amount,type,AccountID,business,processed) values (@desc,@amount,@type,@accid,@business,@proc)
    end

I've made stored procedures before on SQL 2005 (using SQL Express 2008 x64 at home) but I've never had a problem like this.

Takaharu · Dec 17, 2010, 09:05 PM

Code:

CREATE procedure [dbo].[sp_ImportTrans]  
@Date as Date,  
@Description as nvarchar(MAX),  
@Amount as nvarchar(50),  
@Type as nvarchar(50),  
@Account as nvarchar(50),  
@Business as bit,  
@Processed as bit  
as  
if (select count(*) from transactions where  
date = @Date  
and amount = @Amount  
and description = @Description  
and type = @Type  
and accountid = (select id from account where name = @Account)  
and business = @Business  
and processed = @Processed  
) = 0  
insert into transactions (date,description,amount,type,accountid,business,processed) values(  
@Date,  
@Description,  
@Amount,  
@Type,  
(select id from account where name = @Account),  
@Business,  
@Processed  
)  
else  
insert into Tran_Dupes (date,description,amount,type,account) values(  
@Date,  
@Description,  
@Amount,  
@Type,  
(select id from account where name = @Account)  
)

This code made the import insanely fast from my VB app; VB passes the data for the stored procedure, SQL checks to see if the entry exists in the database, checks to see if the transaction matches any of the current transactions then adds them to the table if it's not found. If a match is found then it adds the entry to a separate table. The VB app displays the amount of rows in the dupes table then, if it's over 0, prompts the user to process the dupes (delete, clear all, etc).
It imported nearly 500 transactions into a (an?) SQL database in about 1 second.

Oct 9, 2010, 02:40 PM	#1
Takaharu I can fart in 7 languages Join Date: Aug 2009 Location: England, UK Posts: 1,862 Rep Power: 113 System Specs	SQL stored procedure This is driving me up the freaking wall I'm trying to make a stored procedure that checks to see if the values entered already exist. However, it keeps telling me: Incorrect syntax near the keyword 'if'. Utilising the auto syntax checker I find: "Must declare the scalar variable" on each of the variables. Code: create procedure sp_import(@desc as nvarchar(max), @amount as nvarchar(50), @type as nvarchar(50), @accid as int, @business as int, @proc as int) if not exists(select * from Transactions where Description = @desc and Amount = @amount and Type = @type and accountid = @accid and Business = @business and Processed = @proc) begin insert into Transactions (description,amount,type,AccountID,business,processed) values (@desc,@amount,@type,@accid,@business,@proc) end I've made stored procedures before on SQL 2005 (using SQL Express 2008 x64 at home) but I've never had a problem like this. __________________ I don't get paid to know the answer, therefore I'm far more likely to give you a straight and honest answer. Mods Rig, Box Mods Rig, Folding details

Dec 17, 2010, 09:05 PM	Thread Starter #2
Takaharu I can fart in 7 languages Join Date: Aug 2009 Location: England, UK Posts: 1,862 Rep Power: 113 System Specs	Re: SQL stored procedure Code: CREATE procedure [dbo].[sp_ImportTrans] @Date as Date, @Description as nvarchar(MAX), @Amount as nvarchar(50), @Type as nvarchar(50), @Account as nvarchar(50), @Business as bit, @Processed as bit as if (select count() from transactions where date = @Date and amount = @Amount and description = @Description and type = @Type and accountid = (select id from account where name = @Account) and business = @Business and processed = @Processed ) = 0 insert into transactions (date,description,amount,type,accountid,business,processed) values( @Date, @Description, @Amount, @Type, (select id from account where name = @Account), @Business, @Processed ) else insert into Tran_Dupes (date,description,amount,type,account) values( @Date, @Description, @Amount, @Type, (select id from account where name = @Account) ) This code made the import insanely fast from my VB app; VB passes the data for the stored procedure, SQL checks to see if the entry exists in the database, checks to see if the transaction matches any of the current transactions then adds them to the table if it's not found. If a match is found then it adds the entry to a separate table. The VB app displays the amount of rows in the dupes table then, if it's over 0, prompts the user to process the dupes (delete, clear all, etc). It imported nearly 500 transactions into a (an?) SQL database in about 1 second. __________________ I don't get paid to know the answer, therefore I'm far more likely to give you a straight and honest answer. Mods Rig, Box Mods Rig, Folding details Last edited by Takaharu; Dec 17, 2010 at 09:12 PM.*