不能直接这样用,除非你用拼写SQL的方式DECLARE @SQL NVARCHAR(4000) SET @SQL='SELECT * from table order by ''+@XXX+'''
CREATE PROCEDURE aaaaaaaaaaaaaaaa @SQL varchar(4000), @Order varchar(1000) AS BEGINset @SQL = @SQL + ' order by '+@Orderexecute(@SQL)END
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON goALTER procedure [dbo].[GetProductsPageByPrice] (@SecondId Int, @pagesize int, @pageindex int) asbegin with temptbl as ( SELECT ROW_NUMBER() OVER (ORDER BY shoujia)AS Row, * from Products O where secondid=@secondid) SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize end 那帮我把 shoujia 就是order by 这个改成动态的吧
DECLARE @SecondId NVARCHAR(20) DECLARE @pagesize NVARCHAR(20) DECLARE @pageindex NVARCHAR(20) DECLARE @orderBy NVARCHAR(4000)SET @SecondId='1' SET @pagesize='1' SET @pageindex='1' SET @orderBy='A'DECLARE @SQL NVARCHAR(4000)SET @SQL=' with temptbl as ( SELECT ROW_NUMBER() OVER (ORDER BY '+@orderBy+')AS Row, * from Products O where secondid='+@SecondId+') SELECT * FROM temptbl where Row between ('+@pageindex+'-1)*'+@pagesize+'+1 and ('+@pageindex+'-1)*'+@pagesize+'+'+@pagesize+' 'PRINT @SQLexec(@SQL)
拼接SQL可也
SET @SQL='SELECT * from table order by ''+@XXX+'''
@SQL varchar(4000),
@Order varchar(1000)
AS
BEGINset @SQL = @SQL + ' order by '+@Orderexecute(@SQL)END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[GetProductsPageByPrice]
(@SecondId Int,
@pagesize int,
@pageindex int)
asbegin
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY shoujia)AS Row, * from Products O where secondid=@secondid)
SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize
end
那帮我把 shoujia 就是order by 这个改成动态的吧
DECLARE @pagesize NVARCHAR(20)
DECLARE @pageindex NVARCHAR(20)
DECLARE @orderBy NVARCHAR(4000)SET @SecondId='1'
SET @pagesize='1'
SET @pageindex='1'
SET @orderBy='A'DECLARE @SQL NVARCHAR(4000)SET @SQL=' with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY '+@orderBy+')AS Row, * from Products O where secondid='+@SecondId+')
SELECT * FROM temptbl where Row between ('+@pageindex+'-1)*'+@pagesize+'+1 and ('+@pageindex+'-1)*'+@pagesize+'+'+@pagesize+'
'PRINT @SQLexec(@SQL)
exec(@sql)
直接exec字符串会导致注入危险(因为参数会变成command执行),并且每次都要编译,执行效率低,应该使用参数化的sql,也就是系统存储过程sp_executesql执行,
这里写个小例子:
DECLARE
@ID_ex BIGINT --声明输入参数1
,@Str_ex NVARCHAR(50) --声明输入参数2
,@SqlStr NVARCHAR(4000) --声明字符串:用以提交参数化的Sql语句
,@Param_Def NVARCHAR(4000); --声明字符串:用以定义动态Sql的参数列表
SET @ID_ex = 0; --初始化,模拟输入
SET @Str_ex = N'aaa'; --初始化,模拟输入
SET @SqlStr = N'SELECT * FROM App WHERE AppId >= @ID OR @SearchStr=''aaa'' ';
SET @Param_Def = N'@ID BIGINT,@SearchStr NVARCHAR(50)';EXEC sp_executesql
@SqlStr --提交参数化的Sql语句
,@Param_Def --参数定义
,@ID=@ID_ex --参数替换1
,@SearchStr=@Str_ex; --参数替换2更多细节请参考MSDN:
http://msdn.microsoft.com/zh-cn/library/ms188001.aspx
字段名称不就是sql语句的一部分了, 这么弄,也不过是一部分在程序里拼,一部分放到存储过程里拼。