i have one table and structure of table is like.Now i need to fetch the Resturant Name and status of Resturant ( whether it is closed or open by comparing the opening time and closingTime with currentTime).
View 6 RepliesI have to write stored proc to get the daily transaction for a specified date. Right now what i do is everytime i log a transaction in table3 I update table 2 with the count. Then i use table 2 to write the stored proc and to get the desired result. I have pasted my stored proc below with the output. My problem is when another subsystem is added in the AppInfo table i need to update my stored proc because i have hardcoded the subsystem name.
[Code]....
I have made a simple stored proc which include Transaction through which I have to delete records from two tables EmpDetail and EmpDept . EmployeeId is PK in EmpDetail and FK in EmpDept. Procedure has been executed but on doing exec DelEmployee @EmployeeID='e07' showing 0 rows affected..
[Code]....
I've been self teaching SQL and asp.net and i'm a little stuck. I have a stored proc for the insert and then in my Insert inside my class it's not returning the ID after the insert as I am receiving an error message that NULL value has cannot be inserted. I want to add default values to a seperate table after the insert. I will provide my code below, if anyone can help me why the value is not going across?
[Code]....
This is the sproc
ALTER Procedure [dbo].[spGetGroupDetails]
(
@inUserID Varchar(15),
@inGroupID numeric=0,
@inGroupName varchar(100) = null
)
As
[Code]....
I am Developing A Application . I want to develop this Application with Linq and used Stored proc in linq Plz give me Some suggestion How we used And If You Have Some Related Topic
View 1 Repliescan i set the table name in stored proc at runtime ..
sp_MyStoredProc
@Tablename varchar(25)
AS
BEGIN
like select * from @Tablename
END
is it possible...
I have a recordset in a gridview that comes from an outer joint that creates null values in the recordsetWhen I write to the database using stored proc defined in datasource I get dbnull error.I'm trying to process the onDeleting event in the code behind but don't know what to write or if that's the best wayI need to call a stored proc and if the last parameters value is null then the stored proc does one thing, if it's no null it does something else.However DBNull doesn't ssem to be working.
View 1 RepliesI wanted to know how to prevent more than one user from uploading the report at a time .
This is a Windos based app written in c# . When User A clicks Upload option on one server from the menu to upload the files and at the same time when User B clicks Upload option on different server , User B should be alerted a message saying "User A's uploading is in progress,pls wait" . How to achieve this, with the code..I am thinking this logic should be kept in a stored proc, How do I write that proc?
lets say i have check box list like that
Cairo
Alex
Aswan
All
each of them is a check box inside this check box list and i have a stored proc that takes paramter which is the name of the city and gives me a data according to the name of the city (Filter). i wana know if i checkec more that one city how can i pass this to the stored proc eg if i checked on cairo and alex what can i do to make the filter looks like this where city Like 'Cairo' and City Like 'Alex' My Stored Proc takes @City Parameter like this Create Proc xx(@City varchar(2)) how can i pass the 2 cities 2 the stored proc
i have two stored procs. when th 1st stored proc runs i want to be able to use this value returned from the stored into another stored proc.
my first stored proc is:
[Code]....
which returns [Transfer_stations_Authority_name] = 'Blackpool'
now i want to use this value into my second stored proc@
[Code]....
[Code]....
Is there any option to use loop statement in MS SQL Stored Procedure?
Suppose I have two tables Table-A and Table-B
Table-A
ID
Name
Phone
1
X
12354
2
Y
3689
3
Z
2578
Table-B
ID
Name
Phone
I want to insert all the values of Table-A into Table-B by using Stored Procedure
I am using MS SQL 2005
i have the table with the following fields:
FromGrams
ToGrams
Price
CountryID
suppose consider the table have the value as follows:
FromGrams=10
ToGrams=100
Price=0.25
CountryID=221
if suppose user adding new range that have the value like follows:
FromGrams=50
ToGrams=120
Price=0.95
CountryID=221
then the result table will like this
FromGrams ToGrams Price CountryID
10 50 0.25 221
51 120 0.95 221
like that all conditions have to satisfy
1
0.00
1.00
0.00
13.00
0.99
US First Class
224
True
2
0.00
1.00
0.00
13.00
6.00
US Priority
224
True
3
0.00
1.00
0.00
13.00
1.99
Canada First Class
39
True
4
1.01
2.00
13.01
41.00
1.60
US First Class
224
True
5
1.01
2.00
13.01
41.00
6.00
US Priority
224
True
6
1.01
2.00
13.01
41.00
2.60
Canada First Class
39
True
7
2.01
3.00
41.01
69.00
2.20
US First Class
224
True
8
2.01
3.00
41.01
69.00
6.00
US Priority
224
True
what are the steps required to write efficient stored procedure. like not to use sp_ as a prefix because it is in master database
View 2 RepliesI want to write dynamic store procedure means 1 select_sp, 1 update_sp, 1 insert_sp, 1_delete_sp for all tables.
How it is used in secured manner.
i am using .net 1.1 and sql 2000.i need to write the stored procedure in .cs file.is it possible.if so how?
View 2 RepliesIn sqlserver2005 how to handle exceptions in stored procedures and
1)redirect to other page
2)write in to log file
give me a simple example to write an exception in stored procedure
View 2 RepliesI am trying to get the results of the variables from a stored procedure to write out somewhere so I can see what values are being passed through for each variable in a button click. Does anyone know how to do that. Here is my code for the button click:
[Code]....
Here is my variables in the stored procedure. The first is the select statement and then the update:
[Code]....
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
My function isn't returning anything - strReturn is empty:
[Code]....
When I execute this stored proc using 'exec GetMerchantLocationZip (3333, 373773)' I get the correct zipcode in SQL. Why don't I get it in Visual Studio?
[Code]....
I am learning, so apologies if it's a obvious error.
I am attempting to execute a stored proc in asp.net in the code behind. The parameter I am trying to pass is strErrorMessage that contains a value of "The transport failed to connect to the server.; ". The error message when the query gets executed is: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@errMessage"): Data type 0xE7 has an invalid data length or metadata length. Update with code
try
{
...
...
...
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Email was not sent - " + ex.Message + "');", true);
string strMessage = ex.Message;
string strStackTrace = ex.StackTrace;
strMessage = strMessage.Replace("
", "; ");
strMessage = strMessage.Replace(" ", "");
strStackTrace = strStackTrace.Replace("
", "; ");
strStackTrace = strStackTrace.Replace(" ", "");
AppErrorLog(strMessage, strStackTrace);
return false;
}
protected void AppErrorLog(string strErrorMessage, string strErrorStackTrace)
{
SqlConnection conErrLog = new SqlConnection(strConn);
string sql = "usp_AppErrorLog_AddRecord";
SqlCommand cmdErrLog = new SqlCommand(sql, conErrLog);
conErrLog.Open();
try
{
cmdErrLog.CommandType = CommandType.StoredProcedure;
cmdErrLog.Parameters.Add(new SqlParameter("@errMessage", SqlDbType.NVarChar, 8000));
cmdErrLog.Parameters["@errMessage"].Value = strErrorMessage;
cmdErrLog.Parameters.Add(new SqlParameter("@errStackTrace", SqlDbType.NVarChar, 8000));
cmdErrLog.Parameters["@errStackTrace"].Value = strErrorStackTrace;
cmdErrLog.Parameters.Add(new SqlParameter("@userID", SqlDbType.VarChar, 12));
cmdErrLog.Parameters["@userID"].Value = User.Identity.Name;
SqlDataAdapter ada = new SqlDataAdapter(cmdErrLog);
cmdErrLog.ExecuteNonQuery();
}
catch(Exception e)
{
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('AppErrorLog - " + e.Message + "');", true);
}
finally
{
conErrLog.Close();
}
}
The column datatype in the table is nvarchar(MAX).
i have the following stored proc that inserts data into a database based on a stauts field. the first stored proc inserts into the first table and i am trying to get the identity field out as i need this value for the next stored proc. the first stored proc works fine and inserts the data but the secnd one doesnt, even though there is data there.
[Code]....
I am trying to pass stored procedure from a controller to view. In my Home controller, in Index method, I have this. CustOrderHist is my stored procedure from northwind database.
public ActionResult Index()
{
DataClasses1DataContext dc = new DataClasses1DataContext();
var products = dc.CustOrderHist("ALFKI").GetEnumerator();
return View(products);
[Code]....
I have a stored proc that takes a bit long to run. I tried to do this command.CommandTimeout = 200;still gets time out within 200 secs.How is this done?
View 1 Replies