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


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

SQL Stored Procedure - if exists Update else Insert.

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :ASP.Net
 

Hey,

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 :)




View Complete Post


More Related Resource Links

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))
as
begin
set nocount on
set transaction isolation level read uncommitted
begin tran
 if exists(select id from TblAccountType where id=@id)
begin
update TblAccountType set AccType=@AccType,Description=@Description where id=@id
end
else
begin
insert into TblAccountType(AccType,Description)values(@AccType,@Description)
end
commit tran
end


Insert the results of a stored procedure into specific columns

  
Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

How do I insert values into different colunms but same row in a table using stored procedure

  
Hi everyone, I wish to create a scenario whereby my Stored Procedure will be able to do an Insert values into two colunms but single row of a table. For instance: AS BEGIN INSERT INTO Table1 (Message1, Number1) VALUES(@Message1, @Number1) END When I do the above stored procedure using SQL Express, and Excecute the stored procedure in VS2005 C# using ADO.Net, the table is updated but the values of Message1 and Number1 are inserted into different rows i.e. when value is inserted into Message1, Number1 is '0' and vice-versa. what I really want is for both Message1 and Number1 to be inserted with values at the same row. How do I acheive this please? Many thanks in advance.

Need to create a stored procedure on a db if that db exists..

  
IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

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. 

insert result of stored procedure in new table

  
hi how can i insert the result of stored procedure in the new table. Best Regards. Morteza

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

stored procedure: select and update

  
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?

GridView Insert clicking a button in footer row using stored procedure

  

Hi!! I need your help again.

I have a gridview with a button in the first column of the footer row and some controls (textbox, dropdowlist...) in the rest of the columns. I want to click that button and insert into a database table using an stored procedure the text properties of the controls in the other columns.

I don't know what's wrong... could you help me or post me a link to read it??

I'm setting the insert command with my stored procedure, I'm setting the storedprocedure as the insertcommandtype, I'm able to load the controls with correct values, but finally when I click the insert button that's not work!!!!

Thank you!!!


Insert ID field into SQL table programatically from Stored Procedure

  

Hi,

I have a table in SQL where I insert data (CatCodes) using C# code but I want to insert ID code (DealerID) with it as well. Following is my Stored Procedure and C# code:

PROCEDURE [dbo].[InfoInsert]
	-- Add the parameters for the stored procedure here
	@UserId uniqueidentifier,
	@Title char(32),
	@FirstName char(32),
	@LastName char(32),
	@CompanyName char(32),
	@Email char(32),
	@DealerID int OUTPUT
	
	
	
	
AS
BEGIN
SET NOCOUNT ON

	INSERT INTO [tblDealers] ([UserId], [Title], [FirstName], [LastName], [CompanyName], Email) 
        VALUES (@UserId, @Title, @FirstName, @LastName, @CompanyName, @Email)
        
        select @DealerID = SCOPE_IDENTITY()
        
       
       
END


 

C#:

protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
        {
            

            
         
            
            //Insert membership and personal data
            TextBox UserNameTextBox = (TextBox)CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName");
            SqlDataSource DataSource = (SqlDataSource)CreateUserWizardStep1.ContentTemplateContainer.FindControl("InsertExtraInfo");
           
            

            

            Membership

C# How to check prammatically if null value exists in database table (using stored procedure)?

  
Folks!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
UserID INT NULL,
Name NVARCHAR(50) NULL,
DateOfBirth DATETIME,
IsActive BIT,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID),
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO

INSERT INT

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()
        {
            InitializeComponent();
        }

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

How to write Stored Procedure for Insert Data & Execute it in MS SQL?

  

How to write Stored Procedure for Insert Data & Execute it in MS SQL?


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.
Stephen
Categories: 
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