sql server2000: passing date as parameter to dynamic sql 1

[duchovnick]

Hi,
I try to make a procedure within which there is dynamic sql and pass a date as parametert
as follows:

/*exec myDynamic 2007*/
ALTER PROCEDURE dbo.myDynamic @dyear DATETIME
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL=
'
SELECT
lastname 


FROM
contact1

WHERE
DATEPART(year, ext3)='+@dyear+'
'
EXEC (@dynSQL)
COMMIT
GO

i get that error message:

Server: Msg 241, Level 16, State 1, Procedure myDynamic, Line 7
Syntax error converting datetime from character string.

Can any one tell me what am i doing that is wrong in my code ?
Thanks !

 

[r937]

you are comparing a year to a date

you defined @dyear as DATETIME

perhaps you want

... WHERE ext3 = @dyear

alternatively, perhaps you want

... WHERE DATEPART(year,ext3) = DATEPART(year,@dyear)

on the other hand, perhaps you meant to define @year as INTEGER

in that case, you can say

... WHERE DATEPART(year,ext3) = @dyear

r937.com | rudy.ca

 

[bborissov]

ALTER PROCEDURE dbo.myDynamic @dyear DATETIME
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL = 'SELECT lastname
                      FROM contact1
                      WHERE DATEPART(year, ext3)='+
                            CAST(YEAR(@dyear) as varchar(4))
EXEC (@dynSQL)
COMMIT

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

Hi Borislav
And thanks a lot.
The following code:

/*exec xsql 2007*/
CREATE PROCEDURE dbo.xsql @dyear DATETIME
AS
BEGIN TRANSACTION

'
SELECT
lastname

FROM
contact1

WHERE
DATEPART(year, ext3)=@dyear


COMMIT
GO

Showed about 1000 lines.
Your code:

/*exec dbo.myDynamic 2007*/
ALTER PROCEDURE dbo.myDynamic @dyear DATETIME
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL = 'SELECT lastname
                      FROM contact1
                      WHERE DATEPART(year, ext3)='+
                            CAST(YEAR(@dyear) as varchar(4))
EXEC (@dynSQL)
COMMIT

Although it didnt bring up any error message, it didnt bring any line either. The code you gave
me is correct synthactly but not logically.
I'd be very gratefull if you could explain the differnce in both results.
And thanks again !

 

[bborissov]

That code must give you all records where year in Ext3 is equal to year in @dYear. BUT you must pass a DateTime as parameter (Function expect that). I just saw that you execute that SP that way:
exec dbo.myDynamic 2007
BUT 2007 is DateTime (@dyear DATETIME)
SQL Server converts 2007 to DataTime and you have this result:
1905-07-01 and YEAR(@dyear) = 1905 that is why you didn't got any records. If you want to pass the year (and only year) as parameter make your parameter to be integer not DateTime and then change DSQL to be:

/*exec dbo.myDynamic 2007*/

ALTER PROCEDURE dbo.myDynamic @dyear integer
AS
BEGIN TRANSACTION

DECLARE @dynSQL VARCHAR(2000)
SET @dynSQL = 'SELECT lastname
                      FROM contact1
                      WHERE DATEPART(year, ext3)='+
                            CAST(@dyear as varchar(4))
EXEC (@dynSQL)
COMMIT


exec dbo.myDynamic 2007

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

Thank you so much borislav.