将参数传递给Store Proc. [英] Pass an argument to 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;

推荐答案

为此使用convert命令并像这样写它将起作用

use convert command for this and write like this it will work

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))

如何将字符串转换为时间?




请转换为数据时间并传递参数
请参见下面的示例:

Dim strTime As String ="3:00 PM"

''转换为datetime
Dim dtTime as DateTime = Convert.ToDateTime(strTime)

''以24小时格式显示

Response.Write(dtTime.ToString("HH:mm"))

how to convert string to time ?

Hi ,


please Convert to data Time and pass the parameters
See example below:

Dim strTime As String = "3:00 PM"

'' Convert to datetime
Dim dtTime As DateTime = Convert.ToDateTime(strTime)

'' Display in 24hr format

Response.Write(dtTime.ToString("HH:mm"))

Tanx Guys，但这是我解决的方法:

Tanx Guys but this is how i solved it:

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.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！