--try
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Get_KeyValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Get_KeyValue]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Get_KeyValue
(
@TYPE nvarchar(128), -- table name
@KEY nvarchar(64) output
)
as declare @PID nvarchar(32),@strSql nvarchar(1024)
--get PRIMARY @PID
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(N'BW_Broker')
and a.id=b.id and a.colid=b.colid) select @PID as PID
-- get next Key
set @strSql = ' select @KEY = (max(cast( '+@PID
+' as int))+1) from '+ @TYPE
exec(@strSql)
select @KEY as NEWKeyGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON --问题是 @KEY 得不到正确的值
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Get_KeyValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Get_KeyValue]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Get_KeyValue
(
@TYPE nvarchar(128), -- table name
@KEY nvarchar(64) output
)
as declare @PID nvarchar(32),@strSql nvarchar(1024)
--get PRIMARY @PID
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(N'BW_Broker')
and a.id=b.id and a.colid=b.colid) select @PID as PID
-- get next Key
set @strSql = ' select @KEY = (max(cast( '+@PID
+' as int))+1) from '+ @TYPE
exec(@strSql)
select @KEY as NEWKeyGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON --问题是 @KEY 得不到正确的值
http://community.csdn.net/Expert/topic/4570/4570356.xml?temp=.5934717
的给分有点问题
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(N'BW_Broker')
and a.id=b.id and a.colid=b.colid) select @PID as PID
这一段就是错误的
@PID得到的是列名,怎么转成int?
@PID得到的是列名,怎么转成int?
=====>
如果楼主列名是数字型的,比如create table t([12] varchar(20))
[12]就是列名,他是可以转换为INT型的.
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(N'BW_Broker')
and a.id=b.id and a.colid=b.colid) select @PID as PID
这一段是正确的 目的就是取到列名
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(N'BW_Broker')
and a.id=b.id and a.colid=b.colid) select @PID as PID转换为INT型 是把主键值转换为INT型 主键是 nvarchar
===========>楼主,怎么还没醒悟啊,上面语句能得到主键吗?
use northwind
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Get_KeyValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Get_KeyValue]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Get_KeyValue
(
@TYPE nvarchar(128), -- table name
@KEY nvarchar(64) output
)
as declare @PID nvarchar(32),@strSql nvarchar(1024)
--get PRIMARY @PID
select @PID = name from syscolumns a where exists (
select * from sysindexkeys b where b.id=object_id(@TYPE)
and a.id=b.id and a.colid=b.colid) select @PID as PID
-- get next Key
set @strSql = N'select @KEY = (max(cast( '+@PID
+N' as int))+1) from '+ @TYPEexecute sp_executesql @strSql,N'@KEY nvarchar(64) output',@KEY outputselect @KEY as NEWKeyGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON --测试语句
declare @PIDd nvarchar(32)
exec Get_KeyValue 'region',@PIDd output
print @PIDd
--结果PID
--------------------------------
RegionID(1 row(s) affected)NEWKey
----------------------------------------------------------------
5(1 row(s) affected)5
这个地方应该出现的是@TYPE变量吧,我自做主张改了