insert and select stored procedure

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 2008 Forums

Transact-SQL (2008)

insert and select stored procedure

Author

Topic

tikoti
Starting Member

4 Posts

Posted - 2012-09-05 : 03:41:03

Hi all,

I am quite new in this world and I am trying to do something more elaborated than what I've done before

I have created a stored procedure in sql server 2008 that checks if an id is in the table and if not, inserts it. After that, makes a simple select to return the record set, with that record set I pretend to update the row in vbscript.

What I've done so far...


USE [db1]
GO

/****** Object:  StoredProcedure [dbo].[sp_name]    Script Date: 09/05/2012 00:16:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_name](
@id_id int)

AS IF NOT EXISTS (SELECT * FROM table1 WHERE id_id = @id_id)
BEGIN 
	INSERT INTO table1(id_id) VALUES(@id_id)
END 

SELECT *
FROM [dbo].[table1]
WHERE id_id=@id_id

GO

The procedure works as expected but when trying to update the returned recordset in vbscript it fails if the insert has been executed. It throws the following error
Operation_is_not_allowed_when_the_object_is_closed

I think that when the insert is executed the procedure doesn't return the recordset and I fail to update it from vbscript.

I would appreciate your help

Thank you!!!

sateeshGenpact
Starting Member

6 Posts

Posted - 2012-09-05 : 04:25:25

in SQL SERVER if we are not giving Begin , End that will consider only one sql statement..

try this way it should work..

CREATE PROCEDURE [dbo].[sp_name](
@id_id int)

AS

BEGIN

IF NOT EXISTS (SELECT * FROM table1 WHERE id_id = @id_id)
BEGIN
INSERT INTO table1(id_id) VALUES(@id_id)
END

SELECT *
FROM [dbo].[table1]
WHERE id_id=@id_id

END

Sateesh

tikoti
Starting Member

4 Posts

Posted - 2012-09-05 : 04:57:03

Thank you!!!

great help!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-09-05 : 18:59:46

Just insert it and ignore the duplicate key error (you do have a unique constraint in ID, right?). Much simpler and more likely to work when 2 or more users get on at the same time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources