Stord Procedure Syntax Problem
Nov 27, 2005
This is probably a very simple question but i am having problems with stored procedure syntax.
I want to insert fields with preset values (DateCreated/OrderVerified)
and two values (Name/ Address1) from a databletable called "Customer" and insert both of these into a datatable called "Orders"
I have tried the following syntax but it is not working. Can anyone tell me where i am going wrong
CREATE PROCEDURE SP_CreateOrder
(@CustomerID Varchar(50))
ASINSERT INTO Orders (DateCreated, Verified)VALUES ( GETDATE(), 0,)AND INSERT Into Orders (Name, Address)SELECT (Customer.Name, Customer.Address) FROM Customer
WHERE CustomerID = @CustomerID
Many thanks martin
View 1 Replies
ADVERTISEMENT
Nov 30, 2000
I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
View 1 Replies
View Related
Jan 23, 2008
need help how to
change the shift order in my stord prosege backward
on the field "shifttype"
not like this
shifttype
---------------------------------------------------------
111111 2008-02-24 Sunday 1
111111 2008-02-23 Saturday 2
111111 2008-02-22 Friday 3
111111 2008-02-21 Thursday 4
111111 2008-02-20 Wednesday 5
111111 2008-02-19 Tuesday 6
111111 2008-02-18 Monday 7
111111 2008-02-17 Sunday 8
111111 2008-02-16 Saturday 1
111111 2008-02-15 Friday 2
111111 2008-02-14 Thursday 3
111111 2008-02-13 Wednesday 4
111111 2008-02-12 Tuesday 5
111111 2008-02-11 Monday 6
111111 2008-02-10 Sunday 7
---------------------------------------------------------------------------------------
i need it like this
shifttype
------------------------------------------------------
111111 2008-02-24 Sunday 8
111111 2008-02-23 Saturday 7
111111 2008-02-22 Friday 6
111111 2008-02-21 Thursday 5
111111 2008-02-20 Wednesday 4
111111 2008-02-19 Tuesday 3
111111 2008-02-18 Monday 2
111111 2008-02-17 Sunday 1
111111 2008-02-16 Saturday 8
111111 2008-02-15 Friday 7
111111 2008-02-14 Thursday 6
111111 2008-02-13 Wednesday 5
111111 2008-02-12 Tuesday 4
111111 2008-02-11 Monday 3
111111 2008-02-10 Sunday 2
Code Snippet
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int NOT NULL,
[ShiftType] int NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL
)
INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103) UNION ALL
SELECT 222222,2,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 333333,3,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,-1*spt.number,Enddate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)
--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id= 8 and DATENAME (dw,ShiftDate )='Saturday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts
--select datediff(ms,@g,getdate())
View 5 Replies
View Related
Jun 27, 2006
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
View 2 Replies
View Related
Dec 7, 2006
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
View 2 Replies
View Related
Jun 8, 2007
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
DECLARE @CountItemsInOldCard intSELECT @CountItemsInOldCard = MM_SP_ShoppingCart.Quantity
FROM MM_SP_ShoppingCartWHERE CartID = @OriginalCartId
DECLARE @CountItems intSELECT @CountItems = MM_SP_ShoppingCart.Quantity
FROM MM_SP_ShoppingCartWHERE CartID = @NewCartId
UPDATE MM_SP_ShoppingCart
SET Quantity = @CountItems + @CountItemsInOldCardWHERE CartID = @NewCartId
END
ELSE
UPDATE
MM_SP_ShoppingCart
SET
CartID = @NewCartId
WHERE
CartID = @OriginalCartId
DELETE FROM MM_SP_ShoppingCart
WHERE CartID = @OriginalCartId
RETURN
'Any help
View 2 Replies
View Related
Jul 20, 2005
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.
View 7 Replies
View Related
Jul 20, 2005
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.
View 3 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Jul 27, 2006
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.
View 2 Replies
View Related
Jun 3, 2005
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
Any suggestions?
View 4 Replies
View Related
Dec 2, 2005
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
View 1 Replies
View Related
May 9, 2006
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?
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert( @Title varchar(50), @_Date datetime, @Venue varchar(50), @HeadlinerID int(4), @Opener varchar(150), @Doorstime varchar(50), @Showtime varchar(50), @Price varchar(50), @Onsaledate datetime, @Ticketvendor varchar(50), @TicketURL varchar(150), @Description varchar(1000),
)AS
INSERT INTO shows( Title, _Date, Venue, HeadlinerID, Opener, Doorstime, Showtime, Price, Onsaledate, Ticketvendor, TicketURL, Description)VALUES( @Title, @_Date, @Venue, @HeadlinerID, @Opener, @Doorstime, @Showtime, @Price, @Onsaledate, @Ticketvendor, @TicketURL, @Description )GO
View 3 Replies
View Related
Feb 5, 2004
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
'
Thanks in advance for any help.
View 5 Replies
View Related
Jun 23, 2008
MS SQL SERVER 2005
My problem is the next :
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
Wich could be the problem?
Thanks
JG
View 7 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Jan 3, 2008
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 ');
View 3 Replies
View Related
Jul 7, 2006
I get a syntax error from the following:
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
RETURN
View 1 Replies
View Related
Apr 22, 2008
Hi, i'm writing this stored procedure -
Code Snippet
USE [TheHub]
GO
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.
Any ideas??
View 6 Replies
View Related
May 10, 2004
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.
View 1 Replies
View Related
Nov 24, 2005
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
View 3 Replies
View Related
Jun 21, 2008
Hi,
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'.
-----------------------------------------------------------
@supplierFID int,
@supplierID int,
@LANo nvarchar(15) OUTPUT,
@suppName nvarchar(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tb_LA.LANo, tb_supplier.suppName AS OrderSupplier
INNER JOIN tb_supplier ON tb_LA.supplierFID=tb_supplier.supplierID
Order by tb_LA.LANo
END
------------------------------------------------------
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???
Many thanks
View 5 Replies
View Related
Aug 9, 2006
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)
View 1 Replies
View Related
May 26, 2000
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
thanks
hiss
View 2 Replies
View Related
Feb 17, 2008
Hi all,
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.
Please help and advise.
Thanks,
Scott Chang
View 18 Replies
View Related
Dec 26, 2007
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:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
View 1 Replies
View Related
May 14, 2008
The following query works fine in query analyzer, but when I add it to my stored procedure I receive an error 156. How do I work around this?
select distinct(dateposted)
from billingprocedures bp1,
billingprocedureordercomponentvalues bpocv,
ordercomponentvalues ocv
where bp1.billingid = @billingid
and bp1.procedureid = bpocv.billingprocedureid
and bpocv.ordercomponentvalueid = ocv.ordercomponentvalueid
Thanks,
Bryan
View 12 Replies
View Related
May 4, 2015
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
View 2 Replies
View Related
May 20, 2008
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
View 7 Replies
View Related
Jul 20, 2005
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
View 5 Replies
View Related
Jul 12, 2007
Hi All,
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.
Thanks in advance,
Anand
View 4 Replies
View Related
Dec 14, 2003
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
View 3 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
