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!
View 7 Replies
ADVERTISEMENT
Aug 1, 2007
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
View 5 Replies
View Related
Sep 22, 2004
I create a store procedure in sql, how do I call it?
thankss
View 2 Replies
View Related
Mar 12, 2008
Hi, how to call the shrink_file proc from shrink_file_caller proc as below??
CREATE PROCEDURE [dbo].[Shrink_File]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
...
GO
CREATE PROCEDURE [dbo].[Shrink_File_Caller]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(4000);
SET @nsql = '' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;
EXEC sp_executesql @nsql,N'@err INT OUTPUT', @err = @err OUTPUT;
GO
declare @err int;
EXEC Shrink_File_CALLER 'DATABASE1', @err
select @err as err;
I get the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATABASE1'.
(1 row(s) affected)
View 9 Replies
View Related
Aug 9, 2006
I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
-- Add the parameters for the stored procedure here
@strSearch varchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO
I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar); I don't know what do next.
Help is much appreciated.
View 4 Replies
View Related
Nov 13, 2007
Hi,is there any method to call a store procedure into a function?ThanksFabio
View 1 Replies
View Related
Nov 30, 2004
How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?
View 1 Replies
View Related
Jun 19, 2006
Can i call Tigger in store procedure or vice versa in sql server 2000.
and How it implement ?
View 1 Replies
View Related
Mar 20, 2007
Hello, I have one store procedure that writes something data to one physical sql table 'MyTable' and on beginning I first call:DELETE FROM MyTableProcedure returns at the end data from 'MyTable' as on SELECT. PROBLEM:I started SQL Manager on my Laptop and on Computer near me and all pointed to same database (on Server) and I run that procedure simultaneously on Laptop and Computer at the same time (two hands on F5 button on these computers). Occasionally when clicking with both hands at the same time I got no records at the end of execution (on both computers), otherwise I got the results.WHAT I THINK IS PROBLEM:Somhow one SP start working and it did not come to the end and another computer called this SP and execute first command (DELETE FROM MyTable) and I got empty records at the end..WHAT I NEED:I need to prevent this situation to get empty records. All computers must get these records always. How I can use some sort of LOCKs to do this?Or to do that on C# application level using LOCK command? Thanks in advanceAleksandar
View 2 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Nov 20, 2013
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'.
View 13 Replies
View Related
Feb 17, 2008
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
View 4 Replies
View Related
Feb 28, 2008
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
View 2 Replies
View Related
Mar 5, 2008
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
View 2 Replies
View Related
Apr 11, 2005
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
View 1 Replies
View Related
Jun 8, 2006
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
View 1 Replies
View Related
Aug 30, 2000
I want to know when does SQL flushes Store Procedure from the cache.
Thank You,
Piyush Patel
View 1 Replies
View Related
Dec 10, 2004
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
View 1 Replies
View Related
Nov 1, 2005
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.
Thanks
Betty.
View 2 Replies
View Related
Jan 14, 2005
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?
Thanks
Lystra
View 5 Replies
View Related
Feb 22, 2006
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
Thanks.
View 5 Replies
View Related
Mar 13, 2006
I 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
View 2 Replies
View Related
Apr 20, 2004
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;
View 1 Replies
View Related
Apr 4, 2008
1. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.
View 2 Replies
View Related
Apr 17, 2006
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.
View 13 Replies
View Related
Apr 20, 2006
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")
any suggestions wht may be wrong
View 3 Replies
View Related
May 24, 2006
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 ?
View 11 Replies
View Related
Dec 22, 2007
hello 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.
View 6 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Apr 4, 2008
Hi,
I follow the example for store procedure from this link to deal with large amount of data and it work great.
http://www.4guysfromrolla.com/webtech/042606-1.shtml
CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID
SET ROWCOUNT 0
GO
my problem is how do i do the Sort for this.
Any help would be appreciated.
Thanks
Ddee
View 4 Replies
View Related
Apr 11, 2007
Dear Frnds,
can anyone tell me is it possibel to use store procedure within the stored procedure ?? is it possible ??? if yes ?? then give me simple example
awaiting for positive reply
Thanks
Hiren
View 4 Replies
View Related
Jul 18, 2006
Hello, I have a problem, I want to execute in a single transaction three insert in store procedure, as it could implement. Thanks for its attention
View 1 Replies
View Related