Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

I need it to do a Read/Insert or Update/commit/read while keeping the row locked

Avatar of JimmyJack123
JimmyJack123 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
3 Comments1 Solution543 ViewsLast Modified:
Hi There Experts!

Wondering if someone can help me with this create function I am trying to make in SQL2008
I need it to do a Read/Insert or Update/commit/read while keeping the row locked.

This is the script so far,

CREATE FUNCTION [dbo].[fnCorresMetaData] (@CorroID char(48), @XMLPayload xml, @UpdCount smallint)  
RETURNS TABLE
AS
BEGIN
--Need to lock row to prevent double read/insert
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	-- Check If Row exists
	SELECT CorroID,XMLPayload,UpdCount FROM CorresMetaData
	WHERE CorroID = @CorroID
	IF @@ROWCOUNT = 0
		-- If no row exists create one
		BEGIN
			INSERT INTO CorresMetaData
				(CorroID
				, XMLPayload
				,UpdCount)
			VALUES
				(@CorroID
				,@XMLPayload
				,1)
		END
	ELSE
		-- If row exists append input xml into existing xml and increment UpdCount
		BEGIN 
			UPDATE CorresMetaData
				SET XMLPayload = XMLPayload&@XMLPayload,UpdCount = UpdCount+1
				WHERE CorroID = @CorroID
		END
	COMMIT
	-- Commit Update or Insert then retrieve.
	RETURN SELECT CorroID,XMLPayload,UpdCount
       	       	FROM CorresMetaData
		WHERE CorroID = @CorroID	

END

However, i get the error message when I go to parse it
CREATE FUNCTION is not currently supported in Transact-SQL Intellisense

What am i missing??

I am sure there is a simple explination and am sure this is SQL101, but I am just having a brain explosion.

Thanks for your help!
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté FarkasFlag of Hungary imageSQL Server Consultant
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers