Im new to this forum and new also to SQL SERVER Edition Express.
Im trying to creat stored procedure. My main problem is that I need to display an alias consisting of 2 fields in a combobox (VB.Net) using also an innerjoin. Can anyone help me find my mistake please
My code is here and the error is :
-----------------------------------------------------------
Msg 156, Level 15, State 1, Procedure LA_suppName, Line 16
Incorrect syntax near the keyword 'INNER'.
Eventually I would need to use tb_LA.LANo and make a query to populate the tb_LABooks in another combobox on selectvaluechanged. Is this possible please???
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP. I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int." Thanks Neil CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int) AS Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000) CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int ) SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName' SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId) SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId FROM vw_GetPersonsByCategoryAndType ' EXEC (@SelectClause + @WhereClause +@OrderByClause)
hai guys, i have written a stored procedure which creates a table ex: USE PUBS GO IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC') DROP PROCEDURE RC_STRPROC GO USE PUBS GO CREATE PROCEDURE RC_STRPROC (@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35)) AS IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME') DROP TABLE @TBLNAME CREATE TABLE @TBLNAME (@COLVAL1, @COLVAL2) GO it gives an syntax error at '@tblname' can u guys tell me the problem
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE): ///--MuCh14spInvTotal3.sql--/// USE AP --AP Database is installed in the SSMSE-- GO CREATE PROC spInvTotal3 @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS
IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices JOIN Vendors WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar) GO /////////////////////////////////////////////////////////////// Then I executed it and I got the following error: Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12 Incorrect syntax near the keyword 'WHERE'. I do not know what wrong with it and how to correct this problem.
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
This is probably a very simple question but i would appreciate some help I have the following stored procedure and I am getting the error message "The objects "aspnet_Users" And "aspnet_Users" in the FROM clause have the same exposed names. Use correlation names to distinguish them" Can anyone tell me where I am going wrong. many thanks Martin ALTER PROCEDURE [dbo].[SP_CreateOrder](@CartID char (36),@UserId Varchar (50),@OrderTotal decimal (8)) AS Declare @OrderID int INSERT INTO Orders (UserID,Name,Address1,Address2,City,State,Country,Zip,OrderTotal) SELECT aspnet_Users.UserId,aspnet_Users.Name,aspnet_Profile.Address1,aspnet_Profile.Address2,aspnet_Profile.City,aspnet_Profile.State,aspnet_Profile.Country,aspnet_Profile.Zip,@OrderTotal FROM aspnet_Users,aspnet_Profile Join aspnet_Users On aspnet_Profile.UserId=aspnet_Users.UserId WHERE asp_Users.UserId=@UserId SET @OrderID = @@IDENTITY INSERT INTO OrderDetails (UserId,OrderID,ProductID,Name,Cost,Quantity) SELECT @UserID,@OrderID,Products.ProductID,Products.Name,Products.Cost,ShoppingCart.Quantity From aspnet_User,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE aspnet_User.Userid=@UserId AND ShoppingCart.CartID = @CartID EXEC SP_DeleteShoppingCart @CartIDSELECT @OrderIDGO
Does this Stored Procedure Syntax look okay?I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag 'Add Input Parameters to date and from date variables - dtFrom and dtTo objComm.Parameters.Append.CreateParameter("@dtFrom", adDate, adParamInput, 9) objComm.Parameters.Append.CreateParameter("@dtTo", adDate, adParamInput, 9) CREATE PROCEDURE editorTAmonthlyReport @FromDate DateTime, @ToDate DateTime As
Select U.UserID, U.Title, U.FirstName, U.Surname, TAU.UserPosition, U.Email, A.AgencyName, A.AddressLine1, A.AddressLine2, A.Town, A.County, A.Postcode, C.Country, Coalesce(U.Phone,A.Phone) As Phone, TAU.DateOfCreation From [user] U LEFT OUTER JOIN MyTrafalgarUser MTU ON MTU.userID=U.UserID INNER JOIN TravelAgencyUser TAU ON U.UserID=TAU.UserID INNER JOIN Agency A ON TAU.AgencyID=A.AgencyID INNER JOIN Country C ON A.CountryID=C.CountryID Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @FromDate) < 0 ) and datediff(d,TAU.DateOfCreation, @ToDate) > 0 ORDER BY TAU.DateOfCreation GO
Hi All I will like to allow guess users to add items to the shopping cart (using a temp shoppingcartID): ' If user is not authenticated, either fetch (or issue) a new temporary cartID If Not context.Request.Cookies("MMDotNetID") Is Nothing ThenReturn context.Request.Cookies("MMDotNetID").Value.ToString Else ' Generate a new random GUID using System.Guid ClassDim tempCartId As Guid = Guid.NewGuid() ' Send tempCartId back to client as a cookie context.Response.Cookies("MMDotNetID").Value = tempCartId.ToString() context.Response.Cookies("MMDotNetID").Expires = DateTime.Now.AddMonths(1) ' Return tempCartId Return tempCartId.ToString ' If user is authenticated I use the userID to store the items in the Shopping Cart 'If the user logon after adding some items to the cart I need to Migrate the old items. 'But my STORED PROCEDURE is not working properly:ALTER PROCEDURE MM_SP_ShoppingCartMigrate ( @OriginalCartId nvarchar(50), @NewCartId nvarchar(50)) As IF EXISTS(SELECT * FROM MM_SP_ShoppingCart WHERE CartID = @NewCartId) BEGIN
I need help in writing a stored procedure on SQL Server 2000.Basically the stored procedure's primary task is to generate invoicerecords and insert the records in a invoice table. In order togenerate the invoice records, I have an initial table which arebasically Day Records. The task that I would like to complete is toselect all records from that initial table and I guess put them into atemp table. Now that i have my temp table, I would like to loop thruthe table record by record, and do inserts in the invoice table. Icant seem to figure out the syntax since I am somewhat weak in TSQLProgramming. I would appreciate any assistance on this forum or to myemail. Also If you need some pseudocode for the process or the DDL forthe initial table and the invoice table, I can definitely post that inthe forum possibly in the next thread. Please advise, I would need afull written syntax to get me started since i have some otherprocesses that I would need to build using the template. Thanks again.
I'm enhancing a large ASP application by replacing raw SQL statementsin the code with calls to stored procedures. I've seen thisrecommended in many places to increase SQL Server effieicency.Replacing select statements is going fine but I hit a sticking pointwhen trying to replace an update statement.Currently, I use this kind of statement a lot in my ASP:sql = "update"sql = sql & " field1 = value1"sql = sql & ",field2 = value2"If Len(value3) Then sql = sql & ",field3 = value3"sql = sql & " where ...., etc, etcThe important part here is checking if "value3" has a value beforeinserting it into my SQL statement. So how can I put this updatestatement into a stored procedure, pass in values for value1, value2,and value3, and leave off the value3 part of the update if value3comes in as null.Thanks all.
Hi AllIm trying to use the code at the bottom of this message inside my storedprocedure and when i execute the procedure in query analyzer i get thefollowing error:Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'H'.This error happens a few times and im pretty sure it is because the selectstatement needs the ' around the data that is being searched. This is whatthe select statement looks like for me:SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMSOR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRVAnd im pretty sure it is failing because sql wants the select statement tolook like this:SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID ='HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'Am i thinking along the right lines with this ? If so does anybody know of away that i can put the ' mark around the the data that is being searched for? Any help is greatly appreciatedThanksCREATE PROCEDURE [dbo].[TestSP]@MachineName VarChar(50),@UserName VarChar(50)ASDECLARE @MachineLength Char(2) /* Local Machine Name Length */DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */DECLARE @SqlStr VarChar(300) /* SQL Select String */DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */SET @SrchInt = 1SET @MachineLength = Len(@MachineName)SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = 'WHILE @SrchInt <= @MachineLengthBEGINSET @CurrMach = LEFT(@MachineName,@SrchInt)IF @SrchInt = 1BEGINSET @SqlStr = @SqlStr + LEFT(@MachineName,1)ENDIF @SrchInt > 1BEGINSET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMachENDSET @SrchInt = @SrchInt + 1PRINT @SqlStrENDEXEC (@SqlStr)GO
This is probably a simple question but i would appreciate some help. I have a stored procedure that i want want first to check if a customer exists in the customer table and if a customer doesnt exist to insert values. I know that there are other ways to do this but i was wondering if itwas possible to do this only within the stored procedure. I have been using: SELECT COUNT(*)FROM customersWHERE customerID=@customerID IF COUNT(*)=0............ (Insert statement follows) But the new customer values are not being inserted.Can anyone tell me where I am going wrong or suggest a diferent wat of doing this? Many Thanks martin BY the way the rows seem to be counted as the information is inserted if i use If COUNT(*)>0 but the insert is not happening with the code above.
I'm having a weird issue with a stored procedure I wrote. I can run it in Query Analyzer and no problem, but when I run it from the web I get an error.
Heres the stored procedure: CREATE PROCEDURE dbo.UpdateLosses ( @Record int, @PriorCarrier varchar(50), @Year varchar(4), @Losses money ) AS BEGIN TRAN UPDATE Losses SET PriorCarrier = @PriorCarrier, [Year] = @Year, Losses = @Losses WHERE @Record = Record COMMIT TRAN GO
Here's the error: Line 1: Incorrect syntax near 'UpdateLosses'.
Here's how I run it in Query Analyzer: exec UpdateLosses @Record=20, @PriorCarrier='John', @Year='2002', @Losses=234.32
I would be very grateful if someone could help me with a stored procedure syntax problem I want to insert the value "OrderTotal" into databasetable("Newtable") column "OrderTotal"(money (8)). The value can be returned from the page (Dim amount As Decimal = ShoppingCart.GetTotal() totalAmountLabel.Text = String.Format("{0:c}",amount) or returned by the function "ShoppingCart.GetTotal". This is the syntax i have tried, but I get the error message "Invalid column name OrderTotal" CREATE PROCEDURE SP_NewOrder(@CartID char (36),@CustomerID Varchar (50),@OrderTotal decimal(8)) AS INSERT INTO NewTable (FirstName,ProductID,OrderTotal) SELECT Customer.FirstName,Products.ProductID,Ordertotal From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE Customer.CustomerID=@CustomerID AND ShoppingCart.CartID = @CartIDGO Can anyone tell me where i am going wrong many thanks martin
Dear Forum, I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below: Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’. Is there a trick to putting in integers in a stored procedure?
I have the following stored procedure. The good thing is I finally got the syntax to the point where it doesn't blow up, but I was under the impression the "print" statement would print my result. In this case all I get is the statement ' 1 Row Affected ' which by the way is not the answer I was expecting.
Here's the code: ' CREATE PROCEDURE createevents AS
declare @myvariable varchar(700)
declare @myvar varchar(700)
begin Select @myvariable = d.id from table1 d, table2 dc where d.class = dc.class
If @myvariable > 0 begin Select @myvar =dp. class from table3 dp, table2 dc where dp.class = dc.class
If @myvariable >= 1 begin print @myvariable + (',') + @myvar end end
else begin print @myvariable + ('is empty') end end
I have a Stored Procedure that execute several tasks
If I write the SP en the following format :
quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS BEGIN SELECT ... UPDATE ... INSERT ... END
It saves ok in Stored Procedures folder but doesn't run ok only send the message = Command(s) completed successfully.
Otherwise If I write the SP in the following format :
quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS GO SELECT ... GO UPDATE ... GO INSERT ... GO
It run ok select/update/insert records but doesn't saves in Stored Procedures folder It only save the next part : quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS
Also, when the SP has only one task (let's say a Select), it works ok with AS BEGIN - END format
I'm trying to use a couple of variables in a stored procedure. Things workfine when I hard code the data into the variables and also work fine when Iuse the variable in the WHERE clause and hard code data for the othervariable. So, I think I have a syntax problem when trying to use"FrontPage.@FrontpageProduct" as seen in my example code below. I've triedmany variations... and either get syntax errors or end up with a result of"no records." If somebody could assist me with the proper syntax for a"table_name.@variable_name" reference it would be greatly appreciated.The following procedure is called from a VB/.asp page. It's for astorefront front page where product codes listed in the table "FrontPage"are used to pull product data from table "Products."=============================CREATE PROCEDURE dbo.frontpage@FrontpageProduct varchar,@FrontpageDay varcharASSELECT * FROM Products LEFT JOIN FrontPage ON Products.Code =FrontPage.@FrontpageProduct WHERE FrontPage.theDay = @FrontPageDayGO=============================Again, thank you in advance for any help.Dave
How do I pass my stored procedure value into my query? My first example works, in the second example my parameters are contained in the sql string. How do I expose parameter values to the inner sql string?
Thanks for any help.
Kevin
============================================== This works: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectTest] @StartDate datetime, @EndDate datetime AS BEGIN SET NOCOUNT ON; SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE DownloadDate BETWEEN @StartDate AND @EndDate END
This does not: INSERT INTO ReportTable (ItemDate, ProjectNo, ProjectName, Category, Type, Amount, RevExp) EXECUTE ('SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE [Project Number] > 0 AND DownloadDate BETWEEN @StartDate AND @EndDate ');
CREATE PROCEDURE dbo.IsFirmNickNameUnique @NickName varchar(12), @Found bit output AS IF ((SELECT COUNT(*) FROM Firm WHERE Firm.NickName = @NickName) = 0) BEGIN @Found = 0 END ELSE BEGIN @Found = 1 END
create proc dbo.sp_GetInvitationsHistoryDetails(@ExecID int, @OrgID int) as IF (@OrgID = 0) BEGIN select E.EventID,E.Description as Event,E.EventDate as Date I.Attending as [Att'g],NotAttending as [Not att'g],I.Bootcamp as [Maybe] I.Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID=@ExecID and NotForStats=0 ORDER BY E.EventDate DESC END ELSE BEGIN select E.EventID,E.Description as Event,E.EventDate as Date count(*) as Invited,SUM(CONVERT(smallint,I.Attended)) AS Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID IN (select ID FROM Executives WHERE OrganisationID=OrgID GROUP BY E.EventID,E.Description,E.EventDate ORDER BY E.EventDate DESC END
and i'm getting the following syntax errors when i check it -
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 10
Incorrect syntax near 'I'.
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 19
Incorrect syntax near 'count'.
Msg 156, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 23
Incorrect syntax near the keyword 'ORDER'.
Originally i just ran this sql from C# and it worked, obviously added the @ to the variables but it's basically the same.
I have 2 tables, table A and B. Table A has the following fields; Phone (nvchar), Fname (nvchar), Lname (nvchar), DNC (bit). Table B has one field, PhoneNo (nvchar). I would like to update the field DNC in Table A to True(1) if the values Phone in Table A = PhoneNo in Table B.
I tried this syntax but it never updated:
UPDATE dbo.A SET DNC = 1 WHERE (Phone = 'SELECT MAX dbo.B.PhoneNo FROM dbo.B')
These tables have over 100K records and I would like to make sure it runs through and checks every single record and not just the first 10000.
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
Can anyone see an error here? I am getting a general exception when call this and it is driving me crazy. What am I missing? The error is.....{"Incorrect syntax near 'UpdateStaffDept'." ... Any help is greatly appreciated! Below is the code calling the proc Thank you! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[UpdateStaffDept] @Co_Id INT, @CardNo VARCHAR(50), @DeptNo VARCHAR(50)
AS SET NOCOUNT ON
DECLARE @Error INT
SELECT @Error = @@Error IF @Error = 0 BEGIN
BEGIN TRANSACTION UPDATE Staff SET Dept_No = @DeptNo WHERE Co_Id = @Co_Id AND PC_Id IN (SELECT PC_ID FROM PC_Number WHERE PC_Number = @CardNo)
SELECT @Error = @@Error IF @Error <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Error END ELSE COMMIT TRANSACTION
END
Dim seldeptno As String seldeptno = ddlEditPosDept.SelectedValue Dim cmdChnDept As SqlCommand cmdChnDept = New SqlCommand("UpdateStaffDept", conEditPos) cmdChnDept.Parameters.Add(New SqlParameter("@Co_Id", SqlDbType.Int 10)) cmdChnDept.Parameters("@Co_Id").Value = compid cmdChnDept.Parameters.Add(New SqlParameter("@CardNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@CardNo").Value = lblEditPosCardNo.Text cmdChnDept.Parameters.Add(New SqlParameter("@DeptNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@DeptNo").Value = seldeptno cmdChnDept.ExecuteNonQuery()
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code: SELECT CASE WHEN <CONDITION> THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE)) ELSE 0 END AS CurrentDateAdj, Year(CurrentDateAdj) + '_' + 'some text and processing') FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
