我有这样的程序,守则也照此办理:
ALTER PROCEDURE [dbo].[usp_Gen_Proc]
(@ID INT )
AS
Begin
IF OBJECT_ID( tempdb..#procedure ) IS NOT NULL
DROP TABLE #procedure
DECLARE @Name VARCHAR(100)
DECLARE @SQL VARCHAR(MAX)
DECLARE @Script VARCHAR(MAX),
@DB VARCHAR(100),
@Schema VARCHAR(100),
@Proc_Name VARCHAR(max),
@BR CHAR(2)
SET @BR = CHAR(10)+ CHAR(13)
SET NOCOUNT ON
SELECT @Name= [Procedure_Name] FROM dbo.datsource
WHERE [ID] = @ID
SELECT @Proc_Name = PARSENAME(@Name, 1) ,
@Schema = PARSENAME(@Name, 2) ,
@DB = PARSENAME(@Name, 3)
CREATE TABLE #procedure ( script VARCHAR(MAX) )
EXEC( INSERT INTO #procedure(script)
SELECT definition FROM +@DB+ .sys.all_sql_modules sq
WHERE sq.object_id = (SELECT object_id FROM +@DB+ .sys.objects
WHERE type = P AND name = +@Proc_Name+ ) )
SET @Script = Use +@BR +@DB + @BR+ Go +@BR
SELECT @Script = @Script+script
FROM #procedure
DECLARE @pos INT =7500
SELECT @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,@pos)
PRINT SUBSTRING(@Script,1,@Pos)
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = ( LEN(@script) / 8000 )
WHILE @Counter < @TotalPrints
BEGIN
SET @Counter = @Counter + 1
PRINT SUBSTRING(@script,@pos+1,7500)
SET @pos = @pos+7500
SELECT @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,@pos)
END
END
Basically what the procedure is doing it just prints the procedure code. But the problem I am facing is when I am printing it i am loosing some characters in between.
请允许我知道我究竟是哪里错了?