We help IT Professionals succeed at work.

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

JimmyJack123
JimmyJack123 used Ask the Experts™
on
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

Open in new window


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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer and Administrator
Commented:
In a user defined function you can not change the world outside the function, so you can not execute DDL or DML operations. You should simply rewrite the complete function into a stored procedure.
If the function is called in a query then you should rewrite the query to a cursor and call cycled the SP per row.
Top Expert 2012
Commented:
And you certainly cannot do the following in a function:
INSERT INTO CorresMetaData
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.