Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: More selecting into local variable with dynamic select...

    Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:

    Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
    Code:
    DECLARE @SQLCmd varchar(256)
    DECLARE @TargetDate smalldatetime
    DECLARE @TLevel int
    DECLARE @n int
    SET @TargetDate = '2004-05-24'
    SET @TLevel = 1
    
    
    SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' + 
    CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
    CONVERT(varchar(3), @TLevel) + '))'
    exec (@SQLCmd)
    if @n > 0
    print 'yep'
    else print 'nope'
    and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output:
    Code:
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@n'.
    nope
    Thoughts?
    Last edited by TallCowboy0614; 05-25-04 at 16:35. Reason: no spel chekur
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need a table...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int)
    
    
    DECLARE @SQLCmd varchar(256)
    DECLARE @TargetDate smalldatetime
    DECLARE @TLevel int
    DECLARE @n int
    SET @TargetDate = '2004-05-24'
    SET @TLevel = 1
    
    
    SET @SQLCmd = 'SELECT COUNT(*) FROM Orders'
    
    INSERT INTO myTable99(Col1) exec (@SQLCmd)
    
    SELECT @n = Col1 FROM myTable99 
    
    if @n > 0
    print 'yep'
    else print 'nope'
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks once again, Brett, I tried the sp_executesql route also...and got it all to compile just peachy...but still can't get at the count returned...alas...so a table I will go a-creatin'...
    Code:
    DECLARE @SQLCmd nvarchar(256)
    DECLARE @Message varchar(256)
    DECLARE @TargetDate smalldatetime
    DECLARE @TLevel int
    DECLARE @n int
    SET @n = 0
    SET @TargetDate = '2004-05-24'
    SET @TLevel = 1
    
    
    SET @SQLCmd = 'SELECT count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' + CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' + CONVERT(varchar(3), @TLevel) + '))'
    EXECUTE @n = sp_executesql @SQLCmd /*<- does not work with SELECT @n = ... */
    PRINT @n
    if (@n > 0)
    print 'yep'
    else print 'nope'
    results
    Code:
                
    ----------- 
    27
    
    (1 row(s) affected)
    
    0
    nope
    Thanks!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm starting to get MIGHTY thirst answering these questions...

    And I probably use sp_EXECSQL more (hell at all)

    It just seems so damn inflexible...some one once said that it's more effecient...but I poured over bol and it's sketchy at best...

    When I need to dynamic...I'll do it that way...especially since sp_executesql can only assign 1 row....

    But the best is to avoid dynamic sql....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2004
    Posts
    6

    If you are still working on this -

    Hi,
    Could you please put - PRINT CAST(@SQLCmd as Varchar(250)) in, instead of the EXEC, run it and post the result.
    Normally, it will tell you you haven't declared a variable (when its obvious you have) when your quotes or brackets are wrong in the sequel string.
    Best regards
    Steve

  6. #6
    Join Date
    Oct 2004
    Posts
    6

    Didn't read it right

    Sorry mate, didn't spend enough time reading the original post.
    You had just neglected to actually use the variable @n after you had declared it.
    Your string is just reading 'SELECT @n =' instead of 'SELECT ' (Value of @n) ' ='.
    Don't know what you are doing with it but this should remove your error:

    DECLARE @SQLCmd varchar(256)
    DECLARE @TargetDate smalldatetime
    DECLARE @TLevel int
    DECLARE @n int
    SET @TargetDate = '2004-05-24'
    SET @TLevel = 1


    SET @SQLCmd = 'SELECT ' + CAST(@n AS VARCHAR(250)) + ' = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' +
    CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
    CONVERT(varchar(3), @TLevel) + '))'
    exec (@SQLCmd)
    if @n > 0
    print 'yep'
    else print 'nope'


    Have fun.
    Best regards
    Steve

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    Thoughts?
    Yep.

    -PatP

  8. #8
    Join Date
    Oct 2004
    Posts
    6

    ?

    As In - Yep-E-Aye-Yay?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Dynamic SQL
    As In - Yep-E-Aye-Yay?
    Sorry, Yep as I posted a solution to this question in the thread that is linked to the word "yep" in my previous posting.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •