Where Does BEGIN And END Go In ALTER PROCEDURE
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
ADVERTISEMENT
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
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
Apr 1, 2007
Keep in mind this is my first compiled SQL program Stored Procedure(SP), copied from a book by Frasier Visual C++.NET in Visual Studio2005 (Chap12). So far, so theory, except for one bug (feature?)below. At some point I'm sure I'll be able to laugh about this, akinto forgeting a semi-colon in C/C++, but right now it's frustrating(time to sleep on it for a while).Problem--For some reason I get the error when trying to save files where twotables (called Author and Content), linked by a single key, form arelationship.By simple comparison of the source code in the textbook and my program(below) I found the difference: instead of, like in the textbook, theStored Procedure (SP) starting with "CREATE PROCEDURE", it*automatically* is (was somehow) given the name of 'ALTER PROCEDURE'and I cannot change this to "CREATE PROCEDURE" (you get an error in MSVisual Studio 2005 Pro edition of "There is already an object namedXXX in the database", see *|* below). No matter what I do, the SP isalways changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!(otherwise it simply will not save)Anybody else have this happen? (See below, others have had this happenover the years but it's not clear what the workaround is)Keep in mind this is my first attempt and I have ordered somespecialized books on SQL, but if this is a common problem (and Isuspect it's some sort of bug or quirk in VS2005), please let me know.Frankly I think SQL as done by VS2005 is messed up.Here are two Usenet threads on this problem:(1) http://tinyurl.com/2o956m or,http://groups.google.com/group/micr...1454182ae77d409(2) http://tinyurl.com/2ovybv or,http://groups.google.com/group/micr...9e5428bf0525889The second thread implies this is a bug--any fix?Also this bug might be relate to the fact I've switched (and notrebooted) from Administrator to PowerUser after successfully changingthe permissions in the SQL Server Management Studio Express (see thisthread: http://tinyurl.com/2o5yqa )Regarding this problem I might try again tommorrow to see if rebootinghelps.BTW, in the event I can't get this to work, what other SQL editor/compiler should I use besides MS Visual Studio 2005 for ADO.NET andSQL dB development?RL// source files// error message:'Authors' table saved successfully'Content' table- Unable to create relationship 'FK_Content_Authors'.The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"FK_Content_Authors". The conflict occurred in database "DCV_DB",table "dbo.Authors", column 'AuthorID'.// due to the below no doubt!--CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--*|* Error message given: when trying to save CREATE PROCEDURE StoredProcedure: "There is already an object named 'InsertAuthor' in the dB
View 11 Replies
View Related
Jul 5, 2007
Hi
How can I set alter procedure rights for all procedures in a database for one user?
View 1 Replies
View Related
Nov 13, 2007
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
View 2 Replies
View Related
Dec 24, 2007
I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.
View 3 Replies
View Related
May 18, 2007
Hi all,
I use SQL2005 and I recently noticed this...
When I right click a stored procedure and select modify I get something like this
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
xxx xxx xxx'
instead of the usual alter procedure...
I think that this happened after I installed SP2 (which I cannot remove)
Why this is happening and how can I revert it to the old way of altering stored procs?
View 2 Replies
View Related
May 20, 2008
we use alter statements for our stored procedures when rolling out a new version of our app. We have noticed that when viewing the stored proc in production it looks like the altered one, but when the app runs it, the changes are not taking effect. Has any one else experienced anything like this? It's like the old version is still being held on too.
To get around the problem we have been dropping and recreating the procedures.
Any help would be appreciated. Thanks!
View 1 Replies
View Related
Dec 23, 2007
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Jun 27, 2007
Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?
Thanks,
David
View 9 Replies
View Related
May 13, 2008
Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:
Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN
ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m
END
Thanks for you reply.
View 1 Replies
View Related
Apr 27, 2006
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword]
(
@loginid nvarchar(180),
@oldpassword nvarchar(40),
@newpassword nvarchar(40)
) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @newpassword
WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END
IF @@ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END
COMMIT TRANSACTION
END
************
This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
View 10 Replies
View Related
Jan 28, 2008
Hi all,
I need to get a better understanding of alter procedure. If I Create a Procedure in SQL Server 2005, and want to modify that to Alter Procedure statement instead of create procedure... How do I do that??
I'm not an SQL Server person.. I dont understand why my work place needs Alter proceudre all the time but all our procedures have Alter procedure statement. Once they are created they have to be changed to Alter procedure...
Do I just need to change the keyword CREATE to ALTER?? because when I try to save it ,,, it saves it as a .sql file... Not the script.
Immediate reply would be appreciated
Thanks
View 7 Replies
View Related
Jun 26, 2001
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I must be missing something. Any suggestions?
View 4 Replies
View Related
Aug 10, 2015
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
View 1 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
View 4 Replies
View Related
Jul 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
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
Jul 20, 2005
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
View 2 Replies
View Related
Oct 8, 2007
I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
my code:
Alter table customers alter column age
View 7 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 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
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
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
