Using INSERT in a SQL Server SP saves data during SP execution, but the data are gone when execution

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

SQL Server Development (2000)

Using INSERT in a SQL Server SP saves data during SP execution, but the data are gone when execution

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-09 : 08:58:21

Yuri writes "I use an INSERT statement in a SQL Server stored procedure; if I use a SELECT statement within the SP, the data are returned, but when I run a SELECT statement outside the SP, the data aren't there. What gives here? Here's an example:

Some of the code within the SP:

INSERT INTO
dbo.tbl_ModelApplicants (sFullName)
VALUES
(@sColData)

SELECT * FROM dbo.tbl_ModelApplicants

sFullName
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Luke Sprague

When I open a new window within SQL Query Analyzer after the SP finishes and perform a SELECT, I get the result:

SELECT * FROM dbo.tbl_ModelApplicants

sFullName
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-09 : 09:53:41

Check to make sure you haven't rolled back a transaction ... soulds like that is what is happening.

begin transaction

INSERT INTO
dbo.tbl_ModelApplicants (sFullName)
VALUES
(@sColData)

select * from dbo.tbl_ModelApplicants where sFullName = @sColData

ROLLBACK TRANSACTION

should give you the same result.

Looks like you need to explicitly commit, or do error checking to see where you are rolling back.
*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources