June 4, 2013 at 3:37 am
Hai Friends,
i made on application my input fields are name,class.
where already ve the ID,and name means go to update otherwise ll make on insert.
My procedure is:
=============
alter procedure update_use
(
@name varchar(20),
@class nvarchar(30)
)
as
begin
declare @der varchar(30),
@id int
select @id=id from grts
if exists (select * from grts where id=@id)
begin
set @der='update grts set name=@name,class=@class where id=@id'
end
else
begin
insert into grts (name,class) values(@name,@class)
end
end
what was mY error in that?
June 4, 2013 at 3:47 am
What are you trying to do?
If you are creating an INSERT/UPDATE procedure, you probably need to pass the id and do the existence check based on that
If passing the id is not possible, existence check should be based on "name" or some similar column, it cannot be based on id which is not passed at all.
alter procedure update_use
(
@name varchar(20),
@class nvarchar(30)
)
as
begin
declare @der varchar(30),
@id int
select @id=id from grts -- Mistake 1, This will mostly return the same id
if exists (select * from grts where id=@id) -- Mistake 2, This doesn't make sense as it will always exist because of your code one line above unless there is no row in grts
begin
set @der='update grts set name=@name,class=@class where id=@id'
--Why are you using Dynamic SQL?
end
ELSE -- This will never get called because of your previous mistakes
begin
insert into grts (name,class) values(@name,@class)
end
end
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 3:50 am
raghuldrag (6/4/2013)
what was mY error in that?
I don't know - you can see your screen; we can't. But I'd guess that your problem is you don't have an [font="Courier New"]EXEC sp_execute @der [/font]statement, and even if you did, the variables you used wouldn't be visible in the context in which the UPDATE statement runs. Much better to remove the quotation marks and just run the plain UPDATE statement.
John
June 4, 2013 at 3:50 am
Hai ,
Then How call update and insert in that procedure
June 4, 2013 at 3:57 am
alter procedure update_use
(
@name varchar(20),
@class nvarchar(30)
)
as
begin
declare @id int
select @id=id from grts where name=@name
if exists (select * from grts where id=@id)
begin
update grts set name=@name,class=@class where id=@id
end
else
begin
insert into grts (name,class) values(@name,@class)
end
end
June 4, 2013 at 4:00 am
raghuldrag (6/4/2013)
Hai ,Then How call update and insert in that procedure
As I said earlier, the checking that you are doing using EXISTS needs to be changed
If you are checking it using id, you will have to pass that as an INPUT parameter else you will have to use something like "name" column
You can also avoid using Dynamic SQL here as it is not need at all
We would require the structure of the table involved to give any other suggestions
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 4:04 am
sumadevu (6/4/2013)
declare @id intselect @id=id from grts where name=@name
if exists (select * from grts where id=@id)
begin
update grts set name=@name,class=@class where id=@id
end
You can remove some redundant code as done below..
if exists (select * from grts where name=@name)
begin
update grts set name=@name,class=@class where name=@name
end
Note: This will work only if the column "name" is UNIQUE.
If the "name" column is not unique, you will have to use some column which is unique across all rows in the table
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 6:08 am
ALTER PROCEDURE update_use
(
@name varchar(20),
@class nvarchar(30)
)
AS
UPDATE grts SET class = @class WHERE name = @name
IF @@ROWCOUNT = 0
INSERT INTO grts (name, class) VALUES (@name, @class)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 4, 2013 at 6:29 am
ChrisM@Work (6/4/2013)
ALTER PROCEDURE update_use(
@name varchar(20),
@class nvarchar(30)
)
AS
UPDATE grts SET class = @class WHERE name = @name
IF @@ROWCOUNT = 0
INSERT INTO grts (name, class) VALUES (@name, @class)
Wow. This is the shortest INSERT/UPDATE procedure I have seen.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 6:30 am
Kingston Dhasian (6/4/2013)
ChrisM@Work (6/4/2013)
ALTER PROCEDURE update_use(
@name varchar(20),
@class nvarchar(30)
)
AS
UPDATE grts SET class = @class WHERE name = @name
IF @@ROWCOUNT = 0
INSERT INTO grts (name, class) VALUES (@name, @class)
Wow. This is the shortest INSERT/UPDATE procedure I have seen.
That's because it's the least number of rows 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 4, 2013 at 6:57 am
This is posted in a SQL Server 2008 forum, why not use MERGE?
June 4, 2013 at 8:37 pm
ChrisM@Work (6/4/2013)
Kingston Dhasian (6/4/2013)
ChrisM@Work (6/4/2013)
ALTER PROCEDURE update_use(
@name varchar(20),
@class nvarchar(30)
)
AS
UPDATE grts SET class = @class WHERE name = @name
IF @@ROWCOUNT = 0
INSERT INTO grts (name, class) VALUES (@name, @class)
Wow. This is the shortest INSERT/UPDATE procedure I have seen.
That's because it's the least number of rows 😀
Perhaps not as short but my preference would be something like:
ALTER PROCEDURE update_use
(
@name varchar(20),
@class nvarchar(30)
)
AS
MERGE grts t
USING (SELECT name=@name, class=@class) s
ON s.name = t.name
WHEN MATCHED
THEN SET class = s.class
WHEN NOT MATCHED
THEN INSERT (name, class) VALUES (s.name, s.class);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 4, 2013 at 8:39 pm
Lynn Pettis (6/4/2013)
This is posted in a SQL Server 2008 forum, why not use MERGE?
I agree! I just didn't see your suggestion before I posted mine.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply