While using an insert query for a certain table, can I used a select statement to know whether there's an already existing value in the stored procedure using a return value where insert occurs? Or do I need to separate a procedure?
View 9 RepliesI have an update function in my data layer which is defined as:
public int UpdateRBTable(parameters ...) This calls a SQL Server Stored Procedure to perform an update function on the database.
The process does its job for updating the table. However, the stored procedure has a return value (which indicates how many rows were updated), but this return value is not returned to the application. The application always shows that a zero was returned from the stored procedure.
Is it possible to return multiple values from a stored procedure? Basically, if I execute the stored proc on the C# side, then on the SQL side I do multiple calculations. Let's say I have 5 int values I need returned to the C# side.
View 4 RepliesI not getting the return value from stored procedure that I have written so how to get that plzz its urgent I have return the procedure as ALTER PROCEDURE [dbo].[sp_AddPaySlip]
View 2 RepliesLong story short, it would be a lot easier for me to just return two values (from a many-column table, like combining columns) than to combine them in my asp project. I'm just having many problems with binding to a dropdownlist, and it would be easier not having to guess what index the column is I need :)
So, let's just say my table, "Building", has 5 columns - BuildingID, PracticeID, PracticeName, Address, & Zip (there are many more in my real table).
I already have a stored procedure that is "SELECT * FROM Building ORDER BY PracticeName". What I would like, is something that returns column values together with literal characters, like:
PracticeID - PracticeName (Address) AS First
I know I could use "SELECT PracticeName AS Name FROM Building" to simply change the name of the column I'd receive, but I want to add literal characters ( "-" and "(" and ")" and " ") with specific columns I choose.
Since I have about 20 columns in my table and I only want about 6, I don't need to SELECT *, but only those 6. That's easy enough. But since I would still like these six columns combined, I'm not sure what to do.
Again, what I want is to return something like:
PracticeID - PracticeName (Address) AS First BuildingID - Zip AS Second the "-" and "()" and " " need to be included and returned from SQL, not when I retrieve them)
In my asp.net page I pass paremeters to a stored procedure and then inside a try blog the foloving line: affectedRows = ssqldbsorce.insert
never returns expected result from stored procedure.
Inside stored procedure event if I explicitely write the following line "return 5" for example, it never returns 5, only -1, 1, 2
For example here a very simple one without output parameters(I still should be able to return a value without output parameters, right?):
ALTER PROCEDURE [dbo].[createUser]
@regCode varchar(10)
AS
SELECT * FROM dbo.reg_code WHERE reg_code=@regCode
....
---other statements, and for the sake of the example lets return 5
RETURN 5
Is there something wrong with doing it thi way, here is also a tutorial, according to which it should work just fine: [URL]
Is there another way maybe in the assp.net page to capture the value in the variable from a stored procedure.
I am realitivly new to c# and I am trying to do something that is probably simple, but I might just be over thinking it.What I want to do is have an image change based on a return of a sql data call. I am using a stored procedure that reads values and uses a case to switch between red, yellow, green for status. For example, I have nine items that the SP goes through and returns one of the colors. What I would need is if it detects a red, change the image to red and so forth. Now, when I execute the SP in C# I can get the data but it always just reads the last value. So if it had a red in there and the next one is a green, it uses the green. I even thought about reading the colors and using an IF statement adding the values to determine which color should be used.
[Code]....
i want to return two output parameter n temp table from sql server stored procedure
View 6 RepliesWhen can I use Stored Procedure? Is this useful for looping into multiple SELECT statements to return single value? How? Can anyone post some sample codes? I have text query and notice that my web application is slow and want to find ways to speed it up. Is there any possible ways aside from Stored Procedures?
View 4 Replieshow to return list of values(single column values) from stored procedures in sqlserver 2005?
View 4 Repliesi made stored procedure like that
select AssetCode ,Description
,EquipmentID from [FATMS].[fnMaintenanceHeaderRead](@ContextUser,@ContextClient,@ContextLang,@ContextApp,@ContextOrg,'1',@fnXmlParamMaintenanceHeader)
MH
where MH.Status
IN
( @Status)
when i put three values for parameter (@status) not work but one value work
how to put three values please to parameter @status when excecute that stored procedure
EXEC @return_value = [FATMS].[spMaintenanceReport]
@ContextUser = N'4806',
@ContextClient = 1,
@ContextLang = N'en-US',
@ContextApp = N'FATMS',
@ContextOrg = 23,
@ActionCode = N'1',
@MaintenanceHeaderId='',
@SearchText='',
@EntryType='0',
@Status=N' '
SELECT 'Return Value' = @return_value
I have a stored procedure, which I'm calling from ASP.Net application. I'm facing a problem in stored procedure when comparing values.Please check code below to get further details.
Stored Procedure:
ALTER PROCEDURE [dbo].[MyStoredProcedure]
(@ItemID nvarchar(50), @Weight1 float, @Weight2 float)
-- check remaining/available weight in items table
DECLARE @RemainingWeight float
SET @RemainingWeight = (SELECT RemainingWeight FROM items WHERE ItemID = @ItemID)
[code]...
tbl_salary
salary salperyr hike20 hike20yr
10000.0000 NULL 12000.00 144000.00
12000.0000 NULL 14400.00 172800.00
14000.0000 NULL 16800.00 201600.00
15000.0000 NULL 18000.00 216000.00
18000.0000 NULL 21600.00 259200.00
20000.0000 NULL 24000.00 288000.00
22000.0000 NULL 26400.00 316800.00
in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place(salary*12)in one shot.
I have a .net 3.5 application in c# with sql server 2008 at back end. The application is about assigning jobs to supervisors and then these supervisors have to write remarks about the job progress from time to time. There are two tables ; a JobAssign table with JobOrderNo and Supervisor columns, and a JobUpdate table with jobOrderNo,Date And Remarks(nvarchar) column.
When a supervisor is assinged a job he is sent an email but sometimes the supervisors do not write remarks about a job for some time. i want a stored procedure through which if the remarks of a job are not written for say 5 days an email is sent to the supervisor to do so? in my job update table there are multiple entries for a job so the stored procedure will have to check the Top Remarks Date for each jobOrderNo. then compare it to today date if the difference is difference is 5 days then send email. i have read posts on sending email through job scheduler but i'm new to stored procedure so have no idea how to write such.
I have a database table as follows:
[Code]....
This table receives data from my web application via a stored procedure, snippet pasted below:
[Code]....
In my quote.aspx page, I have a wizard control that collects numerous data points. In one of the wizard steps, I have 20 textbox controls for PartNumbers and 20 textbox controls for respective Quantity.
Question:How do I write a for..each loop that checks for values in my Part Number and Quantity fields and inserts them via my SQLDataSource?
In my application I have 7 listboxes.Each list box has morethan 100 listitems.
the best way to pass the selected listitem values(including text values also) to stored procedure in sql server 2005.
In my stored procedure I have declared some parameters as NULL values(Optional Parameters).
In my C#.NET code I didn't use the parameters to pass values to Stored Procedure's NULL
Parameters. So I am getting Exception.
I don't want to pass parameters from my code.Because I have declared 30 parameters in my
Stored Procedure as NULL values.
Is there any other way to execute Stored Procedure from Front End without passing values to
Optional Parameters in SQL stored procedure?
I could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried
I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011
How Would I accomplish this?
I have a stored procedure with 6 input parameter and 4 output parameter. In My asp.net - C# code I execute the stored procedure and return the parameter, first of all it gave me this error :
Procedure or function w_create_order has too many arguments specified. That I'm not sure why is that because the number of parameters is right, the only thing is may be the way that I used is not right.
Second, I would like to show the parameter in the label control in my page, I'm not sure how to do this.
[Code].....
I have a stored procedure with 6 input parameter and 4 output parameter. In My asp.net - C# code I execute the stored procedure and return the parameter. Stored procedure executed correctly, but the return values are empty. I would like to show the return value on the page. Stored procedure works fine and return value in sql manager.
This is my code:
SqlCommand cmd = new SqlCommand("w_create_order", connection);
cmd.CommandType = CommandType.StoredProcedure;
//input parameters
cmd.Parameters.AddWithValue("@in_order_class", "WEB");
cmd.Parameters.AddWithValue("@in_owner_code", "OP");
cmd.Parameters.AddWithValue("@in_sales_campaign_code", "DEF");
cmd.Parameters.AddWithValue("@in_cus_key", null);
cmd.Parameters.AddWithValue("@in_lab_key", null);
cmd.Parameters.AddWithValue("@in_invoice_number", null);
connection.Open();
cmd.ExecuteNonQuery();
//output parameters
//output parameters
cmd.Parameters.Add("@ord_key", SqlDbType.Int, 500);
cmd.Parameters["@ord_key"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@ord_invoice_number", SqlDbType.Int, 500);
cmd.Parameters["@ord_invoice_number"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@via_ship_via_code", SqlDbType.NVarChar, 500);
cmd.Parameters["@via_ship_via_code"].Direction = ParameterDirection.Output;
string message = (string)cmd.Parameters["@via_ship_via_code"].Value;
cmd.Parameters.Add("@ord_ship_after_date", SqlDbType.DateTime);
cmd.Parameters["@ord_ship_after_date"].Direction = ParameterDirection.Output;
Label1.Text = message;
connection.Close();
I'm having a hard time to return a string value. I usually do this by passing an int, which works just fine. Now, I need to pass a string but couldn't. Is this possible? Or int is only type that SQL can return?
View 11 Repliesi have a stored procedure and i want to return a value from that procedure and returned value i want to store in a variable Stored Procedure is create procedure simpleproc(OUT pcount int)begin set @pcount=(select count(*) from testtable); end
Now i call this proc from C# code
string query="CALL simpleproc(@count)";
MySqlCommand commond = new MySqlCommand();
MySqlParameter returnValue = new MySqlParameter("@count", DbType.Int32);
returnValue.Direction = ParameterDirection.ReturnValue;
commond.Parameters.Add(returnValue);
commond.CommandText = query;
commond.Connection = con; // I already set the connection in con and opened it
commond.ExecuteNonQuery();
int count = Int32.Parse(command.Parameters["@count"].Value.ToString());
What should i have to change to get return value in count
when getting return value from stored procedure, return value only first character, Exec sp_Auto_Gen_TTBDBatNo 'TT', '' in sql server get whole string but in asp.net get first character how to get the whole string value?
CREATE PROC sp_Auto_Gen_TTBDBatNo
@Prefix nvarchar(2),
@Result nvarchar(8) output
AS
BEGIN
DECLARE @LastValue int
-- CompanyCode = @CompanyCode AND BankCode = @BankCode AND AccountCode = @AccountCode
SET NOCOUNT ON
If @Prefix = 'BD'
SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_BD WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2
ELSE
SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_TT WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2
SET NOCOUNT OFF
set @Result = @Prefix + RIGHT(RTRIM(STR(year(getdate()))),2)+RIGHT('0'+LTRIM(RTRIM(STR(month(getdate())))),2) + RIGHT('0'+LTRIM(RTRIM(STR(ISNULL(@LastValue,0)+1))),2)
print @Result
END
C# code:...........................
I'm not so familiar with stored procedures but I'm interesting in moving most of my web selects to stored procedures. I'm working with .net based website and all my data access is something like this:
[Code]....
But now I want to improve some process and make tunings. For that I want to use stored procedures, so instead of the "SELECT ...." sentence I'll put stored procedure name. My problem is how to return a table from one.
I have this storde procedure that I add to my dbml file, when I do that it does not recognize the return type. The return type is a string, how do I set that it should be a string?
View 6 Replies