How To Tune Particular Store Procedure
May 12, 2008Hello,
i need to tune some of my store procedures.
can any one help me how it's possible to tune particular store procedures?
Thanks
Prashant Hirani
ADVERTISEMENT
Tune Procedure !!!!!!!!!!!!!!!!! Urgent
Apr 18, 2002How can l trim the code and make the procedure run faster ???????????????
CREATE Procedure Disbursements_Cats
(@startdate datetime,@enddate datetime)
As
Begin
SELECT Transaction_Record.loan_No AS loan_no,
Transaction_Record.transaction_Date AS Transaction_Date,
Transaction_Record.transaction_type AS Transaction_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,
Product.product AS Product,
Product_Type.product_Type AS product_type,
Product_Type.loan_Type AS Loan_type,
Customer.first_name AS first_name,
Customer.initials AS initials,
Customer.second_name AS Second_Name,
Customer.surname AS surname,
Customer_identification.idno AS ID_No,
Bank.Bank_name AS Bank_Name,
Bank_detail.Account_no AS Account,
Bank_detail.Branch AS Branch
FROM Transaction_Record CROSS JOIN
Bank_detail CROSS JOIN Bank CROSS JOIN
Customer CROSS JOIN Product CROSS JOIN
Loan_Type CROSS JOIN Product_Type CROSS JOIN Customer_identification
End;
GO
Transact SQL :: Fine Tune Procedure / Accessed By Multiple Users
Oct 13, 2015i have a report that runs on a huge table rpt.AgentMeasures , it has 10 months worth of data (150 million records as of today and will keep increasing). i have pasted my proc below , the other tables that are joined to this huge table do not have more then 3k records.This report will be accessed by multiple users (expecting 20 ppl). as of now this reports runs for 5 mins if i pull for 1 month worth of data. if it is wise to use temp tables.
ALTER proc [rpt].[Get_Metrics]
@MinDate DATETIME,
@MaxDate DATETIME,
@Medium Varchar(max),
@footPrint varchar(max),
[code]...
Call Store Procedure From Another Store Procedure
Nov 13, 2006 I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Store Procedure Not Store
Nov 20, 2013My 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'.
How To Tune The Sql Server?
Mar 22, 2004hi here has a question for the mssql,
a problem occurs when i run a large database, the running speed is very slow.
for example, if i want to seek the record of 1500 employees with 15 per person(average) within 1 year(12 months), that mean i have to find record for 1500 * 15 * 12 time.
so, could i find a way to solve this problem? is this call tune the sql server/index/view?
what different of tune the sql server with index and view?
thanks for giving me advice.
:)
How To Tune My Query
Sep 21, 2005I have written a query which fetches data from a table with huge amount of data. This query is actually being used in a stored-procedure and it takes a lot of time, hence slows down my SP.
Here is the query that I'm builiding in the stored-procedure:
-------------------------------------------------------------
SET @selectLeadsInPeriodString = ' SELECT LM_Dealer, LM_Brand, SUM(LM_ImpressionCount)
FROM LM_ImpressionCount_Dealer'
IF(@timeCriterion IS NULL OR LTRIM(RTRIM(@timeCriterion)) = '' OR LTRIM(RTRIM(@timeCriterion)) = 'NULL')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE (CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) >= ''' +
CONVERT(varchar,@startDateTime,102) +
''' AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= ''' +
CONVERT(varchar,@endDateTime,102) + ''')'
ELSE IF(@timeCriterion = 'CurrentMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE())'
ELSE IF(@timeCriterion = 'PreviousMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE()) - 1'
ELSE IF(@timeCriterion = 'YearToDate')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE ([LM_ImpressionCount_Dealer].[LM_ImpressionDate] >= cast((''1/1/''+cast(year(getdate()) AS varchar(4))) AS datetime)
AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))'
IF(@brand IS NOT NULL AND LTRIM(RTRIM(@brand)) <> '')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' AND [LM_ImpressionCount_Dealer].[LM_Brand] = ''' + @brand + ''''
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString + ' GROUP BY LM_Dealer, LM_Brand'
The variables used in the query formation above are passed as input parameters to the SP.
The table being queried has columns 'LM_Dealer', 'LM_Brand', 'LM_ImpressionCount' and 'LM_ImpressionDate'.
Also, the table has a non-clustered index on the column LM_ImpressionDate.
With all this information, can anyone suggest as to how I can optimize the query above.
Thanks in advance.
-Dex
How To Tune Query??
Sep 30, 2005I think this is very silly question
but It is hard for me :eek:
SELECT email, host FROM WAITING_AUTH WHERE email NOT IN
(SELECT email FROM MEMBER)
AND host NOT IN (SELECT host FROM MEMBER)
thanks~ Have a nice weekend
How Can I 'tune-up' My SQL Installation
May 11, 2004Hi,
I have SQL desktop version installed and for the last few days it has really slowed down. I have ran many anti-virus etc. and all is okay on that front.
Any tips regarding how I can tune things up? What should I look for and how do I go about it. Deleting LOG etc. etc???
Please guide.
Thanks.
Looking To Tune Query...
Sep 15, 2006If anyone is able to provide advice for tuning the below query in sqlserver, it is much appreciated. In addition any index suggestions arealso appreciated as I have access to the tables. Thank you.select a.id, isnull(b.advisement_satisfaction_yes, 0) asadvisement_satisfaction_yes,isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,casewhen isnull(b.advisement_satisfaction_yes, 0) >isnull(c.advisement_satisfaction_no, 0) then 'YES'when isnull(b.advisement_satisfaction_yes, 0) <isnull(c.advisement_satisfaction_no, 0) then 'NO'when isnull(b.advisement_satisfaction_yes, 0) =isnull(c.advisement_satisfaction_no, 0) then 'TIE'end as Satisfied_With_Advisementfrom aleft Join(select id, count(answer_text) as Advisement_Satisfaction_yes from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'yes'GROUP BY id) bon a.id = b.idleft join(select id, count(answer_text) as Advisement_Satisfaction_NO from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'NO'GROUP BY id) con a.id = b.idwhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'
View 2 Replies View RelatedHow To Tune Server For Better Performance
Mar 23, 1999ENVIRONMENT:
I have SQL Server6.5 running under a dedicated NT Server. NT configuration includes dual pentium 200Mhz processors, 256MB RAM and RAID system.
The Database size is 1GB with actual data size about 500MB.
PROBLEM:
I have an application which uses lots of joins to get the results. My select query is running too slow even when I run it on the server.
I updated the statistics and rebuild all the indexes on the tables used by the query.
Any suggestions on using SQL Trace and tuning the server/database are welcome.
Srini
Fine-tune Query
Apr 24, 2008Hi,
I have a query as mentioned below:
SELECT BillCurrencyID,DistD,Amount,PartnerFlag1
FROM Factsales_tab_Dtls (NOLOCK)
WHERE (sales_year =2007 OR sales_year=2008)
AND Country ='US'
1) Table Factsales_tab_Dtls is having more than 5.5 million of records.
2) It is having 32 columns.
3)There's nonclusetered index on columns sales_year & country
4)The query takes longer time for execution
Please help me fine-tune the query
How Do We Tune A Large Sql Query
Feb 6, 2006hi,I have a problem asked by one of my senior person and finding theanswer .What is the step by step procedure for tune a large sql query.OR how do we tune a large SQL query with somany joins
View 6 Replies View RelatedDBA HELP: Performane Tune SELECT, SUM, && CASE
Jul 23, 2005HELP!!!I am trying to fine tune or rewrite my SELECT statement which has acombination of SUM and CASE statements. The values are accurate, butthe query is slow.BUSINESS RULE=============1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or bothhave a value.2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse theSQL from both Count1 and Count2)3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.SQL Code========SELECTSUM(CASEWHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR (FIELD_1 IS NOTNULL AND FIELD_2 IS NOT NULL))THEN 1ELSE 0END) AS Count1 ,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE 0END) AS Count2,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)END) AS Total_Count,SUM(CASEWHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)THEN 1ELSE 0END) AS None_Count,FROMTABLE_1
View 1 Replies View RelatedNeed To Tune A Table For Performance Gains
May 2, 2007Hi :I have a TableA with around 10 columns with varchar and numericdatatypesIt has 500 million records and its size is 999999999 KB. i believe itis kbi got this data after running sp_spaceused on it. The index_size wasalso pretty big in 6 digits.On looking at the tableAit didnot have any pks and hence no clustered index.It had other indicesIX_1 on ColAIX_2 on ColBIX_3 on ColCIX_4 on ColA, ColB and ColC put together.Queries performed in this table are very slow. I have been asked totune up this table.I know as much info as you.Data prior to 2004 can be archived into another table. I need to run aquery to find out how many records that is.I am thinking the following, but dont know if i am correct ?I need to add a new PK column (which will increase the size of thetableA) which will add a clustered index.Right now there are no clustered indices2. I would like help in understanding should i remove IX_1, IX_2, IX_3as they are all used in IX_4 anyway .3. I forget what the textbox is called on the index page. it is set to0 and can be set from 0 to 100. what would be a good value for it ?thank you.RS
View 8 Replies View RelatedTune Execution Plan Of Expensive CLR UDF
Apr 18, 2008
I've create a bunch of views to expose a logical model of the underlying database of an application server.
To enforce the security control, I've also created a CLR UDF to call the application server's API for security check and audit log.
For example, we have a table, tblSecret, and the view, vwSecret, is,
SELECT
Id,
ParentId,
Description,
SecretData
FROM tblSecret
WHERE udfExpensiveApiCall(Id) = 1
The udfExpensiveApiCall will return 1 if the current user is allowed to access the SecretData else 0. The CLR UDF call is very expensive in terms of execution time and resources required.
Currently, there are millions rows in the tblSecret.
My objective is to tune the view such that when the view is JOINed, the udfExpensiveApiCall will be called the least number of time.
SELECT
ParentId,
SecertData
FROM vwParent
LEFT JOIN vwSecret ON vwSecret.ParentId = vwParent.ParentId
WHERE vwParent.StartDate > '1/1/2008'
AND vwSecret.Description LIKE '%WHATEVER%'
Is there any way to specify the execution cost of the CLR UDF, udfExpensiveApiCall, such that the execution plan will call the UDF while it is absolutely necessary?
Is there any query hint will help?
Any recommendation?
Thanks,
Simon Chan
Store Procedure
Feb 17, 2008Hi 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
View 4 Replies View RelatedStore Procedure?
Feb 28, 2008Hi 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
How To Run SQL Store Procedure Using Asp.net
Mar 5, 2008I 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
SQL Store Procedure In Asp.net
Apr 11, 2005I 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
Store Procedure Maybe????
Jun 8, 2006Hello, 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
Store Procedure
Aug 30, 2000I want to know when does SQL flushes Store Procedure from the cache.
Thank You,
Piyush Patel
Store Procedure
Dec 10, 2004Hi, 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
Store Procedure Help
Nov 1, 2005Hi 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.
Thanks
Betty.
Help With A Store Procedure
Jan 14, 2005I 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?
Thanks
Lystra
Get ID From Store Procedure
Feb 22, 2006I 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
Thanks.
STORE PROCEDURE And DTS
Mar 13, 2006I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt.
Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible?
Thanks
Store Procedure
Apr 20, 2004This 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;
Store Procedure
Apr 4, 20081. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.
Store Procedure
Apr 17, 2006hello
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.
Store Procedure
Apr 20, 2006hello
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")
any suggestions wht may be wrong
Store Procedure
May 24, 2006iam 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 ?
Store Procedure
Dec 22, 2007hello sir
i want to write store procedure.
I write following code--
drop Proc Analyst
CREATE PROC Analyst
@UserID varchar(20),
@StockName varchar(50)
AS
INSERTtAnalystAssignHistory
(
AnalystAssignHistoryCode,
SecurityCode,
ISIN,
StockName,
UserID,
IsDummyEnabled,
AnalystAssignDate,
StatusCode,
CreatedBy,
CreateDate,
UpdatedBy,
UpdateDate
)
SELECTCOALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1,
SecurityCode,
ISIN,
StockName,
'ShahVis',
IsDummyEnabled,
AnalystAssignDate,
'A',
'Gayatri',
GETDATE(),
NULL,
NULL
FROMtAnalystAssignHistory
WHEREUserId = @UserID
and StockName = @StockName
GO
EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.'
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.