SET @sql = '
-- Insert the ' + @TableName + ' table
CREATE PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END'这是存储过程的一部分 写完以后 点应用 然后管理 再重新打开 居然变成了:
SET @sql = '
-- Insert the ' + @TableName + ' table
ALTER PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END'CREATE --》 ALTER 我是用 SQL2000(SP4)的企业管理器 编辑存储过程的 不信你们也试试!!!如何解决此问题?????????
-- Insert the ' + @TableName + ' table
CREATE PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END'这是存储过程的一部分 写完以后 点应用 然后管理 再重新打开 居然变成了:
SET @sql = '
-- Insert the ' + @TableName + ' table
ALTER PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END'CREATE --》 ALTER 我是用 SQL2000(SP4)的企业管理器 编辑存储过程的 不信你们也试试!!!如何解决此问题?????????
CS中用阻时点鼠标右键居然可以放大,太神奇了
CREATE PROCEDURE xp_GenerateStoreProcess
(
@TableName nvarchar(200), -- the table name
@Prefixion nvarchar(10) = default -- the Prefixion of the table name
)
AS
BEGIN
-- make sure that there is only one primary key field
DECLARE @PKTotal INT
SELECT @PKTotal = SUM(IsNull( z.PKNum, 0)) FROM
(
SELECT PKNum = CASE WHEN EXISTS
(
SELECT 1 FROM sysobjects WHERE [xtype]='PK' AND [name] IN
(
SELECT [name] FROM sysindexes WHERE [indid] IN
(
SELECT [indid] FROM sysindexkeys WHERE [id] = a.[id] AND [colid]=a.[colid]
)
)
)
THEN 1
ELSE 0
END
FROM syscolumns a
LEFT JOIN systypes b ON a.[xtype]=b.[xusertype]
INNER JOIN sysobjects d ON a.[id]=d.[id] AND d.[xtype]='U' AND d.[name]<>'dtproperties'
LEFT JOIN syscomments e ON a.[cdefault]=e.[id]
LEFT JOIN sysproperties g ON a.[id]=g.[id] AND a.[colid]=g.[smallid]
LEFT JOIN sysproperties f ON d.[id]=f.[id] AND f.[smallid]=0
WHERE d.[name]=@TableName
) z IF( @PKTotal <> 1 )
BEGIN
SELECT -1
RETURN
END -- get the short name
DECLARE @ShortName NVARCHAR(200)
IF( LEFT( @TableName, LEN(@Prefixion)) = @Prefixion )
SET @ShortName = RIGHT( @TableName, LEN(@TableName)-LEN(@Prefixion))
ELSE
SET @ShortName = @TableName DECLARE @sql NVARCHAR(4000)
DECLARE @params NVARCHAR(1000)
DECLARE @fields NVARCHAR(1000)
DECLARE @values NVARCHAR(1000)
DECLARE @PKFiledType NVARCHAR(20)
SELECT @PKFiledType = UPPER(b.[name])
FROM syscolumns a
LEFT JOIN systypes b ON a.[xtype]=b.[xusertype]
INNER JOIN sysobjects d ON a.[id]=d.[id] AND d.[xtype]='U' AND d.[name]<>'dtproperties'
LEFT JOIN syscomments e ON a.[cdefault]=e.[id]
LEFT JOIN sysproperties g ON a.[id]=g.[id] AND a.[colid]=g.[smallid]
LEFT JOIN sysproperties f ON d.[id]=f.[id] AND f.[smallid]=0
WHERE d.[name]=@TableName
AND EXISTS
(
SELECT 1 FROM sysobjects WHERE [xtype]='PK' AND [name] IN
(
SELECT [name] FROM sysindexes WHERE [indid] IN
(
SELECT [indid] FROM sysindexkeys WHERE [id] = a.[id] AND [colid]=a.[colid]
)
)
)
---------------------------------------------------------------------
SET @sql = 'if exists (select * from sysobjects where id = object_id(N''[xp_Insert' + @ShortName + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [xp_Insert' + @ShortName + ']'
EXEC(@sql)
SET @params = ''
SET @fields = ''
SET @values = '' DECLARE @FieldName NVARCHAR(50)
DECLARE @DataType NVARCHAR(50)
DECLARE @TypeValue INT
DECLARE @Length INT
DECLARE @Scale INT
DECLARE @IsNullable INT
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
(
SELECT
a.[name] AS 'FieldName',
UPPER(b.[name]) AS 'DataType',
COLUMNPROPERTY(a.[id],a.[name],'PRECISION') AS 'Length',
IsNull(COLUMNPROPERTY(a.[id],a.[name],'Scale'),0) AS 'Scale',
a.[isnullable]
FROM syscolumns a
LEFT JOIN systypes b ON a.[xtype]=b.[xusertype]
INNER JOIN sysobjects d ON a.[id]=d.[id] AND d.[xtype]='U' AND d.[name]<>'dtproperties'
LEFT JOIN syscomments e ON a.[cdefault]=e.[id]
LEFT JOIN sysproperties g ON a.[id]=g.[id] AND a.[colid]=g.[smallid]
LEFT JOIN sysproperties f ON d.[id]=f.[id] AND f.[smallid]=0
WHERE d.[name]=@TableName
AND COLUMNPROPERTY( a.[id],a.[name],'IsIdentity')<>1
)
OPEN cur
FETCH NEXT FROM cur INTO @FieldName, @DataType, @Length, @Scale, @IsNullable
WHILE @@fetch_status=0
BEGIN
SET @params = @params + '
' +
CASE @DataType
WHEN 'VARCHAR' THEN ('@' + @FieldName + ' ' + @DataType + '(' + CAST( @Length AS NVARCHAR(10)) + ')' + (CASE @IsNullable WHEN 1 THEN ' = default' ELSE '' END) + ',')
WHEN 'NVARCHAR' THEN ('@' + @FieldName + ' ' + @DataType + '(' + CAST( @Length AS NVARCHAR(10)) + ')' + (CASE @IsNullable WHEN 1 THEN ' = default' ELSE '' END) + ',')
WHEN 'DECIMAL' THEN ('@' + @FieldName + ' ' + @DataType + '(' + CAST( @Length AS NVARCHAR(10)) + ',' + CAST( @Scale AS NVARCHAR(10)) + ')' + (CASE @IsNullable WHEN 1 THEN ' = default' ELSE '' END) + ',')
WHEN 'NUMERIC' THEN ('@' + @FieldName + ' ' + @DataType + '(' + CAST( @Length AS NVARCHAR(10)) + ',' + CAST( @Scale AS NVARCHAR(10)) + ')' + (CASE @IsNullable WHEN 1 THEN ' = default' ELSE '' END) + ',')
ELSE ('@' + @FieldName + ' ' + @DataType + (CASE @IsNullable WHEN 1 THEN ' = default' ELSE '' END) + ',')
END
SET @fields = @fields + '[' + @FieldName + '],' SET @values = @values + '@' + @FieldName + ',' FETCH NEXT FROM cur INTO @FieldName, @DataType, @Length, @Scale, @IsNullable
END
CLOSE cur
DEALLOCATE cur
SET @fields = LEFT( @fields, LEN(@fields)-1)
SET @values = LEFT( @values, LEN(@values)-1)
SET @sql = '
-- Insert the ' + @TableName + ' table
ALTER PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END' PRINT @sql
--EXEC(@sql)
--------------------------------------------------------------------- RETURN
END
GO
然后 关闭然后 再打开 看一看
SET @sql = '
-- Insert the ' + @TableName + ' table
CREATE PROCEDURE xp_Insert' + @ShortName + '
(
' + @params + '
@output ' + @PKFiledType + ' = default
)
ASBEGIN
INSERT INTO ' + @TableName +'( ' + @fields + ')
VALUES( ' + @values + ' ) SET @output = CAST( IDENT_CURRENT(''' + @TableName + ''') AS ' + @PKFiledType + ') RETURN
END'