将参数传递给Store Proc.

问题描述

大家好；我有一个存储的proc，我需要将datetime变量(@FromDate和@ToDate)传递给它.
从字符串转换日期和/或时间时，转换失败.
我尝试使用转换功能，但没有用.
有人可以帮我吗?

Hi every one; I have a stored proc and I need to pass datetime variables (@FromDate and @ToDate) to it.
The conversion failed when converting date and/or time from character string.
I tried to use convert function but it didn''t work.
Can any one help me?

(
@page int = 1,
@pagesize int = 10,
@FromDate datetime,
@ToDate datetime
)
as
declare @sql nvarchar(max);
declare @sql2 nvarchar(max);
declare @countout int=0;

set @sql='';
set @sql2='';
declare	@lbound int, @ubound int

set @lbound = 1;
set @ubound = 100000;
set @sql='select @countout=count(*) from Trans where Trans.Date between '+ @FromDate +' and '+ @ToDate

Declare @sqlParam nvarchar(100)
set @sqlParam = ' @countOut int output ';

exec sp_executeSQL @sql,@sqlParam,@countOut output;
if @countOut=0
   return 0;

推荐答案

set @sql='select @countout=count(*) from Trans where Trans.Date between '+ convert(datetime, convert(char(10),@FromDate, 110)) +' and '+ convert(datetime, convert(char(10),@ToDate, 110))

declare @sql nvarchar(max);
declare @sql2 nvarchar(max);
declare @countout int=0;
declare @Tempsql nvarchar(max);

set @sql='';
set @Tempsql='';
DECLARE	@lbound int,
		@ubound int


SET @lbound = 1;
SET @ubound = 100000;


set @sql='select @countout=count(*) from Trans tn '

if @FromDate is not null
begin
	set @Tempsql= @Tempsql+' where  tn.Date >= ''' + convert(varchar, @FromDate,20)+'''';
end
 if @ToDate is not  null
begin
set @ToDate= DATEADD("day", 1, @ToDate)
set @Tempsql= @Tempsql+' and  tn.Date <= ''' + convert(varchar, @ToDate,20)+'''';
end

set @sql= @sql + @Tempsql;
Declare @sqlParam nvarchar(100)
SET @sqlParam = '@countOut int output';
EXEC sp_executeSQL @sql,@sqlParam,@countOut OUTPUT;
if (@countOut=0)
return 0;
EXEC dbo.Paging_Get_Bounds @countOut,@page , @pagesize, @lbound output , @ubound output ;
set @sql=' select '+CONVERT(varchar, @countOut)+' as countOut,*            from (
 select  (ROW_NUMBER() OVER (ORDER BY tn.Date DESC) ) as rownumber,*
 from Trans tn
)as tbl
where rownumber between  '+CONVERT(varchar, @lbound)+' and '+CONVERT(varchar, @ubound);

EXEC sp_executeSQL @sql

这篇关于将参数传递给Store Proc.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！