@wloochacz musze przyznać że nie dawało mi to spokoju i znalazłem inne obejście ;)
Jest mozliwość wywołania procedury z funkcji uzytkownika która zmienia dane, pod pewnymi warunkami:
- będzie to funkcja CLR
- będzie miała dostęp do danych (DataAccessKind.Read)
- nie będzie korzystała z połaczenia "contextowego"
- będzie jako UNSAFE/EXTERNAL_ACCESS
Postanowiłem to przetestować.
Przygotowanie danych:
Kopiuj
IF OBJECT_ID('dbo.foo', 'U') IS NOT NULL
DROP TABLE dbo.foo;
CREATE TABLE foo(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
val1 INT,
val2 INT)
go
IF OBJECT_ID('dbo.foo2', 'U') IS NOT NULL
DROP TABLE dbo.foo2;
go
CREATE TABLE foo2(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
val1 INT)
GO
IF OBJECT_ID('dbo.fooid', 'U') IS NOT NULL
DROP TABLE dbo.fooid;
go
CREATE TABLE fooid(
id INT NOT NULL PRIMARY KEY
)
go
INSERT INTO foo2 (val1) VALUES (1)
go 10000
INSERT INTO fooid VALUES(0)
go
IF OBJECT_ID('[dbo].[pGetFooID]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[pGetFooID];
go
CREATE PROCEDURE [dbo].[pGetFooID]
AS
DECLARE @id INT
UPDATE fooid SET id=id+1
SELECT @id = id FROM fooid
RETURN @id
GO
Kod funkcji CLR:
Kopiuj
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,IsDeterministic = false)]
public static SqlInt32 pFooCLR(SqlString connString)
{
using (SqlConnection cnn = new SqlConnection(connString.ToString()))
{
cnn.Open();
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.pGetFooID";
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int i = (int)returnParameter.Value;
return (SqlInt32)i;
}
}
}
T-SQL do tworzenia ASSEMBLY, należy zmienić nazwę bazy danych
Wynik testu:
"Chamska sklejka":
Kopiuj
DECLARE @SQL nvarchar(MAX)
SET @SQL = N'declare @i int
'
SELECT @SQL = @SQL + 'exec @i = [dbo].[pGetFooID]
insert into foo (val1, val2) values ('+ CONVERT(nvarchar(10),val1)+',@i)
'
FROM foo2
EXEC sp_executesql @SQL
Czas wykonania: 00:03:12
Kursor:
Kopiuj
DECLARE @I INTEGER
DECLARE @VAL INTEGER
DECLARE SH CURSOR FAST_FORWARD FOR
SELECT VAL1 FROM FOO2
OPEN SH
FETCH NEXT FROM SH INTO @VAL
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @I = pGetFooID
PRINT @I
INSERT INTO FOO (VAL1, VAL2) VALUES (@VAL,@I)
FETCH NEXT FROM SH INTO @VAL
END
CLOSE SH
DEALLOCATE SH
Czas wykonania: 00:00:10
Funkcja CLR:
Tu małe wyjaśnienie, przekazuje connectionstringa po którym bede się łączył w celu wykonania procedury, istotne jest aby znalazł się w nim zdefiniowany parametr Enlist=false, w celu nie wchodzenia w kolizję z transakcją insertu:
Kopiuj
insert into foo
select val1,[dbo].[pFooCLR](N'Data Source=nazwaserwera;Initial Catalog=nazwabazydanych;User Id=user;Password=pass;Enlist=false') from foo2
Czas wykonania: 00:00:09
Czyli CLR poszedł najszybciej. Oczywiście niczego to nie dowodzi, a raczej tego, że narzut na czas wykonania kursora jednak jest. Zakładajac, że CLR ma również swój narzut na wykonanie, a jest szybszy => z kursorami trzeba ostrożnie ;)
