How to pass Datetime value to a tsql stored Procedure

_{Wednesday, January 13, 2010 2:07 PM}

I have written something like this but it is giving error when passing date to stored procedure.
somebody suggest me.

Alter Procedure [dbo].[Test_Search]
(
@Date DateTime
)
AS
Declare
@Query nvarchar(4000)
Begin
Set @Query=''
Set @Query='Select * from search where CreationDate<='''+(@Date)+''''

end
exec @Query

exec [Test_Search] '2009-11-03'

_{Wednesday, January 13, 2010 2:20 PM ✅Answered}

I made some corrections. Let us know if helpful.

ALTER PROCEDURE [dbo].[Test_Search] 
               @Date DATETIME 
AS 
  DECLARE  @Query NVARCHAR(4000) 
   
  SET @Query = 'Select * from search where CreationDate<=''' + 
                convert(VARCHAR,@Date) + '''' 
   
  PRINT @Query 
   
  EXEC SP_EXECUTESQL     @Query 

GO 

EXEC Test_Search   '2009-11-03' 

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

_{Wednesday, January 13, 2010 2:19 PM}

Try the following, even though I don't see a need for dynamic sql.

Alter Procedure [dbo].[Test_Search]
(
@Date DateTime
)
AS
Declare 
@Query nvarchar(4000)

Set @Query=''
Set @Query='Select * from search where CreationDate<='''+ CAST(@Date AS VARCHAR(50)) +''''

print @Query
exec (@Query)

exec dbo.[Test_Search] '2009-11-03'

Abdallah El-Chal, PMP, ITIL, MCTS

_{Wednesday, January 13, 2010 2:20 PM}

something like below

Alter Procedure [dbo].[Test_Search]
(
@Date DateTime
)
AS
Declare @Query nvarchar(4000)
Begin
Set @Query=''
Set @Query='Select * from search where CreationDate<='''+  convert(nvarchar(20), @Date) +''''

end
exec @Query

exec [Test_Search] '2009-11-03'

_{Wednesday, January 13, 2010 2:21 PM}

1 - There is not need for dynamic query and concatenate the value of @Date in order to execute this query.

select *
from search
where CreationDate <= @Date;

2 - If you insist in using a dynamic query, then parameterize the query and use sp_executesql instead.

declare @Date datetime;
declare @query nvarchar(4000);

set @query = N'select * from search where CreationDate <= @Date';
set @Date = '20091103';

exec sp_executesql @query, N'@Date datetime', @Date;

Parameterizing the query this way, will help you with:

• There is not need for conversion ('... where CreationDate <=''' + convert(varchar(35), @Date, 126) + '''').
• You do not have to deal with embeded apostrophies.
  - Better protection against sql injection.

3 - Notice the format I am using with the date literal. It is better to use a format that is not language aware, like ISO 8601 or ANSI. Check function CONVERT in BOL, for more info.

AMB

_{Wednesday, January 13, 2010 2:21 PM}

Hi use this way. dont use dynamic query . its affect sql injection. plz avoid that way

ALTER

Procedure [dbo].[Test_Search]

(

@Date

DateTime

)

AS

Begin

Select

* from search where CreationDate <=CONVERT(VARCHAR,@Date,101)

end

exec

[Test_Search] '2010-11-03'

-->Manigandan-DBA , Mark as Answer if it helps!

_{Wednesday, January 13, 2010 2:23 PM}

Take a look at sp_ExecuteSQL in BOL. See also http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-pPremature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Wednesday, January 13, 2010 3:45 PM}

You can also use sp_executesql that allows use parameters 
http://www.sommarskog.se/dynamic_sql.html