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
Hi, I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value. Anybody could help me with the problem? Thanks.
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
I have an asp.net 1.1 website that uses sql server 2000 and vb.I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run itin the query analyzer the correct value is returned. Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB
' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID"))
' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID)
Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer
' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID)
Dim parameterOrderCustID As New SqlParameter("@customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID)
'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()
' Return the customer_id (obtained as out paramter of SPROC)
If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If
End Function
the stored procdure is CREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID] ( @order_id int, @customer_id int OUTPUT ) AS
/* Return the customer_id from the Orders. */ SELECT @customer_id = customer_id
FROM nlb_Orders
WHERE order_id = @order_id GO
I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2. Any ideas would be appreciated ThanksPete
The following query only brings back too much data in the table - there are duplicates and the lab it shows is wrong. The problem is: AND a.calLab = f.ID in the Where statement. Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0. tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null I need to get the rest of the data. Any suggestions? Thanks..... Zath
SELECT a.assignedID, b.Manufacturer, a.modelNumber, a.serialNumber, e.equipmentType, a.Description, c.Location, d.Status, a.modifiedDate, a.modifiedBy, a.Notes, a.Picture, f.LabName, a.calibrationRequired, a.calDate, a.CalDueDate, a.assetNumber, a.ID FROM tblEquipments a, tblManufacturers b, tblLocation c, tblStatus d, tblEquipment_Type e, tblLabs f WHERE a.manufacturer = b.manufacturerID AND a.location = c.locationID AND a.Status = d.statusID AND a.EquipmentType = e.ID AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0) ORDER BY a.ID
I have a stored procedure that selects * from my table, and it seems to be working fine: USE myDB GO IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL DROP PROCEDURE GetAll GO CREATE PROCEDURE GetAll AS DECLARE ref_cur Cursor FOR SELECT * FROM myTable Open ref_cur FETCH NEXT FROM ref_cur DEALLOCATE ref_cur
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values. Can anyone help me finish this?public class dbGet_base { public dbGet_base() { _requestId = 0; }
public dbGet_base(Int64 RequestId) { this._requestId = RequestId; getDbValues(RequestId); } public void getDbValues(Int64 RequestId) { getDbValues(RequestId, "GetAll"); } public void getDbValues(Int64 RequestId, string SP_Name) { using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"])) using (SqlCommand Command = new SqlCommand(SP_Name, Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId; Command.Parameters.Add(?? } }
@UserIdvarchar(50), @Passwordvarchar(50) AS SELECT tblAdvertisers.UserID, tblAdvertisers.Password FROM tblAdvertisers WHERE ((tblAdvertisers.UserID = @UserId) AND (tblAdvertisers.Password = @Password))
I can run it in Query Analyzer and it returns one record as it should. I want it in ASP.NET to return the amount of rows that are effected, e.g that the login is correct.
The code I have is:
public bool ValidateUserIdAndPassword(string userId, string password) { sqlCommand.CommandType = CommandType.StoredProcedure; //the name of the stored procedure sqlCommand.CommandText = "spValidateUserIdAndPassword";
SqlParameter myParam;
//add the param's to the SP
//userId information for the user myParam = new SqlParameter("@UserId", SqlDbType.VarChar, 50); myParam.Value = CStr(userId); sqlCommand.Parameters.Add(myParam);
//password information for the user myParam = new SqlParameter("@Password", SqlDbType.VarChar, 50); myParam.Value = CStr(password); sqlCommand.Parameters.Add(myParam);
I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?
I am using the following stored procedure to insert a value into the database. I am new to stored procedures so I need help. Basically this stored procedurewill only insert a value for "CustomerName" if it is not a duplicate value. So I noticed in My C# when I call this stored procedure, a negative one "-1"is returned if the insert is not performed because of a duplicate value. Is that correct? Should I be getting back a negative one "-1" ? You seeI thought that a Zero "0" would be returned if the insert was not performed.Not a negative one? SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[CustomerCreate]( @CustomerDescription nvarchar(100), @CustomerName nvarchar(100), @LastUpdateDate datetime, @LastUpdateUser nvarchar(32), @inserted_record smallint output) AS if exists (select 1 from Customer where CustomerName = @CustomerName) BEGIN set @inserted_record = 0 END ELSE BEGIN INSERT INTO Customer ( CustomerDescription, CustomerName, Active, LastUpdateDate, LastUpdateUser ) VALUES ( @CustomerDescription, @CustomerName, @LastUpdateDate, @LastUpdateUser ) END
I am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.
I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?
Private Sub Form_Open(Cancel As Integer) dim ...
Set add_bag_results = Nothing With add_bag_results .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "spSampling_add_bag_results" .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) '.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1)) .Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n) .. Set rs_add_bag_results = .Execute End With
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :( Syntax error converting the varchar value 'ABC' to a column of data type int. SQL file ====================== DECLARE @S AS VARCHAR(1000) EXEC @S = PDT.DBO.SP_RPT 'ABC' SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S ====================== Stored Procedure ====================== CREATE procedure sp_RPT( @array varchar(1000) ) AS DECLARE @content AS VARCHAR(2000) SET @content = 'RPT: ' + @array + ' loaded successfully ' SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array) GO
Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNThese are the results that are returnedDateCreated OrderID UserID OrderTotal Sent Delivered Paid CustomerName ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 14:49:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNthen there is no problem. I get:41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(2 row(s) returned)
Hi, I need to write a select query that will run all returned results through a separate stored procedure before returning them to me. Something like.... SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID() ......and run all the selected userids through a stored procedure like..... CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate()) Can anyone help me to get these working togethsr in the same qurey?
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks. Query SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.
Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.
But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.
I have a novice question. How does one suppress result sets returned from a stored procedure?
I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.
Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.
The purpose of procA is to query several subsystems and identify which ones need attention.
Cursor While Loop exec @ProcName @ObjectName,@userID,@syncDate set @recs = @@rowcount;
I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5..... (Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
This is probably a very simple question but i would appreciate some help I have the following stored procedure and I am getting the error message "The objects "aspnet_Users" And "aspnet_Users" in the FROM clause have the same exposed names. Use correlation names to distinguish them" Can anyone tell me where I am going wrong. many thanks Martin ALTER PROCEDURE [dbo].[SP_CreateOrder](@CartID char (36),@UserId Varchar (50),@OrderTotal decimal (8)) AS Declare @OrderID int INSERT INTO Orders (UserID,Name,Address1,Address2,City,State,Country,Zip,OrderTotal) SELECT aspnet_Users.UserId,aspnet_Users.Name,aspnet_Profile.Address1,aspnet_Profile.Address2,aspnet_Profile.City,aspnet_Profile.State,aspnet_Profile.Country,aspnet_Profile.Zip,@OrderTotal FROM aspnet_Users,aspnet_Profile Join aspnet_Users On aspnet_Profile.UserId=aspnet_Users.UserId WHERE asp_Users.UserId=@UserId SET @OrderID = @@IDENTITY INSERT INTO OrderDetails (UserId,OrderID,ProductID,Name,Cost,Quantity) SELECT @UserID,@OrderID,Products.ProductID,Products.Name,Products.Cost,ShoppingCart.Quantity From aspnet_User,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE aspnet_User.Userid=@UserId AND ShoppingCart.CartID = @CartID EXEC SP_DeleteShoppingCart @CartIDSELECT @OrderIDGO
Does this Stored Procedure Syntax look okay?I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag 'Add Input Parameters to date and from date variables - dtFrom and dtTo objComm.Parameters.Append.CreateParameter("@dtFrom", adDate, adParamInput, 9) objComm.Parameters.Append.CreateParameter("@dtTo", adDate, adParamInput, 9) CREATE PROCEDURE editorTAmonthlyReport @FromDate DateTime, @ToDate DateTime As
Select U.UserID, U.Title, U.FirstName, U.Surname, TAU.UserPosition, U.Email, A.AgencyName, A.AddressLine1, A.AddressLine2, A.Town, A.County, A.Postcode, C.Country, Coalesce(U.Phone,A.Phone) As Phone, TAU.DateOfCreation From [user] U LEFT OUTER JOIN MyTrafalgarUser MTU ON MTU.userID=U.UserID INNER JOIN TravelAgencyUser TAU ON U.UserID=TAU.UserID INNER JOIN Agency A ON TAU.AgencyID=A.AgencyID INNER JOIN Country C ON A.CountryID=C.CountryID Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @FromDate) < 0 ) and datediff(d,TAU.DateOfCreation, @ToDate) > 0 ORDER BY TAU.DateOfCreation GO
Hi All I will like to allow guess users to add items to the shopping cart (using a temp shoppingcartID): ' If user is not authenticated, either fetch (or issue) a new temporary cartID If Not context.Request.Cookies("MMDotNetID") Is Nothing ThenReturn context.Request.Cookies("MMDotNetID").Value.ToString Else ' Generate a new random GUID using System.Guid ClassDim tempCartId As Guid = Guid.NewGuid() ' Send tempCartId back to client as a cookie context.Response.Cookies("MMDotNetID").Value = tempCartId.ToString() context.Response.Cookies("MMDotNetID").Expires = DateTime.Now.AddMonths(1) ' Return tempCartId Return tempCartId.ToString ' If user is authenticated I use the userID to store the items in the Shopping Cart 'If the user logon after adding some items to the cart I need to Migrate the old items. 'But my STORED PROCEDURE is not working properly:ALTER PROCEDURE MM_SP_ShoppingCartMigrate ( @OriginalCartId nvarchar(50), @NewCartId nvarchar(50)) As IF EXISTS(SELECT * FROM MM_SP_ShoppingCart WHERE CartID = @NewCartId) BEGIN
I need help in writing a stored procedure on SQL Server 2000.Basically the stored procedure's primary task is to generate invoicerecords and insert the records in a invoice table. In order togenerate the invoice records, I have an initial table which arebasically Day Records. The task that I would like to complete is toselect all records from that initial table and I guess put them into atemp table. Now that i have my temp table, I would like to loop thruthe table record by record, and do inserts in the invoice table. Icant seem to figure out the syntax since I am somewhat weak in TSQLProgramming. I would appreciate any assistance on this forum or to myemail. Also If you need some pseudocode for the process or the DDL forthe initial table and the invoice table, I can definitely post that inthe forum possibly in the next thread. Please advise, I would need afull written syntax to get me started since i have some otherprocesses that I would need to build using the template. Thanks again.
I'm enhancing a large ASP application by replacing raw SQL statementsin the code with calls to stored procedures. I've seen thisrecommended in many places to increase SQL Server effieicency.Replacing select statements is going fine but I hit a sticking pointwhen trying to replace an update statement.Currently, I use this kind of statement a lot in my ASP:sql = "update"sql = sql & " field1 = value1"sql = sql & ",field2 = value2"If Len(value3) Then sql = sql & ",field3 = value3"sql = sql & " where ...., etc, etcThe important part here is checking if "value3" has a value beforeinserting it into my SQL statement. So how can I put this updatestatement into a stored procedure, pass in values for value1, value2,and value3, and leave off the value3 part of the update if value3comes in as null.Thanks all.
Hi AllIm trying to use the code at the bottom of this message inside my storedprocedure and when i execute the procedure in query analyzer i get thefollowing error:Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'H'.This error happens a few times and im pretty sure it is because the selectstatement needs the ' around the data that is being searched. This is whatthe select statement looks like for me:SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMSOR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRVAnd im pretty sure it is failing because sql wants the select statement tolook like this:SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID ='HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'Am i thinking along the right lines with this ? If so does anybody know of away that i can put the ' mark around the the data that is being searched for? Any help is greatly appreciatedThanksCREATE PROCEDURE [dbo].[TestSP]@MachineName VarChar(50),@UserName VarChar(50)ASDECLARE @MachineLength Char(2) /* Local Machine Name Length */DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */DECLARE @SqlStr VarChar(300) /* SQL Select String */DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */SET @SrchInt = 1SET @MachineLength = Len(@MachineName)SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = 'WHILE @SrchInt <= @MachineLengthBEGINSET @CurrMach = LEFT(@MachineName,@SrchInt)IF @SrchInt = 1BEGINSET @SqlStr = @SqlStr + LEFT(@MachineName,1)ENDIF @SrchInt > 1BEGINSET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMachENDSET @SrchInt = @SrchInt + 1PRINT @SqlStrENDEXEC (@SqlStr)GO
This is probably a simple question but i would appreciate some help. I have a stored procedure that i want want first to check if a customer exists in the customer table and if a customer doesnt exist to insert values. I know that there are other ways to do this but i was wondering if itwas possible to do this only within the stored procedure. I have been using: SELECT COUNT(*)FROM customersWHERE customerID=@customerID IF COUNT(*)=0............ (Insert statement follows) But the new customer values are not being inserted.Can anyone tell me where I am going wrong or suggest a diferent wat of doing this? Many Thanks martin BY the way the rows seem to be counted as the information is inserted if i use If COUNT(*)>0 but the insert is not happening with the code above.
I'm having a weird issue with a stored procedure I wrote. I can run it in Query Analyzer and no problem, but when I run it from the web I get an error.
Heres the stored procedure: CREATE PROCEDURE dbo.UpdateLosses ( @Record int, @PriorCarrier varchar(50), @Year varchar(4), @Losses money ) AS BEGIN TRAN UPDATE Losses SET PriorCarrier = @PriorCarrier, [Year] = @Year, Losses = @Losses WHERE @Record = Record COMMIT TRAN GO
Here's the error: Line 1: Incorrect syntax near 'UpdateLosses'.
Here's how I run it in Query Analyzer: exec UpdateLosses @Record=20, @PriorCarrier='John', @Year='2002', @Losses=234.32
I would be very grateful if someone could help me with a stored procedure syntax problem I want to insert the value "OrderTotal" into databasetable("Newtable") column "OrderTotal"(money (8)). The value can be returned from the page (Dim amount As Decimal = ShoppingCart.GetTotal() totalAmountLabel.Text = String.Format("{0:c}",amount) or returned by the function "ShoppingCart.GetTotal". This is the syntax i have tried, but I get the error message "Invalid column name OrderTotal" CREATE PROCEDURE SP_NewOrder(@CartID char (36),@CustomerID Varchar (50),@OrderTotal decimal(8)) AS INSERT INTO NewTable (FirstName,ProductID,OrderTotal) SELECT Customer.FirstName,Products.ProductID,Ordertotal From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE Customer.CustomerID=@CustomerID AND ShoppingCart.CartID = @CartIDGO Can anyone tell me where i am going wrong many thanks martin
Dear Forum, I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below: Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’. Is there a trick to putting in integers in a stored procedure?
I have 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