.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

stored procedure: select and update

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

View Complete Post

More Related Resource Links

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Update Database Record using GridView and Stored Procedure

Hi, I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error. I can populate values from database fine but its produce error when i click on edit link button. Please see the code below. *********** ASP.net GridView Code I am using *****************<asp:GridView ID="GridView1" runat="server" DataKeyNames="RollID, EmpID" AutoGenerateColumns="False" Width="700px"> <Columns> <asp:TemplateField HeaderText="ApplicationID" Visible="false"> <ItemTemplate> <asp:Label ID="AppsID" runat="server" Visible="false"><%# Eval("RollID") %>'</asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ReleaseID" Visible="false"> <ItemTemplate> <asp:Label ID="rlsID" runat="server" Visible="false"><%# Eval("EmpID") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Roll Name"> <ItemTemplate> <asp:Label ID="rlsVersion" runat="server"><%# Eval("Roll_Name")%></asp:Label> </ItemTemplate> <EditI

SqlDataSource, FormView, and Update with stored procedure in code behind

Hello, Using a FormView with a SqlDataSource, I'm attempting to Update data by calling a stored proc in code behind. I was having trouble getting parameters using Update Parameters in the SqlDataSource, but found a working solution by coding the parameters. The problem now is I'm getting an "Updating is not supported by data source 'XYZ' unless UpdateCommand is specified'. I saw some previous posts on the forums, but didn't find them very enlightening. 

update stored procedure for existing values of column(salperyr)

tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in 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.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

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.   so plz suggest me update stored procedure for this

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

calling stored procedure with in select statement



        Any one pls tell me how to call the stored procedure in SQL server

SQL Stored Procedure - if exists Update else Insert.



I'm trying to create a stored procedure that get a Key and Name (both varchar), the procedure will check if a the key allready exists in the table, if it exists it will update the name, if its not exists it will add it to the table..

I have no clue how to use IF to check if the row exists, please help.

Thanks :)

Deadlock: Store Procedure that only contains select statement owns an update key lock


I have a seemingly simple deadlock graph that contains 2 SP's. One SP is updating a table, while another SP (the victim) is selecting from it. The interesting thing about the graph is that the SP that contains the select (and only a select) is shown to own an UPDATE lock on the table that SP2 wants to update. How is this possible?



Stored procedure returns two select results


Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard


FROM buddies


the other is a bit more complicated


SELECT Buddy as Online
FROM Buddies
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader


update two tables from single grid view using stored procedure PLZ help


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace INV_DN
    public partial class Form1 : Form
        SqlConnection con;
        SqlDataAdapter da1;
        SqlCommand cmd;
        //  SqlCommandBuilder cb;
        DataSet ds;
        public Form1()

        private void Form1_Load(object sender, EventArgs e)
            con = new SqlConnection("User Id=sa;Password=123;database=amar");
            cmd = new SqlCommand("getdata", con);

Stored Procedure Metadata failure- Dummy select not working


I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -


IF 1=0


INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




insert and update from a same stored procedure


in sql server 2005

hi i am creating  a stored procedure for a inserting and updating a table as follows

where id is identity.

my intention is when i supply the id value  then it performs update other wise it goes to insert

my table is as

CREATE TABLE [dbo].[TblAccountType](
    [id] [tinyint] IDENTITY(1,1) NOT NULL,
    [AccType] [varchar](20) NOT NULL,
    [Description] [varchar](max)  NULL)

and stored procedure is



create  proc [dbo].[SppInsertAccType]
(@id as tinyint =null,
@AccType as varchar(20),
@Description as varchar(max))
set nocount on
set transaction isolation level read uncommitted
begin tran
 if exists(select id from TblAccountType where id=@id)
update TblAccountType set AccType=@AccType,Description=@Description where id=@id
insert into TblAccountType(AccType,Description)values(@AccType,@Description)
commit tran

SQL timestamp in GridView and manually added update parameter for stored procedure in C#



I have a SQL table containing a timestamp column.  This table is displayed in ASP using GridView control.

The timestamp column is indicated in the DataKeyNames property of the GridView.

I'm using TemplateFields for all columns and a ItemTemplate HiddenField control connected to the timestamp column using Eval.

<asp:TemplateField ItemStyle-Wrap

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend