34,590
社区成员
发帖
与我相关
我的任务
分享
USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('mylog') is not null
drop procedure mylog
go
create procedure [dbo].[mylog](@billno varchar(20),@month varchar(6))
as
begin
declare @name varchar(50),@type varchar(10),@sql Nvarchar(max),@procname varchar(100)
set @type=left(@billno,2)
set @name='dbo.KFT_DebugLog2_'+@month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
if @type='KH'
begin
set @procname='applynew'
end
if @type='TJ' or @type='HJ' or @type='YJ'
begin
set @procname='exchange'
end
if @type='WX'
begin
set @procname='repair'
end
set @sql='select * from @tablename where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@tablename as varchar(50),@t as varchar(50),@b as varchar(50)',@name,@procname,@billno
print @sql
end
set @sql='select * from '+@name+' where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@t as varchar(50),@b as varchar(50)',@procname,@billno
PRINT @sql
就像楼上说的,执行sp_executesql时,表名不能是传进去的参数,所以为了正确执行你得用变量代替里面的表名。
这样就可以正确的执行了,但是打印出来的@sql还是原来的,因为打印的时候里面的@t和@b就是一个字符 DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
你看看这种写法也是吧变量放在字符串里面,然后在sp_executesql中声明了@sql中的@oid是变量,在传入参数,这跟我的不一样吗[/quote]
这种方式可以,但是不能print,得是exec(@sql) ,楼主那个直接exce有问题吗?[/quote]
不太明白你的意思,直接exec肯定是不行的吧,用exec sp_executesql 不是应可以吗[/quote]
对的,我说的就是exec sp_executesql 有问题吗?如果想用print调试,就得拼接字符串。如果想用这种输入输出参数的,就得用exec sp_executesql
USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('mylog') is not null
drop procedure mylog
go
create procedure [dbo].[mylog](@billno varchar(20),@month varchar(6))
as
begin
declare @name varchar(50),@type varchar(10),@sql Nvarchar(max),@procname varchar(100)
set @type=left(@billno,2)
set @name='dbo.KFT_DebugLog2_'+@month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
if @type='KH'
begin
set @procname='applynew'
end
if @type='TJ' or @type='HJ' or @type='YJ'
begin
set @procname='exchange'
end
if @type='WX'
begin
set @procname='repair'
end
set @sql='select * from @tablename where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@tablename as varchar(50),@t as varchar(50),@b as varchar(50)',@name,@procname,@billno
--PRINT @sql
end
[/quote]
有问题啊,报底下这个错误,我不是在sp_executesql中声明过了吗
消息 1087,级别 16,状态 1,第 1 行
必须声明表变量 "@tablename"。
[/quote]
表名不能是参数的。
USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('mylog') is not null
drop procedure mylog
go
create procedure [dbo].[mylog](@billno varchar(20),@month varchar(6))
as
begin
declare @name varchar(50),@type varchar(10),@sql Nvarchar(max),@procname varchar(100)
set @type=left(@billno,2)
set @name='dbo.KFT_DebugLog2_'+@month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
if @type='KH'
begin
set @procname='applynew'
end
if @type='TJ' or @type='HJ' or @type='YJ'
begin
set @procname='exchange'
end
if @type='WX'
begin
set @procname='repair'
end
set @sql='select * from '+ @name+' where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@t as varchar(50),@b as varchar(50)',@procname,@billno
--PRINT @sql
end
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
你看看这种写法也是吧变量放在字符串里面,然后在sp_executesql中声明了@sql中的@oid是变量,在传入参数,这跟我的不一样吗[/quote]
这种方式可以,但是不能print,得是exec(@sql) ,楼主那个直接exce有问题吗?[/quote]
不太明白你的意思,直接exec肯定是不行的吧,用exec sp_executesql 不是应可以吗[/quote]
对的,我说的就是exec sp_executesql 有问题吗?如果想用print调试,就得拼接字符串。如果想用这种输入输出参数的,就得用exec sp_executesql
USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('mylog') is not null
drop procedure mylog
go
create procedure [dbo].[mylog](@billno varchar(20),@month varchar(6))
as
begin
declare @name varchar(50),@type varchar(10),@sql Nvarchar(max),@procname varchar(100)
set @type=left(@billno,2)
set @name='dbo.KFT_DebugLog2_'+@month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
if @type='KH'
begin
set @procname='applynew'
end
if @type='TJ' or @type='HJ' or @type='YJ'
begin
set @procname='exchange'
end
if @type='WX'
begin
set @procname='repair'
end
set @sql='select * from @tablename where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@tablename as varchar(50),@t as varchar(50),@b as varchar(50)',@name,@procname,@billno
--PRINT @sql
end
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
你看看这种写法也是吧变量放在字符串里面,然后在sp_executesql中声明了@sql中的@oid是变量,在传入参数,这跟我的不一样吗[/quote]
这种方式可以,但是不能print,得是exec(@sql) ,楼主那个直接exce有问题吗? DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('mylog') is not null
drop procedure mylog
go
create procedure [dbo].[mylog](@billno varchar(20),@month varchar(6))
as
begin
declare @name varchar(50),@type varchar(10),@sql Nvarchar(max),@procname varchar(100)
set @type=left(@billno,2)
set @name='dbo.KFT_DebugLog2_'+@month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
if @type='KH'
begin
set @procname='applynew'
end
if @type='TJ' or @type='HJ' or @type='YJ'
begin
set @procname='exchange'
end
if @type='WX'
begin
set @procname='repair'
end
set @sql='select * from @tablename where procname like ''%@t%'' and parameters like ''%@b%'''
exec sp_executesql @sql,N'@tablename as varchar(50),@t as varchar(50),@b as varchar(50)',@name,@procname,@billno
--PRINT @sql
end
[/quote] DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
你看看这种写法也是吧变量放在字符串里面,然后在sp_executesql中声明了@sql中的@oid是变量,在传入参数,这跟我的不一样吗[/quote]
这种方式可以,但是不能print,得是exec(@sql) ,楼主那个直接exce有问题吗?[/quote]
不太明白你的意思,直接exec肯定是不行的吧,用exec sp_executesql 不是应可以吗 DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'class ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName+' WHERE id = @OID ORDER BY id DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
你看看这种写法也是吧变量放在字符串里面,然后在sp_executesql中声明了@sql中的@oid是变量,在传入参数,这跟我的不一样吗USE [KFT_LOG]
GO
/****** Object: StoredProcedure [dbo].[getmylog] Script Date: 09/19/2017 15:05:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('mylog') IS NOT NULL
DROP PROCEDURE mylog
GO
CREATE PROCEDURE [dbo].[mylog]
(
@billno VARCHAR(20) ,
@month VARCHAR(6)
)
AS
BEGIN
DECLARE @name VARCHAR(50) ,
@type VARCHAR(10) ,
@sql NVARCHAR(MAX) ,
@procname VARCHAR(100)
SET @type = LEFT(@billno, 2)
SET @name = 'dbo.KFT_DebugLog2_' + @month
/*
set @name='dbo.KFT_DebugLog2_'+convert(varchar(4),GETDATE(),120)+cast(DATEPART(MM,GETDATE()) as varchar(10))
自动获取当前月份作为查询表名
*/
IF @type = 'KH'
BEGIN
SET @procname = 'applynew'
END
IF @type = 'TJ'
OR @type = 'HJ'
OR @type = 'YJ'
BEGIN
SET @procname = 'exchange'
END
IF @type = 'WX'
BEGIN
SET @procname = 'repair'
END
SET @sql = 'select * from ' + @name + ' where procname like ''%'
+ @procname + '%'' and parameters like ''%' + @billno + '%'''
EXEC(@sql)
PRINT @sql
END
--方法1
--你把%写进参数值里,像下面这样
set @sql='select * from '+@name+' where procname like @t and parameters like @b%'
exec sp_executesql @sql,N'@t as varchar(50),@b as varchar(50)',N'%'+@procname+'%',N'%'+@billno+'%'
PRINT @sql
--方法2
--或者你这样拼接sql也是可以执行的
set @sql='select * from '+@name+' where procname like ''%''+@t+''%'' and parameters like ''%''+@b+''%'''
exec sp_executesql @sql,N'@t as varchar(50),@b as varchar(50)',@procname,@billno
PRINT @sql