Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2009-02-16 : 22:58:49

//Is it possible to make storedprocedure like :
//tbl structure :tbl1(id,col1,col2,...) tbl2(id,col1,...,chkcol)
// select id,col1,col2... from tbl1,one of the rows already in tbl2{just change tbl2.chkcol=1},otherwise insert required fields of that row into tbl2

thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 23:15:29

[code]
CREATE PROC [dbo].[samplesp]
(
@ID INT
)
AS
SET NOCOUNT ON
BEGIN

if EXISTS (select * from tbl2 WHERE id = @id)
BEGIN
UPDATE Tbl2
SET chkcol2 = 1
WHERE id = @id
END
ELSE
BEGIN
INSERT INTO tbl2
(
id,col1,co2,........
)
SELECT
id,col1,col2,....... from tbl1
ENd

END
SET NOCOUNT OFF
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-16 : 23:17:03

declare @cnt int
select @cnt = Count(1) from tbl1 where id = @id and col1 = @col1------------

update tbl1
set chkcol = 1
where id = @id and col1 = @col1 AND @cnt <> 0 ----------

insert into tbl1
columns
select
input parameters
where @cnt = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources