存储过程连接，搞半天了，高手看一下@baseSql如何连接

[sdweihailh]

create proc PROK1
@tablename nvarchar(20),
@fieldname nvarchar(20),
@userName nvarchar(20) output
as
DECLARE @TJ VARCHAR(8)
set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE()))
set @TJ='PU'+@TJ
DECLARE @baseSql Nvarchar(3000)
set @baseSql='select @userName=MAX(SUBSTRING(@fieldname,9,4)) from '+@tablename+'where left(@fieldname,4)='+@TJ
EXEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT
print @userName
GO
--调用
declare @userName nvarchar(10)
exec PROK1 LSPORV,@userName output
select @userName
--

[發糞塗牆]

create proc PROK1 @tablename nvarchar(20), @fieldname nvarchar(20), @userName nvarchar(20) output as DECLARE @TJ VARCHAR(8) set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE())) set @TJ='PU'+@TJ DECLARE @baseSql Nvarchar(3000) set @baseSql='select @userName=MAX(SUBSTRING(@fieldname,9,4)) from '+@tablename+'where left(@fieldname,4)='+@TJ print @basesql 这里就print出来了 EXEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT print @userName GO --调用 declare @userName nvarchar(10) exec PROK1 LSPORV,@userName output select @userName --

[sdweihailh]

select @userName=MAX(SUBSTRING(@fieldname,9,4)) from LSPORV where left(@fieldname,4)=PU201212 消息 137，级别 15，状态 2，第 1 行 必须声明标量变量 "@fieldname"。 (1 行受影响)

[-晴天]

语句有点问题,另外,参数也不对. create proc PROK1 @tablename nvarchar(20), @fieldname nvarchar(20), @userName nvarchar(20) output as DECLARE @TJ VARCHAR(8) set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE())) set @TJ='PU'+@TJ DECLARE @baseSql Nvarchar(3000) set @baseSql='select @userName=MAX(SUBSTRING(@fieldname,9,4)) from '+@tablename+' where left(@fieldname,4)='''+@TJ+'''' XEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT print @userName GO --调用 declare @userName nvarchar(10) exec PROK1 'LSPORV','columnname',@userName output select @userName go drop procedure prok1 把exec PROK1 中的 columnname 改成相应的列名.

[sdweihailh]

试了变量没有传递进去

[-晴天]

create proc PROK1 @tablename nvarchar(20), @fieldname nvarchar(20), @userName nvarchar(20) output as DECLARE @TJ VARCHAR(8) set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE())) set @TJ='PU'+@TJ DECLARE @baseSql Nvarchar(3000) set @baseSql='select @userName=MAX(SUBSTRING('+@fieldname+',9,4)) from '+@tablename+' where left(@fieldname,4)='''+@TJ+'''' EXEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT print @userName GO --调用 declare @userName nvarchar(10) exec PROK1 'LSPORV','columnname',@userName output select @userName go drop procedure prok1

[sdweihailh]

create table LSPORV (cgdh nvarchar(12)) INSERT INTO LSPORV SELECT 'PU2012120001' UNION SELECT 'PU2012120002' UNION SELECT 'PU2012120003' create proc PROK1 @tablename nvarchar(20), @fieldname nvarchar(20), @userName nvarchar(20) output as DECLARE @TJ VARCHAR(8) set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE())) set @TJ='PU'+@TJ DECLARE @baseSql Nvarchar(3000) set @baseSql='select @userName=MAX(SUBSTRING('+@fieldname+',9,4)) from '+@tablename+' where left(@fieldname,4)='''+@TJ+'''' EXEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT print @userName GO --调用 declare @userName nvarchar(10) exec PROK1 'LSPORV','cgdh',@userName output select @userName go 消息 137，级别 15，状态 2，第 1 行 必须声明标量变量 "@fieldname"。 (1 行受影响)

[發糞塗牆]

改成这样

--create table LSPORV
--(cgdh nvarchar(12))
--INSERT INTO LSPORV
--SELECT 'PU2012120001' UNION
--SELECT 'PU2012120002' UNION
--SELECT 'PU2012120003' 
--GO
ALTER  proc PROK1
@tablename nvarchar(20),
@fieldname nvarchar(20),
@userName nvarchar(20) output
as 
DECLARE @TJ VARCHAR(8)
set @TJ=CONVERT(VARCHAR,DATEPART(YYYY,getDATE()))+ CONVERT(VARCHAR,DATEPART(MM,getDATE()))
set @TJ='PU'+@TJ
DECLARE @baseSql Nvarchar(3000)
set @baseSql='select @userName=MAX(SUBSTRING('+@fieldname+',9,4)) from '+@tablename+' where left('+@fieldname+',4)='''+@TJ+''''
EXEC sp_executesql @baseSql,N'@userName varchar(80) OUTPUT',@userName OUTPUT 
print @userName
GO
--调用
declare @userName nvarchar(10)
exec PROK1 'LSPORV','cgdh',@userName output 
select @userName
go

[sdweihailh]

TO DBA_Huangzj AND qianjin036a 你们执行成功了吗？返回是PU2012120003，我用的SQL2005怎么一直执行不成功！

[sdweihailh]

结果为NULL

[sdweihailh]

ok，谢谢，搞定了