Correct Syntax For An Update Stored Procedure
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
ADVERTISEMENT
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 23, 2004
I am using @strsql to construct the where condition for a select query.
Can you please correct my syntax.
DECLARE @strsql nvarchar(2000)
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
Thank you very much.
View 9 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
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
Nov 3, 2007
Hi
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first:
I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like.
To add a simple image to a given album, I am trying to do the following:
* Retrieve name, description from the UI
* Insert a new row into images with this data
* Get the ID from the newly created row
* Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
View 9 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
Sep 25, 2006
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
View 2 Replies
View Related
Mar 15, 2007
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
and (r.id = 'anything')
View 7 Replies
View Related
Feb 25, 2008
Hi.
This is a Stored proc I am working on
I dont know much about stored procs.
This is what I am trying to achieve
The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetNextAction]
(
@Code char(10),
@Track varchar(30),
@NextAction ntext output
)
AS
BEGIN
SET NOCOUNT ON;
Declare @ID int;
@ID = Select Sequence from [dbo].[Track] where Code=@Code;
Declare @nextCode varchar;
@nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1);
@NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode;
return @NextAction;
END
GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this.
Thanks
View 4 Replies
View Related
Dec 3, 2006
hi there
RS2005 - Layout tab
I have a field that says
txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))
I want another field that says
txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))
obviously this is going to error
how would I do this please
thanks
View 19 Replies
View Related
Oct 19, 2007
select CONVERT(VARCHAR, stdate, 'YYYY') startyear
from tbl_emp
i want output : 2007
How can i get?
View 5 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
Jan 29, 2008
If someone can show me the correct syntax for the right side of the code below, I'd appreciate it.
srcMoreInfo.InsertParameters("Address").DefaultValue = DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text
View 11 Replies
View Related
Jun 21, 2000
CREATE PROCEDURE test1
@spname nvarchar(20),
@tblname nvarchar(50)
AS
declare @sqlrun varchar(8000)
set @sqlrun = "CREATE PROCEDURE @sp_name AS CREATE TABLE @tblname
(MsdsId [nvarchar] (5),
IngredId [nvarchar] (2),
Agency [nvarchar] (10),
AgencyText [nvarchar] (4) ,
nName [nvarchar] (40))"
eXEC sp_ExecuteSql @sqlrun
WHATS WRONG IN THIS CODE ?
---------------------------------
PS: I'm getting this error:
-------------------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
View 2 Replies
View Related
Feb 27, 2007
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES
XYZ Company
ABC Limited Liability Company
ZZZ Corporation
KKK Inc.
ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS
Company
Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES
XYZ Company
ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT *
FROM [Names of Companies]
WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
Can anyone recommend a better way to do this?
Thanks for all your help!
View 8 Replies
View Related
May 18, 2006
Somehow I'm not getting dtsrun to work.
The server is local
The name of the package is ImportHosttrncs. It is saved under Local Packages.
There is no password.
Is this syntax correct?
dtsrun /Slocalhost /PImportHosttrncs
View 1 Replies
View Related
Apr 11, 2008
What is the correct syntax for dropping a column if it exists?
Cant seem to get it to work. This is SQL 2005 if it makes a difference.
ALTER TABLE Reports_PI_Recent
IF EXISTS (DROP COLUMN RollingAverage )
View 3 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
