IF ... BEGIN ... END Problem
Feb 28, 2007
I can't see why i get this error :
Server: Msg 156, Level 15, State 1, Procedure fn_GetSpouseFrSecondPerson, Line 49
Incorrect syntax near the keyword 'BEGIN'.
from this code :
if (@flags | @LN_MATCH)
BEGIN
set @nPos = charindex(@sOwnerslast, @sSecondperson)
set @sSpouse = left(@sSecondperson, @nPos - 1)
END
(whole listing is below for the curious)
the problem is not my boolean test; if i just replace it with TRUE, the same results.
Only by commenting out the BEGIN ... END block eliminates the error.
Is there some esoteric rule about which commands or how much or how little can be in a code block?
I'm stumped...
Thanks,
Joe
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTERFUNCTION fn_GetSpouseFrSecondPerson(
@sOwnersName varchar(50),
@sSecondPerson varchar(40),
@sToken varchar(10),
@TrustFlag int, -- implicit cast from datetime does not work (how are nulls cast?)
@CompFlag int
)
RETURNS varchar(30)
AS
BEGIN
-- if SecondPerson is empty, return empty
if @sSecondPerson = '' RETURN ''
if @sSecondPerson is NULL RETURN ''
DECLARE @sOwnersLast varchar(50)
if @TrustFlag is not null SET @TrustFlag = 1 else SET @TrustFlag = 0 --convert null to 0
DECLARE @sSpouse varchar(40), @nLen int, @nPos int
DECLARE @nOwnerSex int, @nSpouseSex int
DECLARE @flags int -- store flags
SET @flags = 0 -- initialize
DECLARE @TRUST int, @COMP int, @LN_MATCH int, @FOR int, @FN_DIFF int, @HW int -- flags bitmasks
SET @TRUST= 1
SET @COMP= 2
SET @FOR= 4
SET @LN_MATCH= 8
SET @FN_DIFF= 16
SET @HW= 32
-- get last name of ownersname without suffix (JR, etc)
SET @sOwnersLast = dbo.fn_GetLastNameNoSuffix(@sOwnersName)
-- first check for spouse with same last name as ownersname
if @TrustFlag = 1 set @flags = @flags + @TRUST
if@CompFlag = 1set @flags = @flags + @COMP
if charindex('FOR', @sSecondPerson) > 0
set @flags = @flags + @FOR
if charindex(@sOwnersLast,@sSecondPerson) > 0
set @flags = @flags + @LN_MATCH
if (@flags | @LN_MATCH)
BEGIN
set @nPos = charindex(@sOwnerslast, @sSecondperson)
set @sSpouse = left(@sSecondperson, @nPos - 1)
END
SET @sSpouse = ProcsAndFuncs.dbo.fn_StrConv(@sSpouse)
RETURN @flags + ':' + @sSpouse
END--function
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
Hi have have two linked SQL Servers and I am trying to get things workingsmootly/quickly.Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ?Basicly, these SPs update a local table and a remote table in the sametransaction. I cant have one table updated and not the other. Please dontsay replicate the tables either as at this time, this is is not an option.I have for example a number of stored procedures that are based around thefollowing:where ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
View 1 Replies
View Related
Oct 12, 2006
Hi All,Can any one help by giving me the details/difference in using the Transaction Isolation Levels (read uncommitted, read committed, repeatable read, or serializable)in asp.net. I just want to know in which case we can use these things in begining a transaction, and will it improve the performance. thanks in advance Boo
View 3 Replies
View Related
Jul 15, 1998
Hi All,
I am basically a newbie to networking. My manager gave me backoffice and said "here, install this".
I have NT and the service pack installed............but now I want to install SQL.........where do I begin?
We are installing NT, and SQL etc on drive C: of the server, and the database (we use Goldmine for database software/contact management), is installed on drive D:
anyone point me in the right direction????
web pages, books, newsgroups...........any help is greatly appreciated.
Thanx In Advance.
Mike Ciotti
mciotti@usinfotel.com
View 1 Replies
View Related
Nov 15, 2004
If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.
Tom
View 1 Replies
View Related
Apr 23, 2008
I need to copy two large tables from one database into another, via the internet. I haven't worked out exactly how yet, but the first issue which has occurred to me is that by the time the first table has been exported (via a SELECT clause?) into a suitable file, the second table (to which it is related) will be out of sync. So, how do I ensure that I end up with a snapshot of the two tables, perfectly in sync with each other? I know that BEGIN/END TRANSACTION makes sure that UPDATES to tables remain in sync, but will it work just for SELECT statements?
View 3 Replies
View Related
May 14, 2008
hello, I have a big problem with a script.. some instructions seems to be not executed. I don't understand. If I execute line perline it's ok - but the entire block no.
Explain me and find the solution:
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ACS_ACL'
AND COLUMN_NAME = 'ZoneUId' )
BEGIN
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
UPDATE dbo.ACS_ACL SET ZoneUId = '1' WHERE ZoneUId IS NULL;
print 'end of script'
END
result:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'ZoneUId'.
the error is on line:
UPDATE dbo.ACS_ACL SET ZoneUId = '000000001' WHERE ZoneUId IS NULL;
how is is possible because the preceding line is
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
View 9 Replies
View Related
Nov 2, 2007
Could someone tell me if this is the correct way to begin and commit a transaction:
CREATE procedure AddTitle
@title varchar(255),
@descriptions varchar(255),
@mediaId int,
@quantityowned int,
@classificationid int
AS
BEGIN Tran TranStart
Set NOCOUNT ON
declare @titleid int --declaring the titleid
INSERT INTO Titles(title, [descriptions])
values(@title,
@descriptions)
set @titleid = SCOPE_IDENTITY() --grabbing the id to be placed in the other table
INSERT INTO Resources(titleid,mediaid,quantityowned)
values(@titleid,
@mediaid,
@quantityowned)
insert into titleclassification(titleid, classificationid)
values(@titleid, @classificationid)
select @titleid as titleid -- return to caller if needed
Commit Tran Transtart
GO
Where would I put rollback transaction? Do I need it?
Thanks!
View 3 Replies
View Related
Jan 12, 2008
I have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile
drop table ReferenceFile
SELECT TOP
INTO SourceFile
FROM
SELECT TOP
INTO ReferenceFile
FROM
-- test data
INSERT ReferenceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values ()
INSERT SourceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values (22)
-- test data
drop table MATCHTEST
SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip
INTO MATCHTEST
FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST
ADD REF_ML VARCHAR(2)
INSERT MATCHTEST ()
SELECT
FROM ReferenceFile r
inner join SourceFile s
on s.New_Last_Name = r.New_Last_Name
select * from MATCHTEST
View 3 Replies
View Related
Sep 24, 2006
/*Subject: How best to use BETWEEN Begin and End Dates to find out if anemployeewas/is member of any group for a certain date range?You can copy/paste this whole post in SQL Query Analyzer or ManagementStudio andrun it once you've made sure there is no harmful code.I am working on an existing database where there is code that is usingBETWEEN logic and three different OR conditions to search for a user thathas worked between begin and end date parameters that you search for.For me the three WHERE conditions with the Begin and End dates are a littleconfusing so I would like to know if there's a better/simpler way to writethis.1- I have groups table with GroupID, Name2- I have employees table with EmployeeID, LastName, FirstName3- I have employeegroups table where the EmployeeID has the GroupID he/shewas/is a member of and from what Begin to what End dates.The employee can never be a member of two groups in any date interval.The employee always was/is a member of a group from a certain to a certaindate and then the next group he/she is a member of a group begins 1 dateafter the previous group membership's end date. Therefore If I worked from2006-01-01 to 2006-01-31 and then I changed group, well in this databasethenext group dates would begin at 2006-02-01 till an Open Ended default dateof2009-12-31.I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05Please continue to read below at the bottom.*/USE tempdbGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype= 'U')BEGINTRUNCATE TABLE EmployeeGroupsDROP TABLE EmployeeGroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')BEGINTRUNCATE TABLE GroupsDROP TABLE GroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype ='U')BEGINTRUNCATE TABLE EmployeesDROP TABLE EmployeesENDGOCREATE TABLE dbo.Groups(GroupID int NOT NULL,Name varchar(50) NOT NULLCONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED(GroupID))GOCREATE TABLE dbo.Employees(EmployeeID int NOT NULL,LastName varchar(50) NOT NULL,FirstName varchar(50) NOT NULLCONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(EmployeeID))GOCREATE TABLE dbo.EmployeeGroups(EmployeeID int NOT NULL,GroupID int NOT NULL,BeginDate datetime NOT NULL,EndDate datetime NOT NULL,CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED(EmployeeID,GroupID),CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID),CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY(GroupID) REFERENCES Groups(GroupID))GOINSERT Groups (GroupID, Name)SELECT 1, 'Group1' UNION ALLSELECT 2, 'Group2' UNION ALLSELECT 3, 'Group3' UNION ALLSELECT 4, 'Group4'GOINSERT Employees (EmployeeID, LastName, FirstName)SELECT 1, 'Davolio', 'Nancy' UNION ALLSELECT 2, 'Fuller', 'Andrew' UNION ALLSELECT 3, 'Leverling', 'Janet' UNION ALLSELECT 4, 'Peacock', 'Margaret' UNION ALLSELECT 5, 'Buchanan', 'Steven'GOINSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALLSELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALLSELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALLSELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALLSELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALLSELECT 4, 3, '2006-05-17', '2099-12-31'GO--SELECT * FROM Groups--SELECT * FROM Employees--SELECT * FROM EmployeeGroupsDECLARE @EmployeeID INTEGERDECLARE @BeginDate DATETIMEDECLARE @EndDate DATETIMEPRINT 'First example of querying...'SET @EmployeeID = 1SET @BeginDate = 'Sep 18 2005 12:00:00:000AM'SET @EndDate = 'Sep 24 2006 12:00:00:000AM'-- This is the code logic being used in the database I am looking at.SELECT *FROM EmployeeGroupsWHERE EmployeeGroups.EmployeeID = @EmployeeIDAND ((EmployeeGroups.BeginDate <= @BeginDate AND EmployeeGroups.EndDate
Quote:
View 2 Replies
View Related
Dec 31, 2007
I want to know if it's a good practice to use sql-transaction in both c# and sql procedures code.
View 4 Replies
View Related
Mar 30, 2006
I notice you can do a END and BEGIN trasaction in BOTH. But I hope BOTH are not needed. So where so I use them ?
Or should I even use them for simple updates and inserts ?
View 3 Replies
View Related
Mar 14, 2001
Many times i write stoted procedures with transaction blocks.
I have delete a row after begin transaction and in continue i
read from table the select statement get back the deleted row:
begin tran
delete mytable
where id = @myid
and seqid = 3
select sum(balance)
from mytable
where id = @myid
............
...............
commit tran
.... OR
rollback tran
the sum(balance) function has calculate the balance of row 3
I use SQL 7.0
Thanks
Renato
View 1 Replies
View Related
May 16, 2002
begin
if @counter=2
update t_import_main set program2=@prog, g2=@gno,yr2=@yr, Program_code2=@pcode where uno=@oldu
case
when @pcode is null then
Program_code2=@gno
End
I get this error message
Incorrect syntax near the keyword 'case'.
What am i doing wrong??
View 2 Replies
View Related
Aug 10, 2001
No I did not write this below, this is from a vendor, I used profiler and I believe their SP is causing a blocking problem on their vendor supplied DB. It thought at the least always have a begin end or a begin trans commit trans. ANy quick opinions greatly appreciated
create procedure write_planned_service_rec
@p1 varchar(20),@p2 varchar(20),@p3 varchar(20),@p4 varchar(20),@p5 varchar(20),
@p6 varchar(20),@p7 varchar(20),@p8 varchar(20),@p9 varchar(20),
@p10 varchar(20),@p11 varchar(20),@p12 varchar(20),@p13 varchar(20),@p14
varchar(20),
@p15 varchar(20),@p16 varchar(20),@p17 varchar(20),@p18 varchar(20),@p19
varchar(20),
@p20 varchar(20)
AS
IF @p20 = 'P'
update patient set date_insurance_updated = getdate() where patient_id =
@p1 and practice_id = @p13
View 1 Replies
View Related
Feb 7, 2008
I wonder about whether this rule is valid or invalid for nested BEGIN/END statement...
Code:
BEGIN
BEGIN
--Query #1 (blah)...
END
WHILE EXISTS (SELECT TOP 1 * FROM #tmpTblPurchaseRaw)
BEGIN
BEGIN
--Query #2 (blah)...
END
BEGIN
--Query #3 (blah)...
END
END
END
I have no idea if nested BEGIN/END is allowed or not...
View 1 Replies
View Related
Sep 26, 2004
Hi
I have an overnight process that takes transactions from an external system & applies updates to a single db table. Other processes may be active on the db but none touch the tables I'm using. I cannot guarantee the volume of source transactions (may vary from 100s to 100,000s).
My question is should I protect the update within a begin+commit/rollback or should I have a recovery procedure to run in the event of failure (that would delete any rows added to my db table)? (My preference is to do the latter - so I'm really looking for any reasons why I shouldn't take this approach).
Thanks.
View 1 Replies
View Related
Aug 12, 2014
Writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts. Here what I come up with but I don't think it will work.
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
Insert 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
[Code] ....
View 1 Replies
View Related
Dec 17, 2007
Is there any good way to check the Begin and End constuction , like which begin is associated with with End.
Thanks,
Bali
View 1 Replies
View Related
Jul 23, 2005
Greetings,I've been reading with interest the threads here on deadlocking, as I'mfinding my formerly happy app in a production environment suddenlydeadlocking left and right. It started around the time I decided towrap a series of UPDATE commands with BEGIN/END.The gist of it is I have a .NET app that can do some heavy reading (nowriting) from tblWOS. It can take a minute or so to read all the datainto the app, along with data from other tables.I also have a web app out on the floor where people can entertransactions which updates perhaps 5-20 records in tblWOS at a time.The issue comes when someone is loading data with the app, and someoneelse tries an update through the web app: deadlocks-ville on theapplication and/or the web app.Again, I believe it began around the time I wrapped those 5-20 recordupdates to tblWOS on the web app with BEGIN/END. The funny thing isthat the records involved are not the same ones, so I'm thinking somekind of table-level lock is going on.I've played with UPDLOCK in examples, but don't quite understand whatit's attempting to do. Since the web update is discrete and short, andit is NOT updating records that are getting loaded, I'd like theBEGIN/UPDATE/END web transaction to happen and not deadlock the loadingapplication.Any suggestions? I'd be most grateful.thanks, Leaf
View 4 Replies
View Related
Mar 29, 2006
Hallo All,
Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.
On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.
View 3 Replies
View Related
Feb 28, 2008
Can help me?
I have to insert BEGIN and COMMIT Transaction in my stored procedure (call to a trigger)
Code Snippet
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[DETTAGLIO_TURNI_DIFENSORI]
(
@tipo_albo VARCHAR(50),
@data_inizio DATETIME,
@data_fine DATETIME,
@descrizione VARCHAR(50),
@idturno INT,
@n_dif INT
)
AS
-- DICHIARAZIONE VARIABILI...
DECLARE @dett_idturno as INT
DECLARE @totale_giorni as INT
DECLARE @tipo_albo_A as VARCHAR(9)
DECLARE @tipo_albo_B as VARCHAR(9)
DECLARE @data_odierna as DATETIME
DECLARE @tot_avvocati as INT
DECLARE @avv_giorno as INT
DECLARE @avv_giornoA as INT
DECLARE @avv_giornoB as INT
DECLARE @conta_giorni as INT
DECLARE @incremento_giorni as INT
DECLARE @incr_dif_giorno as INT
DECLARE @nuovo_idturno as INT
DECLARE @idalboturno as INT
DECLARE @old_turni as INT
DECLARE @tot_giorniA as INT
DECLARE @tot_giorniB as INT
DECLARE @conta_giorni_incremento as INT
DECLARE @conta_avvocati as INT
DECLARE @avv as INT
DECLARE @altri_turni AS INT
DECLARE @neg_giorni_incremento as int
-------------------------------------------------------------------------------------------------------
-- SET VARIABILI PER EVITARE PROBLEMI CON NULL o 0
SET @totale_giorni = 0
SET @tot_avvocati = 0
SET @avv_giorno = 0
SET @conta_giorni = 0
SET @incremento_giorni = 0
SET @incr_dif_giorno = 0
SET @idalboturno = 0
SET @old_turni = 0
--------------------------------------------------------------------------------------------------------
SET @nuovo_idturno = @idturno
BEGIN TRAN
-- conta i giorni
SELECT @totale_giorni = (DATEDIFF(dd, @data_inizio, @data_fine))+1
-- select da vista avvocati per tipo difensori selezionato.. (controllare anche data_fine_iscrizione?)
create table #Tmp
(
[ID_anagrafica] varchar(50)
)
IF @tipo_albo = 'DIFO'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'CPT'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_ORDINARI
SELECT @tot_avvocati As tot_avvocati
-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'DIFM'
BEGIN
SET @tipo_albo_A = 'DIFO'
SET @tipo_albo_B = 'CPT'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_MINORI
SELECT @tot_avvocati as tot_avvocati
-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'CPT'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'DIFO'
-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_CPT
SELECT @tot_avvocati as tot_avvocati
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END
DECLARE @idanagrafica varchar(50)
-- GIORNI PER AVVOCATO
-- tot_giorni * n_dif_minimo / tot_avvocati = tot_giorni_avvocato (con intero successivo)
SET @incremento_giorni = ((@totale_giorni * @n_dif)/@tot_avvocati)
-- controllo se c'è resto..
IF ((@totale_giorni * @n_dif)%@tot_avvocati) <> 0
BEGIN
SET @incremento_giorni = @incremento_giorni + 1
END
-- ogni avvocato deve essere difensore per almeno 2 giorni di seguito..
-- quindi se l'incremento è minore di 2 deve essere uguale a 2
IF @incremento_giorni < '2'
BEGIN
SET @incremento_giorni = '2'
END
-- AVVOCATI AL GIORNO
-- numero variabile.. prendere in considerazione il primo intero e l'intero successivo..
SET @avv_giorno = (@incremento_giorni * @tot_avvocati)/@totale_giorni
SET @avv_giornoB = @avv_giorno
-- controllo il resto della divisione.. se <> 0 @avv_giornoB = @avv_giorno + 1..
-- altrimenti i due valori sono uguali..
IF ((@incremento_giorni * @tot_avvocati)%@totale_giorni) <> 0
BEGIN
SET @avv_giornoA = @avv_giorno + 1
END
ELSE
BEGIN
set @avv_giornoA = @avv_giorno
END
-- conteggi giorni totali difensori...
-- giorni con N difensori
SET @tot_giorniB = ((@avv_giornoB * @incremento_giorni)/@totale_giorni)
-- giorni con M difensori
SET @tot_giorniA = @totale_giorni - @tot_giorniB
declare @totale as int
declare @conta_inseriti as int
set @conta_inseriti = 0
set @totale = (@avv_giornoA * @tot_giorniA) + (@avv_giornoB * @tot_giorniB)
-- ciclo per totale dei giorni
SET @conta_giorni = 1
SET @conta_giorni_incremento = 0
SET @conta_avvocati = 0
WHILE @conta_giorni <= @totale_giorni BEGIN
-- ogni giorno @avv deve essere ZERO
SET @avv = 0
IF @conta_giorni <= @tot_giorniA
BEGIN
SET @avv_giorno = @avv_giornoA
END
ELSE
BEGIN
SET @avv_giorno = @avv_giornoB
END
-- ciclo per ogni giorno per totale di avvocati/giorno
SET @incr_dif_giorno = 0
WHILE (@incr_dif_giorno < @avv_giorno) AND EXISTS(SELECT TOP 1 ID_anagrafica FROM #Tmp) BEGIN
SET @conta_avvocati = @conta_avvocati + 1
SET @data_odierna = DATEADD(dd, (@conta_giorni-1), @data_inizio)
SET @neg_giorni_incremento = -1 * @conta_giorni_incremento
SET @old_turni = 0
SET @altri_turni = 0
SELECT TOP 1 @idanagrafica = ID_anagrafica from #Tmp
DELETE #Tmp WHERE ID_anagrafica = @idanagrafica
-- query che controlla i turni già assegnati per altre liste...
SELECT @old_turni = COUNT(*)
FROM Albo_Turno_Dettaglio CROSS JOIN
ALBO_TURNO
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
(
ALBO_TURNO.Tipo = @tipo_albo_A --- probabile problema con trigger
OR
ALBO_TURNO.Tipo = @tipo_albo_B
)
AND
(
Albo_Turno_Dettaglio.Data
BETWEEN
DATEADD(dd, (@neg_giorni_incremento + 1), @data_odierna)
AND
DATEADD(dd, (@incremento_giorni-@conta_giorni_incremento), @data_odierna)
)
SELECT @old_turni AS old_turni
-- (nel caso in cui il ciclo ricominci..) controllare che questo avvocato non abbia
-- già un set di giorni in questo turno...
-- passato il primo controllo.. deve passare anche questo..
SELECT @altri_turni = COUNT(*)
FROM Albo_Turno_Dettaglio
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
Albo_Turno_Dettaglio.idturno = @nuovo_idturno
SELECT @altri_turni AS altri_turni
IF @old_turni = NULL
BEGIN
SET @old_turni = 0
END
IF @ALTRI_TURNI = NULL
BEGIN
SET @ALTRI_TURNI = 0
END
IF (@old_turni = 0 AND (@altri_turni = 0 OR @altri_turni < (@incremento_giorni)))
BEGIN
-- se non ci sono turni sovraposti assegna il turno all'avvocato x N giorni (incremento_giorni)
-- seleziono il dettaglio con idturno max per aumentare di uno...
SET @idalboturno = 1
SET @dett_idturno = 1
SELECT @dett_idturno = MAX(idalboturno)
FROM Albo_Turno_Dettaglio
if (@dett_idturno) = null
begin
set @dett_idturno = 0
end
SET @idalboturno = @dett_idturno + 1
BEGIN TRAN
INSERT Albo_Turno_Dettaglio
(
idalboturno,
idalbo,
idturno,
data
)
VALUES
(
@idalboturno,
@idanagrafica, -- da cursore
@nuovo_idturno,
@data_odierna -- problemi inserimento data??
)
COMMIT
-- valorizza il numero degli avvocati del giorno + 1
SET @incr_dif_giorno = @incr_dif_giorno + 1
END
ELSE
BEGIN
-- non incrementare la variabile...
SET @incr_dif_giorno = @incr_dif_giorno
END
END -- END WHILE AVVOCATI PER GIORNI
SET @conta_giorni_incremento = (@conta_giorni_incremento + 1)
IF @conta_giorni_incremento < @incremento_giorni
BEGIN
--DEVO RIPOPOLARE LA TABELLA PERCHE' OGNI AVVOCATO DEVE AVERE X GIORNI..
-- cancello tutti i record e poi inserisco di nuovo..
DELETE FROM #Tmp
IF @tipo_albo = 'DIFO'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'DIFM'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END
IF @tipo_albo = 'CPT'
BEGIN
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END
END
ELSE
BEGIN
set @conta_giorni_incremento = 0
END
SET @conta_giorni = @conta_giorni + 1
END
DROP TABLE #TMP
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 9 Replies
View Related
Feb 29, 2008
I get a syntax error when trying to use BEGIN TRY / END TRY in a SQL Server 2005 stored procedure.
I've checked the compatability of the database and its SQL Server 2005 (90). I'm creating the stored procedure in SSMS 2005. Any thoughts would be appreciated, thanks.
View 2 Replies
View Related
Sep 11, 2006
Hello
As I'm not an experienced stored procedure programmer I was wondering where I need to put the BEGIN and END statement in a ALTER Stored Procedure.
As I understand it's the proc that gets altered, not the statements in the proc and that I find confusing.
I need to change the name of a column like:
UPDATE table
SET
Fielddd1 = 'test'
and I wan to change it into
UPDATE table
SET
Field1 = 'test'
Quite simple no? Forget it! Even if I hit the execute command to modify it. It get's changed back to the old code behind my back. So I guess I need to put a BEGIN and an END statement but I tried that already.
Could someonegive me a pointer?
Many thanks!
Worf
View 3 Replies
View Related
May 4, 2006
I'm working on a stored procedure which includes an IF statement at the end of the procedure which appends my sql call with two lines. But I'm getting an error that probably comes from the fact that I have a nested BEGIN END block in my overall procedure. Can anyone tell me if my assumption is correct and help polish of the syntax?
The error I'm getting is:
Msg 156, Level 15, State 1, Procedure payments_sp, Line 55
Incorrect syntax near the keyword 'AND'.
and the sql code looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[payments_sp]
-- Add the parameters for the stored procedure here
@payment_type varchar(15),
@mydate smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @myBool1 tinyint, @myBool12 tinyint
SET @myBool1 = 1
IF (@payment_type = 'CODE1' OR @payment_type = 'CODE4')
SET @myBool1 = 1
ELSE
-- the payment type must be 'CODE1' or 'CODE2'
-- set the value to false
SET @myBool1 = 0
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4')
SET @myBool12 = 1
ELSE
SET @myBool12 = 0
SELECT
SUM(Mydatabase.dbo.[PAYMENTS].[AMT_RECD])
FROM Mydatabase.dbo.[PAYMENTS]
INNER JOIN Mydatabase.dbo.[ACCT]
ON Mydatabase.dbo.[PAYMENTS].[SOME_UID] = Mydatabase.dbo.[ACCT].[SOME_UID]
WHERE Mydatabase.dbo.[ACCT].[CLIENT] = 'MY CLIENT'
AND Mydatabase.dbo.[PAYMENTS].[DATE_RECD] = @mydate
AND Mydatabase.dbo.[PAYMENTS].[BOOL] = @myBool1
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val1'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val2'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val3'
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4')
BEGIN
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val4'
AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val5'
END
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
View 4 Replies
View Related
May 5, 2008
hi,
I have wriiten the code cn.Open();
SqlCommand CmdInsertAct1 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName1.Text.ToString() + "','" + txtActAmt1.Text.ToString() + "','" + ddlAct1.SelectedValue + "' )", cn);SqlCommand CmdInsertAct2 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName2.Text.ToString() + "','" + txtActAmt2.Text.ToString() + "','" + ddlAct2.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct3 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName3.Text.ToString() + "','" + txtActAmt3.Text.ToString() + "','" + ddlAct3.SelectedItem.Value + "')", cn);SqlCommand CmdInsertAct4 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName4.Text.ToString() + "','" + txtActAmt4.Text.ToString() + "','" + ddlAct4.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct5 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName5.Text.ToString() + "','" + txtActAmt5.Text.ToString() + "','" + ddlAct5.SelectedItem.Value + "')", cn);
CmdInsertAct1.ExecuteNonQuery();
CmdInsertAct2.ExecuteNonQuery();
CmdInsertAct3.ExecuteNonQuery();
CmdInsertAct4.ExecuteNonQuery();
CmdInsertAct5.ExecuteNonQuery();
cn.Close();....................................................................
in this code I want to put Commit,Begin,Rollback Transactions.Plz help me.send replies urgently.
View 3 Replies
View Related
Jun 20, 2008
I have this code
DECLARE @tempTableName VarChar(50)SET @tempTableName = NEWID()
CREATE TABLE #@tempTableName( State Char(2), Billed Money, AslCode VarChar(10))INSERT INTO #@tempTableName EXEC GetRecords '2/1/2008'
which gives me this error when I run
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
GetRecords does a select * from a linked server's table. Which is working fine but if I try to do an insert (into temp table) then I get the error.
Any help?
View 1 Replies
View Related
Jun 28, 2005
Is it necessary to include the Begin and End in this statement? IF @CId = '102' BEGIN SET @102 = 1 ENDOr, can it be rewritten as... IF @CId = '102' SET @102 = 1 Thanks all,Zath
View 4 Replies
View Related
Jul 20, 2005
Hi I have a Table with Automatic ID numbers...In access I delete All records by handWhen I add new record with delphi (SQL)the number ID of record count begin with last record+1and not with 0Someone know the statement to reset that?thanx for any help
View 2 Replies
View Related
May 1, 2008
need help
Incorrect syntax near the keyword 'BEGIN'
what is the problem
Code Snippet
DECLARE @vempID varchar(500)
set @vempID = '68737477,51622017'
BEGIN
IF EXISTS (SELECT empID FROM table_2 WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
BEGIN
DELETE FROM table_2
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
end
ELSE
BEGIN
INSERT INTO table_2
SELECT * FROM table_A
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@vempID+',') > 0
END
END
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'BEGIN'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ELSE'.
TNX
View 4 Replies
View Related
Sep 5, 2006
I created a ftp task that uses a variable for the remote path because the file I download changes names every based on the date. So I build up the entire path into a container variable and set the remote path to a variable. then I get an error that the filename does not begin with '/' when the filename that the error references is my variable... I cannot even debug/build my package cause of this error. I cannot find anything online or in BOL. any help would be appriciated. If you need more info to help let me know also.
My variable is set as a string,
exact error:
Error 1 Validation error. Get DailyFiles FTP Task: Variable "DailyFiles" doesn't start with "/". DAILY.dtsx 0 0
Thanks in advance.
View 3 Replies
View Related
Jan 25, 2007
Hi,
I am using the Bulk Insert Task to bulk copy data from a flat file to a SQL Server 2005 table. The flat file contains pipe ( | ) delimited columns and several thousand records which are {CR}{LF} delimited. The file also contains a header record and trailer record, which contain several words but also contains a ( | ) symbol among those words.
E.g.:
HEDR | yadi yadi yada500 | Data Data | More Data | 600460 | Datum | More More | 705550 | Data | Data | 603FOOTR | yadi yadi yada
I need to ignore the header and trailer records and just pickup the proper records. But even though I manually set the First Row property to 2 and the Last Row property to 4, It does not pickup the first true record i.e. the record which begins with 500, and if I set the First Row to 1, it throws me an error citing 'truncation error' or similar. I think it is taking the first record (i.e. header row along with the first row as one, and since there are now more pipes ( | ) the error is thrown)
I've tried setting different values for these properties but to no avail... Any help will be deeply appreciated...
Thanks
Gogula
View 3 Replies
View Related
May 28, 2008
I am trying to enclose a create procedure in a begin...end block and get the following:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.
If i execute the create w/o the begin..end it executes correctly. I am at a complete loss. The greater plan is this: when we roll out an updated version of our software we update many things in the database, I need a way to halt and rollback any database changes if there is an error. If anyone has a better suggestion of help with resolving my error I would greatly appreciate it.
Here is my statement:
begin
CREATE PROCEDURE dbo.LMS_InvTurnsEOMUpdate
AS
-- EOM Script to update the InvTurnsEOMCostHistory and InvTurnsEOMInventory tables.
-- these are used for the Inventory Turns Report.
-- Add this script to SQL jobs to run on the last day of the month.
-- kaj 1/08
--exec LMS_InvTurnsEOMUpdate
--select * from InvTurnsEOMCostHistory
--select * from InvTurnsEOMInventory
declare @PeriodID varchar(10)
declare @PeriodDisplaySeq numeric(7,0)
declare @LMSDateCur numeric(7,0)
-- get current system date in NG format
--select cast(getdate() as smalldatetime)
set @LMSDateCur = ((year(getdate())*10000+month(getdate())*100+day(getdate()))-19000000)
-- get period id and period display seq
select @PeriodID = AOAHNB, @PeriodDisplaySeq = AOI4NB
from YAAOREP
where @LMSDATECUR>=AOAADT and @LMSDATECUR <=AOABDT
-- delete everything in InvTurnsEOMCostHistory Table that has a Period ID and Period Display Seq = current
delete from InvTurnsEOMCostHistory where @PeriodID = AOAHNB
-- insert into the InvTurnsEOMCostHistory Table
insert into InvTurnsEOMCostHistory
select ItemPrice.Item_ID, ' ', @PeriodID, @PeriodDisplaySeq,
@LMSDateCur, ItemPrice.ItemPrice_AvgCost, ItemPrice.ItemPrice_PurchaseCost,
ItemPrice.ItemPrice_LastCost, ItemPrice.ItemPrice_StandardCost,
Item.Item_PackMultiple
From ItemPriceMaxDat
Left outer join ItemPrice on ItemPrice.Item_ID = ItemPriceMaxDat.Item_id and
ItemPrice.ItemPrice_DateOfData = ItemPriceMaxDat.ItemPrice_DateOfData
Left outer join Item on ItemPriceMaxDat.Item_ID = Item.Item_Id
Where ItemPrice.CEST='1'
-- delete everything in InvTurnsEOMInventory Table that has a Period ID and Period Display Seq = current
delete from InvTurnsEOMInventory where @PeriodID = AOAHNB
insert into InvTurnsEOMInventory
select WH_ID, Item_ID, @PeriodID, @PeriodDisplaySeq,
@LMSDateCur, ItemWH_BackOrder, ItemWH_OnOrder, ItemWH_Allocated,
ItemWH_OnHand
From ItemWH
Where ItemWh.CEST='1'
end
Thanks,
Sean
View 5 Replies
View Related