When I run my package in my local computer, everything is fine. But when I depoly this package to test server, strange issue happen. Because this package will call a store procedure.So I create this store procedure by manually before I run package on test server just like I did this on my computer. But when I start to run this package on test server about 10 minutes,it always failed because it can not find the store procedure. And then I check this store procedure, it is not there. So I recreate this store procedure again, and rerun package, same issue happen. If I don't run package just create store procedure, store procedure will not disappear. It looks that store procedure will be dropped during running this package. But the thing is the package didn't do anything for deleting store procedure. So what's the reason cause that? Any tip or suggestion are welcome. Thank you.
Hello all, I have the following Stored Procedure that has been working perfectly for the last year: CODE ==================================================== DELETE FROM tblReportMainMembers INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444' UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users. But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again. Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it. Any ideas? Thanks in advance.Richard M.
I'm receiving NULL for all output parameters here:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE Accounts_ValidateCredentials2@Account_Name varchar(50),@Account_Password varchar(50),@IPAddress varchar(15),@Time datetime,@retAccID bigint OUTPUT,@retAccDisplayName varchar(50) OUTPUT,@retAccStatus int OUTPUT,@retAccEmail varchar(128) OUTPUT,@retAccLastIP char(15) OUTPUT,@retAccLastAccess datetime OUTPUT,@retAuthStatus int OUTPUTASBEGINSET NOCOUNT ON;SELECT @retAccID AS [Account_ID], [Account_Name],[Account_Password],@retAccStatus AS [Account_Status],[Account_ActiveFlag],@retAccEmail AS [Account_EMail],@retAccLastIP AS [Account_LastIP],@retAccLastAccess AS [Account_LastAccess]FROM [Accounts] WHERE [Account_Name] = @Account_Name END
I have this problem that has me banging my head on the wall :(
I have a stored procedure - has a cursor & a temp table in it.
Problem is - it works, but it will only run once.
If I close the query manager page & open a new one it will run again - giving the output recordset. When calling it from an ASP page - it runs fine once, then returns an empty recordset. If I kill the ASP sessions - it will run again - but only once unless I kill the ASP sessions again.
I did a test to return the @@Fetch_status and it is coming back -1 on any second attempts to run the procedure per session. But, at the end of the procedure I close the cursor and deallocate the cursor.
Any clues on why this cursor would still remain alive when the procedure is recalled by the same session?
this is my problem in stored procedure 1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE 2) I need to see only the employee than in the table v_un 3) i see all the employee 4) problem in this line ( [new date] = @mydate2,[new_shift2] = ) i see 2 rows of result from fields [new date] + ,[new_shift] how to see only 1 field for each field
new date new_shift new date empid name --------------------------------------------------------------- 2007-12-01 99 2007-12-02 99 2568947 aaa 2007-12-01 99 2007-12-02 99 2845209 bbbb 2007-12-01 99 2007-12-02 99 4807756 ccc 2007-12-01 99 2007-12-02 99 9819590 ddd 2007-12-01 99 2007-12-02 99 10055648 eee 2007-12-01 99 2007-12-02 99 10815413 ffff 2007-12-01 99 2007-12-02 99 11070042 gggg 2007-12-01 99 2007-12-02 99 11162047 hhh ------------------------------------------------------------------------------- i need to see only one for each field
new date new_shift id name --------------------------------------------------------------- 2007-12-01 99 2568947 aaa 2007-12-01 99 2845209 bbbb 2007-12-01 99 4807756 ccc 2007-12-01 99 9819590 ddd 2007-12-02 99 10055648 eee 2007-12-02 99 10815413 ffff 2007-12-02 99 11070042 gggg 2007-12-02 99 11162047 hhh
Code Block DECLARE @yeara [varchar](4) DECLARE @month1 [varchar](2) DECLARE @day1 [varchar](2) DECLARE @day2 [varchar](2) DECLARE @day3 [varchar](2) DECLARE @mydate1 [datetime] DECLARE @mydate2 [datetime] set @yeara ='2007' SET @month1 ='12' Set @day1 ='1' Set @day2 ='2' set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103) set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103) SELECT SilokE CASE WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 2 else 99 END, [new date] = @mydate2,[new_shift] = CASE WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 12 ))) THEN 2 else 99 END, SilokE.empid, SilokE.Fname FROM SilokE
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hi Everyone,I've been battling this for two days with no luck. I'm using SQLServer 2000.Here's the mystery: I've got a stored procedure that takes a singlevarchar parameter to determine how the result set is sorted. Here itis:CREATE PROCEDURE spDemo @SortField varchar(30)ASSELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDescFROM ActivityLogORDER BY CASE @SortFieldWHEN 'dtmTimeStamp' THEN dtmTimeStampWHEN 'strEmpName' THEN strEmpNameWHEN 'strOld' THEN strOldWHEN 'strNew' THEN strNewWHEN 'strActionDesc' THEN strActionDescENDGOWhen I execute the stored procedure in the Query Analyzer, it worksperfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or'strNew'. When passing in any of the other three possible values for@SortField, I get the following error:Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4Syntax error converting datetime from character string.Now instead of executing the stored procedure, if I copy and paste theSELECT statement directly into the Query Analyzer (after removing theCASE statement and manually trying each different value of @SortField),it works fine for all five possible values of SortField.Even though the error points to Line 4 of the stored procedure, itseems to me that the CASE statement is causing problems for some, butnot all, values of the @SortField parameter.Any ideas?Thanks,Jimmy
I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND
SQL Server 2000, VB.NET 1.1. Here's the dummy sp: CREATE PROCEDURE am_user.spTestCrap ( @maxFloodControl int, @int_memID int) ASset nocount onselect @maxFloodControl, @int_memIDreturn 62GO Here's some VB.NET code that does not work: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strConn As String = "server=amdb1;UID=am_User;PWD=thepwd;DATABASE=thedb;pooling=true" Dim objConn As New SqlClient.SqlConnection(strConn) objConn.Open() Dim objComm As SqlClient.SqlCommand Dim nReturnedValue As Integer Dim memID As Integer 'This is a "root" level message objComm = New SqlClient.SqlCommand("am_User.spTestCrap", objConn) objComm.CommandType = CommandType.StoredProcedure objComm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int) objComm.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue objComm.Parameters.Add("@maxFloodControl", CType(0, Integer)) objComm.Parameters.Add("@int_memID", memID) Try objComm.ExecuteNonQuery() If Not IsDBNull(objComm.Parameters("@RETURN_VALUE").Value) Then nReturnedValue = CType(objComm.Parameters("@RETURN_VALUE").Value, Integer) End If Catch MsgBox(Err.Description) End Try End Sub The error I get back in the message box is: "Procedure 'spTextCrap' expects parameter @maxFloodControl", which was not supplied." Why the BLEEP not? Didn't I supply it? What the BLEEP am I missing? TIA,
anyone know where the stored procedure wizard went in sql 2005? in 2000 it was so easy to check the type of SP you wanted and then it automatically wrote all the fields and value tsql for that - literally took 1 minute to create an insert update delete stored proc on any table. i don't see that in sql 2005 let me know, if you know. thanks - Jeff
I have installed Standard Sql 2000 . However i don't find the ex stored Procedures xp_sqlinventory and sp_sqlregister .. Can anyone Point out here i can get them?
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field). I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string: select product_code, CASE can_image_en ?? When (can_image_en LIKE '%.jpg') then can_image_en Else 'no_image_EN.jpg' End as can_image_en, none of these do the trick either (some are bad syntax obviously): ? When (can_image_en LIKE '%.jpg') then can_image_en ? When LIKE '.jpg' then can_image_en ? When '%.jpg' then can_image_en ? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en When '%.jpg%' then can_image_en Else 'no_image_EN.jpg'
Hello. You can compile a proc with a non-existant table. proc compiles do fail though on exsiting tables with non-existing columns.
Anyways, question is, how can you scan thru all procedure code to find tables that do not exist in the database? Is there any new DM feature that provides this info? Meaning, if I have a proc and inside it, refer to a table name that does not exist, I want to know the proc will not execute clean. Can the deferred name resolution be turned off somehow?
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
Hi all, I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters??? Thanks. Kabir
I have asp.net web application which interface with SQL server, I want to run store procedure query of SQL using my asp.net application. How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server. for Instance Dim connections as string, Dim da as dataset, Dim adpt as dataadapter. etc if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application thank you maxmax
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance. NetAdventure
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me. Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo EmployeeID and SerialNo are primary keys. I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work CREATE PROCEDURE dbo.inventoryinfo AS DECLARE @EmployeeID int DECLARE @SerialNo varchar(50) IF( @SerialNo is NULL) SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID ELSE SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
Hi, All, I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month
Month salefromagent directsale total Jan 1100 2300 3400 Feb 800 500 1300 .......... Dec --------------------------------- I know we can handle this in the program use two queries. But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue. Betty
Hi all, I need to write a sales store procedure. The sales summary is basically group by agentCode. But the problem is some agents have subagents. i.e., in the sales table. one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales. what should I do in the store procedure.
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:
CREATE PROCEDURE AddEmployee (@efname nvarchar(20), @elname nvarchar(20) ) AS
insert into tblEmployeeName (EmployeeFName, EmployeeLName) values (@efname,@elname) GO
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS Tot_work_all number(12,2); Bid_tot number(12,2); Ewo number(12,2); Overruns number(12,2); Underruns number(12,2); Contr_tot_all number(12,2); sContractType ae_contract.contr_type%type; BEGIN select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0)) into Tot_work_all from valid_item Where db_contract = db_id; Select sum(Contq * Contr_Price) into Bid_tot From Valid_item Where nvl(New_Item,'N') <> 'Y' and db_contract = db_id; Select sum(Qtd * Contr_price) into Ewo From Valid_item Where nvl(New_item,'N') = 'Y' and db_contract = db_id; Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns From Valid_item Where Qtd > Nvl(Projq,0) and db_contract = db_id and nvl(New_Item,'N') = 'N'; IF LAT_type <> 'R' THEN Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Projq,0) < Contq and db_contract = db_id and nvl(New_Item,'N') = 'N'; ELSE Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Qtd,0) < Contq and db_contract = db_id and nvl(New_Item,0) = 'N'; end if; Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0) +NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN --If the divisor is zero here, it's not an error. update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE --If the divisor is zero here, it would be an error update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; Else --Here we have a real number to calculate, so go ahead and do your stuff! update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; END;
hello iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "
TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page. depending on querystring the record is selected.
hello iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank. if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel. the stroe procedure is
CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100)) AS SELECT * FROM employee_master WHERE empuser_id IN (@ID)
and this is the statement that i use in my .aspxpage to call store procedure
Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn) cmd.SelectCommand.CommandType = CommandType.StoredProcedure cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100)) cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")
iam using store procedure to return certain no. of columns frm table.the query is SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")
i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records. i tried this in query analyzer as well it runs normally returing rows. only in store procedure it gives me blank record. any suggestions what is wrong ?
In this procedure i want to runtime insert ShahVis i.e REassignuserid. it will always change so i want runtime insert this field. and created by this is store in globaly i want to retrive this runtime. Please help me.
I am trying to loop through a string of ID's and check if theproductname for the ID is 'Chai' if so change it to 'Tea'. This is inthe northwind db - products table. call it exec sp_UpdateProduct'1,2,3'It does not work, any ideas - syntax, etc....CREATE PROCEDURE dbo.sp_UpdateProduct@IDs varchar(200)ASBEGINdeclare @sStr1 varchar(200)declare @iStrLen1 intdeclare @sParseElm1 char(2)declare @sStatus varchar(200)declare @count intset @sStr1= @IDsset @iStrLen1= len(@sStr1)SET @count = 0while charindex(',',@sStr1) <> 0beginselect @sParseElm1 = substring(@sStr1,1,CHARINDEX(',',@sStr1 ) - 1)set @sStatus = 'select productname from products where productid='+@sParseElm1if @sStatus = 'CHAI'update products set productname = 'TEA'elseupdate products set productname = 'CHAI'set @sStr1 = substring(@sStr1, CHARINDEX(@sStr1,',') + 1, @iStrLen1)endendGO
