Connect Same Database In The CLR Store Procedure

Apr 3, 2006

Hi everybody:

I have a urgent problem, and can not google any useful answer. Please help me!

In my CLR store procedure, I have used a context connect, and I want to creat another connect to the same database. I have set the database permission to external, creat asymmetric key in my assembly, and creat login for that key. The dbo is the owner.

When I use this connect string as store procedure input, and I opened this new connect in the CLR sp, it throw an exception --"***.MDF is in used, failed to connect"!!

How can I do?

Thank you very much! 

View 1 Replies


ADVERTISEMENT

Can't Seem To Connect To A Store Procedure On SQL Server

Dec 1, 2004

Hi,

I am trying to get a page to call on a store procedure in SQL server and can seem to get it right. Any advice would be appreiated




<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Dim Conn as new OleDbConnection("Provider=SQLOLEDB;Server=*;Database=WEST1;User ID=*;Password=*;Trusted_Connection=False")
Sub Submit(Sender as Object, e as EventArgs)
Dim objCmd as OleDbCommand = new OleDbCommand ("vendorPending 10031,85140109", Conn)

Dim objReader as OleDbDataReader

objCmd.CommandType = CommandType.StoredProcedure


Try
objCmd.Connection.Open()
response.write ("opening data")
objReader = objCmd.ExecuteReader

catch ex as OleDbException
Response.write ("Error retrieving data.")

end try

DataGrid1.DataSource = objReader
DataGrid1.DataBind()

objCmd.Connection.Close()

End Sub

</script>
<html>
<head>
<title></title>
</head>
<body>
<form method="post" runat="server">
<tbody>
<tr>
<td>
<br />
</td>
</tr>
<tr>
<td>
Login :
</td>
<td>
<asp:textbox id="user" runat="server"></asp:textbox>
</td>
</tr>
<tr>
<td>
Password :
</td>
<td>
<asp:textbox id="Pass" runat="server" textmode="Password" MaxLength="10"></asp:textbox>
<br />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:button id="Button1" onclick="submit" runat="server" text="submit"></asp:button>
</td>
</tr>
<br />
<tr>
<td>
</td>
</tr>
</tbody>
<br />
<asp:DataGrid id="DataGrid1" runat="server" HorizontalAlign="Left" Font-Names="Arial" ItemStyle-BackColor="#FFFFFF" AlternatingItemStyle-BackColor="#CCCCCC" Font-Size="10pt" Font-Name="Arial" width="755px" cellpadding="2" gridlins="vertical" BorderColor="Black" AutoGenerateColumns="False">
<HeaderStyle font-size="Small" font-names="Arial" font-bold="True"></HeaderStyle>
<EditItemStyle horizontalalign="Left"></EditItemStyle>
<AlternatingItemStyle backcolor="#CCCCCC"></AlternatingItemStyle>
<ItemStyle horizontalalign="Right" backcolor="White"></ItemStyle>
<Columns>
<asp:BoundColumn DataField="invnum" HeaderText="&lt;b&gt;Invoice Number&lt;/b&gt;"></asp:BoundColumn>
<asp:BoundColumn DataField="invdate" HeaderText="Invoice Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="duedate" HeaderText="Due Date" DataFormatString="{0:MM-dd-yyyy}"></asp:BoundColumn>
<asp:BoundColumn DataField="invamt" HeaderText="Invoice Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
<asp:BoundColumn DataField="payamt" HeaderText="Pay Amount" DataFormatString="${0:N2}"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>


The store proceudure takes 2 int parameters. I have coded it inline in order to test it. I also will implement validation once I get this working

Thanks

View 1 Replies View Related

Granting Permission To Object In X Database From A Store Procedure In Y Database

May 16, 2000

I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion.
Thanks
Maroof Khan

View 1 Replies View Related

Create Store Procedure That Take Data From 2 Database

Sep 7, 2007

 hello all.., i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo 
 in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill            balance
111222            200$
222444            10$ 
this is a list of order table 
user_id            no_order            date            totalcost
    a                     1                  1/1/07             50$
when customer insert no_bill(111222) in page and click a button,  then bill table became
no_bill            balance
111222            150$
222444            10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" 
is procedure can take data from 2 database?mystore procedure like:ALTER PROCEDURE [dbo].[pay](    @no_bill AS INT,    @no_order AS int,    @totalcost AS money)ASBEGIN    BEGIN TRANSACTION            DECLARE @balanc AS money                SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill)        UPDATE [bill]        SET            [balance] = @balanc - @totalcost        WHERE            [no_bill] = @no_bill    COMMIT TRANSACTIONEND  it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.Transaction
count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 0, current count = 1.No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[pay]." plss.. help... 

View 13 Replies View Related

How To Dynamically Assign Database Name In Query Or Store Procedure?

Sep 22, 2006

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @MyDatabaseName varchar(30)

set @MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

View 8 Replies View Related

Recovery :: Creation Of Store Procedure In AlwaysOn Database Infrastructure

Oct 2, 2015

I would ask if there some precaution on creating stored procedure on a database member of an always on group. I copied the stored procedure on all nodes but I'm not sure if this is enough to make it works in case of failover ?

View 5 Replies View Related

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

Store Procedure Not Store

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

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Store Procedure

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

Store Procedure?

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

How To Run SQL Store Procedure Using Asp.net

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

SQL Store Procedure In Asp.net

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

Store Procedure Maybe????

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

Store Procedure

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

Store Procedure

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

Store Procedure Help

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

Help With A Store Procedure

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

Get ID From Store Procedure

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

STORE PROCEDURE And DTS

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

Store Procedure

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

Store Procedure

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

Store Procedure

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

Store Procedure

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

Store Procedure

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

Store Procedure

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

Store Procedure Help

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

Store Procedure

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

Is It Possible To Use Two Store Procedure Within One Store Procedure ??

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

Aid With Store Procedure

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

Store Procedure Query

Feb 18, 2006

Hello AllI get stuck in one problem , Please help me and excuse me on the poorKnowledgein SQL Server Store procedureQ1...I want to return a string type value from store procedure , Is itpossible to do it.if posible then Please guide us how we can do it .Q2...I search a lot on net but i am unable to understand the ExtendedStore Procedure.How to register it and how to use in our store procedure .Q3 ... How to use xp_sendmail.dll in SQL Server.Thanking youWith regardsTarun

View 1 Replies View Related

Need Helps In This Store Procedure?

Aug 28, 2006

Hi Everyone,I appreciate if you can help on the following procedure.  When I ran it in my project in Visual Studio, the compiler always complained that  it expected a parameter "@stud_id" that was not provided.  What was wrong with this procedure? Thank you for your help.a123.  ALTER      Procedure registerstudent(     @email nvarchar(100), @student_password nvarchar(10), @first_name nvarchar(25), @last_name nvarchar(25), @address nvarchar(255), @city nvarchar(50), @province nvarchar(25), @country nvarchar(50), @phone_no_cell nvarchar(25), @phone_no_home nvarchar(25), @hour_rate int, @status char(1), @stud_id int output    )As     INSERT INTO student    ( email,  student_password,  first_name,  last_name,  address,  city,  province,  country,  phone_no_cell,  phone_no_home,  hour_rate,  status      )    VALUES    ( @email, @student_password, @first_name, @last_name, @address, @city, @province, @country, @phone_no_cell, @phone_no_home, @hour_rate, @status           )SELECT  @stud_id = @@identity 

View 2 Replies View Related

Store Procedure Prob?

Mar 21, 2007

Hi,I have three different tables. I want one login screen and check all table ,if any user got it,but i have written simple store procedure and i take values by parametrically,how can i know user's table name?ALTER PROCEDURE [dbo].[sp_GirisKontrol]    -- Add the parameters for the stored procedure here    @Username nchar(12),    @Password nchar(12),    @Id int output,    @Rowguid UniqueIdentifier outputASBEGIN    SET NOCOUNT ON;    SELECT @Id=Id,@Rowguid=Rowguid FROM Admin WHERE Username =@Username AND Password =@Password UNION    SELECT @Id=Id,@Rowguid=Rowguid FROM Ogretmen WHERE Username =@Username AND Password =@Password
UNIONSELECT @Id=Id,@Rowguid=Rowguid FROM Ogrenci WHERE Username =@Username AND Password =@Password         if(@Id IS NULL)    SELECT @Id=-1END  

View 3 Replies View Related







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