SQL Query Analyzer: Viewing Variable Values

Posted on November 18, 2005
Filed Under /dev/code/ | 637 views |

Say you’re in SQL Query Analyzer writing some SQL that will one day grow up big and strong into a stored procedure and it isn’t working. You’re getting a cryptic error message when you execute*:

DECLARE @timezoneOffset float
DECLARE @baseDate datetime
DECLARE @sqlStr varchar(40)

SET @timezoneOffset = -3
SET @baseDate = CONVERT( varchar(40), DATEADD( hh, @timezoneOffset, GETDATE() )

SET @sqlStr = ”SELECT * FROM entries WHERE ( ENTRY_DATE <= ‘ + @baseDate + ‘ ORDER BY ENTRY_DATE DESC”

EXEC( @sqlStr )

*not real production code

because it doesn’t work. But why not? (The astute among you will already have figured it out but me, sometimes I’m slow). To debug this remove the EXEC() and add in this SELECT:

– EXEC( @sqlStr )
SELECT @sqlStr AS STATEMENT

which outputs the contents of @sqlStr into the results pane. In this case the error was in my lack of proper quoting around the datetime value being inserted into @sqlStr. It should have been:

SET @sqlStr = ”SELECT * FROM entries WHERE ( ENTRY_DATE <= ”’ + @baseDate + ”’ ORDER BY ENTRY_DATE DESC”

Thanks Jason.

Comments

One Response to “SQL Query Analyzer: Viewing Variable Values”

  1. pintu on July 21st, 2008 11:47 pm

    CREATE View Test
    declare @CusAccNo Varchar(50)
    AS

    select app.Slno, app.CustAccNo, app.Cr, app.Dr, app.PDate, app.Bal,
    ISNULL((select app.Bal+sum(a.Cr-a.Dr) from Deposit_DataEntry as a
    where a.CustAccNo=app.CustAccNo and a.slno < app.slNo), app.Bal) as fbal
    from
    (select top 100 percent Deposit_DataEntry.Slno, Deposit_DataEntry.CustAccNo, Deposit_DataEntry.Cr, Deposit_DataEntry.Dr, Deposit_DataEntry.PDate, Deposit_DataEntry.Cr-Deposit_DataEntry.Dr as Bal
    from Deposit_DataEntry order by Deposit_DataEntry.SlNo) as app WHERE CustAccNo=@CusAccNo

    Is the Query is wright?
    Please check The Syntax

Leave a Reply