locked
comparing two dates using dynamic sql RRS feed

  • Question

  • DECLARE @CUTOFFDATE DATETIME

    DECLARE @SCRIPT NVARCHAR(MAX) = 'select a,b,c,(CASE when D > ' + @CUTOFFDATE + ' THEN ''X'' ELSE ''Y'' END) AS SType from propertytotal ' 

    EXEC (@SCRIPT);

    i am trying to execute the same sql statement using EXEC command . But i dont get desired result.

    how to execute the same sql query using sp_executesql?

    If possible give some example how to pass multiple inputs to sp_executesql  command.


    Monday, January 2, 2012 11:07 AM

Answers

  • Hi,

    My apology, here is correct one

    DECLARE @CUTOFFDATE DATETIME

    select a,b,c,(CASE when D > @CUTOFFDATE THEN 'X' ELSE 'Y' END) AS SType from propertytotal

    Thanks Jeff for pointing this.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, January 2, 2012 12:01 PM

All replies

  • Hi,

    Here also no need of using dynamic sql, try with below static query:

    DECLARE @CUTOFFDATE DATETIME
    
    select a,b,c,(CASE when D > @CUTOFFDATE  THEN 'X' ELSE 'Y' END AS SType from propertytotal
    


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, January 2, 2012 11:35 AM
  • Sorry untested

     

    declare @sql nvarchar(1000),@CUTOFFDATE  datetime

    select  @CUTOFFDATE   ='20010101'

    set @sql = 'select a,b,c,(CASE when D > @CUTOFFDATE THEN ''X'' ELSE ''Y'' END) AS SType from propertytotal ' 

    exec sp_executesql @sql, N'@CUTOFFDATE  DATETIME',@CUTOFFDATE   

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 2, 2012 11:37 AM
    Answerer
  • Hi,

    Here also no need of using dynamic sql, try with below static query:

     

    DECLARE @CUTOFFDATE DATETIME
    
    select a,b,c,(CASE when D > @CUTOFFDATE  THEN 'X' ELSE 'Y' END AS SType from propertytotal
    

     


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!


    You're missing a ) after END
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, January 2, 2012 11:43 AM
  • Hi,

    My apology, here is correct one

    DECLARE @CUTOFFDATE DATETIME

    select a,b,c,(CASE when D > @CUTOFFDATE THEN 'X' ELSE 'Y' END) AS SType from propertytotal

    Thanks Jeff for pointing this.


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Monday, January 2, 2012 12:01 PM