ALTER PROCEDURE [dbo].[PRC_QUERY]
(@P_TABLENAME VARCHAR(MAX),
@P_COLUMNS NVARCHAR(2000),
@P_CONDITION NVARCHAR(1000)=NULL,
@P_SORT NVARCHAR(100)=NULL,
@P_DIR VARCHAR(4),
@P_PAGEINDEX INT,
@P_PAGESIZE INT,
@P_STOREPROCEDURE NVARCHAR(1000),
@P_SPPARAMETER NVARCHAR(1000),
@P_TOTALRECORDS INT=0 OUTPUT)
AS
SET NOCOUNT ON
DECLARE @V_SQL NVARCHAR(MAX)
DECLARE @V_START INT
DECLARE @V_TOP INT
DECLARE @V_TABLE NVARCHAR(MAX)
DECLARE @MY_TABLE NVARCHAR(MAX)
DECLARE @OUT_TABLE NVARCHAR(MAX)
BEGIN
--查询总记录数
SET @V_TABLE=@P_TABLENAME
SET @P_TOTALRECORDS=0
IF @P_STOREPROCEDURE IS NOT NULL AND @P_STOREPROCEDURE<>''
BEGIN
print @P_STOREPROCEDURE+' '+@P_SPPARAMETER+',@OUT_TABLE OUT'
IF @P_SPPARAMETER IS NOT NULL AND @P_SPPARAMETER<>''
BEGIN
SET @V_SQL=@P_STOREPROCEDURE+' '+@P_SPPARAMETER+',@OUT_TABLE OUT'
exec sp_executesql @V_SQL,N'@OUT_TABLE NVARCHAR(MAX) OUT',@OUT_TABLE OUT
END
ELSE
BEGIN
SET @V_SQL=@P_STOREPROCEDURE+' @OUT_TABLE OUT'
exec sp_executesql @V_SQL,N'@OUT_TABLE NVARCHAR(MAX) OUT',@OUT_TABLE OUT
END
if(@P_PAGESIZE=0)
begin
SET @V_SQL=@OUT_TABLE
IF @P_CONDITION IS NOT NULL AND @P_CONDITION<>''
BEGIN
SET @V_SQL='SELECT * FROM ('+@V_SQL+') TEMP_VIEW WHERE '+@P_CONDITION
END
IF @P_SORT IS NOT NULL AND @P_SORT<>''
BEGIN
SET @V_SQL=@V_SQL+' ORDER BY '+@P_SORT+' '+@P_DIR
END
exec sp_executesql @V_SQL
RETURN
end
SET @V_TABLE='('+@OUT_TABLE+') TEMP_VIEW'
print @V_TABLE
END
SET @V_SQL='SELECT @P_TOTALRECORDS=COUNT(1) FROM '+@V_TABLE+' WHERE 1=1 '
IF @P_CONDITION IS NOT NULL AND @P_CONDITION <>''
BEGIN
SET @V_SQL=@V_SQL+' AND '+@P_CONDITION
END
exec sp_executesql @V_SQL,N'@P_TOTALRECORDS INT OUT',@P_TOTALRECORDS OUT
SET @V_START=(@P_PAGEINDEX-1)*@P_PAGESIZE
SET @V_TOP=@P_PAGEINDEX*@P_PAGESIZE
SET @V_SQL='SELECT '+@P_COLUMNS+' FROM (SELECT ROW_NUMBER()OVER(ORDER BY TEMP_COLUMN) TEMP_ROW_NUM, * FROM (SELECT TOP '
SET @V_SQL=@V_SQL+ CAST(@V_TOP AS VARCHAR(4))+' TEMP_COLUMN=0,'+@P_COLUMNS+' FROM '+@V_TABLE+ ' WHERE 1=1 '
--print @V_SQL
IF @P_CONDITION IS NOT NULL AND @P_CONDITION<>''
BEGIN
SET @V_SQL=@V_SQL+' AND '+@P_CONDITION
END
print @V_SQL
IF @P_SORT IS NOT NULL AND @P_SORT<>''
BEGIN
SET @V_SQL=@V_SQL+' ORDER BY '+@P_SORT+' '+@P_DIR
END
print @V_SQL
print @P_SORT
print @P_SORT+' '+@P_DIR
SET @V_SQL=@V_SQL+')T1)T2 WHERE TEMP_ROW_NUM >'+CAST(@V_START AS VARCHAR(10))
exec sp_executesql @V_SQL
print @V_SQL
END
原本里面@V_SQL NVARCHAR(MAX) 是NVARCHAR类型的,是可以执行,但是因为NVARCHAR只支持到4000,而实际可能超过这个,所以只能改为VARCHAR,支持到8000,可是我改完后,就报标题这个错误。查了资料,有说后面+''''的可以解决,我试过还是不行。麻烦哪位帮忙改下这个过程。
(@P_TABLENAME VARCHAR(MAX),
@P_COLUMNS NVARCHAR(2000),
@P_CONDITION NVARCHAR(1000)=NULL,
@P_SORT NVARCHAR(100)=NULL,
@P_DIR VARCHAR(4),
@P_PAGEINDEX INT,
@P_PAGESIZE INT,
@P_STOREPROCEDURE NVARCHAR(1000),
@P_SPPARAMETER NVARCHAR(1000),
@P_TOTALRECORDS INT=0 OUTPUT)
AS
SET NOCOUNT ON
DECLARE @V_SQL NVARCHAR(MAX)
DECLARE @V_START INT
DECLARE @V_TOP INT
DECLARE @V_TABLE NVARCHAR(MAX)
DECLARE @MY_TABLE NVARCHAR(MAX)
DECLARE @OUT_TABLE NVARCHAR(MAX)
BEGIN
--查询总记录数
SET @V_TABLE=@P_TABLENAME
SET @P_TOTALRECORDS=0
IF @P_STOREPROCEDURE IS NOT NULL AND @P_STOREPROCEDURE<>''
BEGIN
print @P_STOREPROCEDURE+' '+@P_SPPARAMETER+',@OUT_TABLE OUT'
IF @P_SPPARAMETER IS NOT NULL AND @P_SPPARAMETER<>''
BEGIN
SET @V_SQL=@P_STOREPROCEDURE+' '+@P_SPPARAMETER+',@OUT_TABLE OUT'
exec sp_executesql @V_SQL,N'@OUT_TABLE NVARCHAR(MAX) OUT',@OUT_TABLE OUT
END
ELSE
BEGIN
SET @V_SQL=@P_STOREPROCEDURE+' @OUT_TABLE OUT'
exec sp_executesql @V_SQL,N'@OUT_TABLE NVARCHAR(MAX) OUT',@OUT_TABLE OUT
END
if(@P_PAGESIZE=0)
begin
SET @V_SQL=@OUT_TABLE
IF @P_CONDITION IS NOT NULL AND @P_CONDITION<>''
BEGIN
SET @V_SQL='SELECT * FROM ('+@V_SQL+') TEMP_VIEW WHERE '+@P_CONDITION
END
IF @P_SORT IS NOT NULL AND @P_SORT<>''
BEGIN
SET @V_SQL=@V_SQL+' ORDER BY '+@P_SORT+' '+@P_DIR
END
exec sp_executesql @V_SQL
RETURN
end
SET @V_TABLE='('+@OUT_TABLE+') TEMP_VIEW'
print @V_TABLE
END
SET @V_SQL='SELECT @P_TOTALRECORDS=COUNT(1) FROM '+@V_TABLE+' WHERE 1=1 '
IF @P_CONDITION IS NOT NULL AND @P_CONDITION <>''
BEGIN
SET @V_SQL=@V_SQL+' AND '+@P_CONDITION
END
exec sp_executesql @V_SQL,N'@P_TOTALRECORDS INT OUT',@P_TOTALRECORDS OUT
SET @V_START=(@P_PAGEINDEX-1)*@P_PAGESIZE
SET @V_TOP=@P_PAGEINDEX*@P_PAGESIZE
SET @V_SQL='SELECT '+@P_COLUMNS+' FROM (SELECT ROW_NUMBER()OVER(ORDER BY TEMP_COLUMN) TEMP_ROW_NUM, * FROM (SELECT TOP '
SET @V_SQL=@V_SQL+ CAST(@V_TOP AS VARCHAR(4))+' TEMP_COLUMN=0,'+@P_COLUMNS+' FROM '+@V_TABLE+ ' WHERE 1=1 '
--print @V_SQL
IF @P_CONDITION IS NOT NULL AND @P_CONDITION<>''
BEGIN
SET @V_SQL=@V_SQL+' AND '+@P_CONDITION
END
print @V_SQL
IF @P_SORT IS NOT NULL AND @P_SORT<>''
BEGIN
SET @V_SQL=@V_SQL+' ORDER BY '+@P_SORT+' '+@P_DIR
END
print @V_SQL
print @P_SORT
print @P_SORT+' '+@P_DIR
SET @V_SQL=@V_SQL+')T1)T2 WHERE TEMP_ROW_NUM >'+CAST(@V_START AS VARCHAR(10))
exec sp_executesql @V_SQL
print @V_SQL
END
原本里面@V_SQL NVARCHAR(MAX) 是NVARCHAR类型的,是可以执行,但是因为NVARCHAR只支持到4000,而实际可能超过这个,所以只能改为VARCHAR,支持到8000,可是我改完后,就报标题这个错误。查了资料,有说后面+''''的可以解决,我试过还是不行。麻烦哪位帮忙改下这个过程。
EXEC命令可以使用默认字符