MS-SQL tárolt eljárás
Beküldte pzoli - 2011, május 23 - 5:50du
Minta dinamikusan összeállított SQL futtatásra:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE CreateValuesRecord @DocID INT, @DocTypeID INT AS DECLARE @Script nvarchar(4000) DECLARE @TblName NCHAR(64) SELECT @TblName=[DocType] FROM [DocType] WHERE [DocTypeID] = @DocTypeID SELECT @Script = 'INSERT INTO ['+RTRIM(@TblName)+'](DocID) '+ 'VALUES( '+Str(@DocID)+')' EXEC(@Script) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Minta ideiglenes tábla használatára:
CREATE PROCEDURE SelectTopDoc @TopLowValue INT, @DocRegBookID INT AS SELECT RegNum, DateOfDisp, [DateTime]=DocActDiary.[DateTime], DocID=DocActDiary.DocID INTO #Registered FROM DocRegister JOIN DocActDiary ON DocRegister.DocActDiaryID=DocActDiary.DocActDiaryID WHERE (DocRegister.DocRegBookID=@DocRegBookID) AND (RegNumber >= @TopLowValue) AND (SubNum IS NULL) SELECT DocIncoming.CustomerID, DocIncoming.SubjID, DocIncoming.DocKindID, DocActDiary.DocID INTO #Incoming FROM DocIncoming JOIN DocActDiary ON DocIncoming.DocActDiaryID=DocActDiary.DocActDiaryID SET CONCAT_NULL_YIELDS_NULL OFF SELECT #Registered.RegNum, #Registered.DateOfDisp, #Registered.[DateTime], #Incoming.CustomerID, #Incoming.SubjID, #Incoming.DocKindID, #Registered.DocID, [Name] = LastName+' '+MiddleName+' '+FirstName, Subject.Subject FROM #Registered JOIN #Incoming ON #Registered.DocID=#Incoming.DocID JOIN Customer ON #Incoming.CustomerID = Customer.CustomerID JOIN Subject ON #Incoming.SubID = Subject.SubID ORDER BY RegisteringNumber GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
- A hozzászóláshoz be kell jelentkezni