22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[GetLiquidDataPagedAndSorted] (
@startRowIndex int,
@maximumRows int,
@wellname nvarchar(20),
@thedate nvarchar(10),
@sortExpression nvarchar(100)) AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
SET @sortExpression = 'GetTime Desc'
-- Issue query
DECLARE @sql nvarchar(2000)
SET @sql = 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNum
FROM LiquidData ) AS LiquidDataWithRowNumbers
WHERE
RowNum > ' + CONVERT(nvarchar(10), @startRowIndex) + ' and RowNum <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ')
and wellname='+@wellname+' and
gettime>= '+@thedate+' and gettime<'+dateadd(day,1,@thedate)
-- Execute the SQL query
exec sp_executesql @sql
insert into #temptable
exec 存储过程名 @param1,@param2,……
--使用动态SQL请考虑SQL注入问题
ALTER PROCEDURE [dbo].[GetLiquidDataPagedAndSorted] (
@startRowIndex int,
@maximumRows int,
@wellname nvarchar(20),
@thedate nvarchar(10),
@sortExpression nvarchar(100) ='') AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
SET @sortExpression = 'GetTime Desc'
-- Issue query
DECLARE @sql nvarchar(2000)
SET @sql = 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNum
FROM LiquidData ) AS LiquidDataWithRowNumbers
WHERE
RowNum > ' + CONVERT(nvarchar(10), @startRowIndex) + ' and RowNum <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ')
and wellname='+@wellname+'
and gettime>= '+@thedate +' and gettime<'+CONVERT(VARCHAR(10),DATEADD(day,1,@thedate),111)
-- Execute the SQL query
--PRINT @sql
EXEC sp_executesql @sql
/*
EXEC GetLiquidDataPagedAndSorted 1,10,'X','2014-8-1',''
*/
ALTER PROCEDURE [dbo].[GetLiquidDataPagedAndSorted] (
@startRowIndex int,
@maximumRows int,
@wellname nvarchar(20),
@thedate nvarchar(10),
@sortExpression nvarchar(100)) AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
SET @sortExpression = 'GetTime Desc'
-- Issue query
DECLARE @sql nvarchar(2000)
SET @sql = 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNum
FROM LiquidData ) AS LiquidDataWithRowNumbers
WHERE
RowNum > ' + CONVERT(nvarchar(10), @startRowIndex) + ' and RowNum <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ')
and wellname='''+@wellname+'''
and gettime>= '+@thedate+' and gettime<'''+ CONVERT(VARCHAR(32),dateadd(day,1,@thedate)) + ''''
-- Execute the SQL query
exec sp_executesql @sql
dateadd(day,1,@thedate) 这里返回的日期,和字符串相加会报错。
另外 wellname='''+@wellname+'''少了左右引号
这种建议还是用变量方式来传入吧