Pass an argument to Store Proc.

Question

0.00/5 (No votes)

See more:

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?

SQL

(
@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;

Posted 18-Jun-12 7:33am

khangaldi

Updated 18-Jun-12 8:13am

R. Giskard Reventlov

v2

Add a Solution

Comments

ZurdoDev 18-Jun-12 14:21pm

Do a SQL trace and see what is being passes exactly.

Sandeep Mewara 18-Jun-12 14:43pm

Issue is the value and the way you have passed the datetime fields.

SP is just fine and I doubt if this is relevant here.

How are you passing the values to this SP?

db7uk 18-Jun-12 16:45pm

When you say you have tried the convert. What happened? I normally do this if working with dynamic sql:
select @countout=count(*) from Trans where Trans.Date between convert(datetime, '+ @FromDate +', 103) and convert(datetime, '+ @ToDate + ', 103)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prosan · Answer 1 · 2012-06-18T20:42:00

Solution 2

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

SQL

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

Posted 18-Jun-12 20:42pm

Prosan

Comments

khangaldi 19-Jun-12 3:23am

i don't know why but this code didn't work , although it seem correct

ajay.anilmaddi · Answer 2 · 2012-06-18T20:34:00

Solution 1

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

Posted 18-Jun-12 20:34pm

ajay.anilmaddi

khangaldi · Answer 3 · 2012-06-18T21:24:00

Tanx Guys but this is how i solved it:

SQL

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