Stored Procedure T -sql Syntax Errors

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


ADVERTISEMENT

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Controlling Errors In Stored Procedure

Jul 14, 2006

Hi everyone:
I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised.
Can it be done?
Thanks for any help.

View 2 Replies View Related

How Can I Trap Errors In A Stored Procedure?

May 26, 2004

My simple question:

Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.

The Setup:

I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.

I create a temporary table called #Results to hold the results of processing each record in the xml.

To process the xml I have a Cursor which loops over a SELECT from the xml.

For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.

When the cursor is done looping I 'SELECT * FROM #Results'.

I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.

I try to call this stored procedure using the following code:


int c = 0;

try
{
sqlAdapter.Fill( sqlDS );
}
catch( System.Data.SqlClient.SqlException )
{
c = sqlDS.Tables.Count;
}

The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.

Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?

I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.

View 7 Replies View Related

Dynamic Stored Procedure Errors

Aug 17, 2004

Hi

I am getting the following error

Syntax error converting the varchar value 'Select * from Residential WHERE Price BETWEEN ' to a column of data type int.

when running the following SP.



CREATE PROCEDURE testing
(
@Locationnvarchar(100)=NULL,
@TypeHomenvarchar(50)=NULL,
@MinPriceint=0,
@MaxPriceint=9999999999,
@Bedroomsnvarchar(2)=NULL,
@BathsSearchnvarchar(2)=NULL
)
AS

Declare @strSql char(255)
Set @strSql="Select * from Residential WHERE "

Set @strSql=@strSql + "Price BETWEEN " + @MinPrice + " AND " + @MaxPrice
If @Location is NOT NULL
Set @strSql=@strSql + ' AND city = ' + @Location

If @TypeHome is NOT NULL
Set @strSql=@strSql + ' AND Type = ' + @TypeHome

Set @strSql=@strSql + ' AND BDRM >= ' + @Bedrooms
Set @strSql=@strSql + ' AND BATHS <= ' + @BathsSearch
Set @strSql=@strSql + ' AND IDX = Y'

Exec(@strSql)



What is causing this error?

Thanks in advance

View 3 Replies View Related

Trapping Stored Procedure Errors

Jun 23, 2000

In Stored Procedures that have a group of statements wrapped in a Begin and End Statement What is the best way of trapping errors ?

View 1 Replies View Related

How To Handle Errors In Stored Procedure

Feb 23, 2004

How to handle errors in stored procedure ?

View 1 Replies View Related

Trapping Errors In Stored Procedure

Jul 20, 2005

Hi there,I am converting a large PL/SQL project into Transact-SQL and have hitan issue as follows:I have a PL/SQL procedure that converts a string to a date. Theprocedure does not know the format of the date in the string so ittries loads of formats in converting the string to a date until itsucceeds.After trying each potential format it uses the Oracle 'EXCEPTION WHENOTHERS' construct to trap the failure so it can try another format.Is it possible to do this with SQLServer ? If I do a CONVERT and it isnot one of the standard formats it fails. This is part of a backgroundscheduled process and I cannot afford the procedure to bomb out.I suspect the answer is I cannot do this and will need to impose somecontrol over the string being received (from various externalsystems!!) to ensure it is a specific known format. Even if I know itwill be one of the known SQLServer formats this will not be enoughsince if the first one I try is not correct the process will crash.Any ideas ?Thanks

View 2 Replies View Related

Incorrect Syntax When There Appears To Be No Syntax Errors.

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

Stored Procedure Syntax

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

Does This Stored Procedure Syntax Look Okay?

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

STORED PROCEDURE Syntax

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

Need Help With A Stored Procedure Syntax

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

Stored Procedure Syntax Help

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

Stored Procedure Syntax

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

Getting Permission Errors On Using Webclient In CLR Stored Procedure.

Jan 11, 2008

 Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or
aggregate "sp_LeadSend": System.Security.SecurityException: Request for
the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: at System.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.  

View 2 Replies View Related

Stored Procedure Syntax Problem

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

Stored Procedure Syntax Problem

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

Stored Procedure Syntax Problem

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

Syntax Trouble With Int In Stored Procedure

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

Stored Procedure Syntax - Value Not Returned

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

Stored Procedure Syntax Problem

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

Stored Procedure Syntax Problem

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

Syntax To Use Stored Procedure Parameter

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

Stored Procedure Syntax Problem

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

Stored Procedure Not Updating Database (no Errors Appearing)

Dec 1, 2003

Hi All,

I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen



ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int)
AS
BEGIN

UPDATE dbo.PaymentDeleted
SET EmpID_WhoDeleted = @EmpID_WhoDeleted
WHERE PmtDeletedID = @PmtDeletedID

UPDATE dbo.PaymentDeleted
SET Reason_Deleted = @Reason_Deleted
WHERE PmtDeletedID = @PmtDeletedID

END



Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Me.Validate()
If Me.IsValid Then
Dim DR As SqlClient.SqlDataReader

Dim strPmtID As String
strPmtID = lblPmtIDDel.Text

Dim MySQL As String
MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'"
Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1)
SqlConnection1.Open()
DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
SqlConnection1.Close()


Dim strDeletePmt As String
strDeletePmt = lblPmtIDDel.Text

Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure

Dim myParam As SqlClient.SqlParameter
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int))
myParam.Direction = ParameterDirection.Input
myParam.Value = lblPmtIDDel.Text

myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@EmpID_WhoDeleted", SqlDbType.VarChar))
myParam.Value = txtEmpIDDelete.Text

myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@Reason_Deleted", SqlDbType.VarChar))
myParam.Value = txtDeleteComments.Text

SqlConnection1.Open()
cmd.ExecuteNonQuery()
SqlConnection1.Close()

End If
Response.Redirect("PaymentVerification.aspx")
End Sub

View 1 Replies View Related

Is There A Way To Capture All Bulk Insert Errors From Within A Stored Procedure?

Sep 28, 2007

Hi all!!

I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?


Running this...


BULK INSERT Customers

FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')


Produces this (notice column name at the end of the first error)...


Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".




Running this (similar to code in my stored procedure)...


BEGIN TRY

BULK INSERT Customers

FROM 'c: estcustomers.txt'

WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH



Produces something similar to this (which is useless)...
...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

View 3 Replies View Related

Variable Type Errors When Calling Stored Procedure

May 12, 2008

I currently have a stored procedure that is defined as follows:


CREATE PROCEDURE UpdateSyncLog

@TableName char(100),

@LastSyncDateTime datetime,

@ErrorState int OUTPUT


I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:

exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState

Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).

I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32

The parameter mappings are as follows:

Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1

Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1

The error I am getting when running this execute sql task is as follows:


Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.

What gives?

View 2 Replies View Related

MSDE Update SQL Stored Procedure Syntax

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

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 View Related

Alias &#043; Stored Procedure Syntax Error

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

Syntax Error When Building Up A Where Clause In Stored Procedure

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

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors

Apr 6, 2007

We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.






Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}



return (IEnumerable)myList;

}

}



When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.






Code SnippetEXEC GetObject 'Item'



Is there some sorf of trick I am missing?



Thank you!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved