Results 1 to 12 of 12

Thread: Dynamic SQL

  1. #1
    Join Date
    Oct 2003
    Posts
    60

    Unanswered: Dynamic SQL

    I am dynamically building SQL strings within my code and using EXEC () to excute. My quetions is as follows: Can I assign the ouput of

    Exec (@sqlstring) to a variable

    i.e

    @myvar=exec (@sqlstring)


    note: @sqlstring return a single number, count of records.
    jaraba

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look up sp_executesql in BOL (I think the synatx is right).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs up

    Hi jaraba,

    See if this will work out for u....
    Code:
    DECLARE @myVar INT
    DECLARE @str varchar(50)
    SET @str = 'select ''@myVar'' = (5 * 100) '
    EXEC (@str)
    PRINT @myVar
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think that's what the poster asked for.

    Try this:
    declare @i int

    exec
    @i = sp_executesql N'select getdate()'

    select hResult=@i

    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think this is basically the same question as this thread addressed.

    -PatP

  6. #6
    Join Date
    Dec 2004
    Posts
    46

    Cool

    Quote Originally Posted by KnightHasan
    Hi jaraba,

    See if this will work out for u....
    Code:
    DECLARE @myVar INT
    DECLARE @str varchar(50)
    SET @str = 'select ''@myVar'' = (5 * 100) '
    EXEC (@str)
    PRINT @myVar

    I think knight is right.

  7. #7
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Exclamation Poster puhlease..

    Yeah ronin, that I am.
    If we talk about the original post, then to meet the specific requirement my code cud be modified as:

    Code:
    DECLARE @myVar INT
    DECLARE @str varchar(50)
    SET @str = 'select ''@myVar'' = count(*) from MYTABLE'
    EXEC (@str)
    PRINT @myVar
    That way, @myVar wud hold the count of records from MYTABLE.

    According to the code that rdjabarov posted, the result of :

    select hResult=@i

    is a 0 and not the current date. Correct me rdjabarov if I am wrong(which I am not )

    Now where is the guy who originally started this thread? Can we have any comments from ur side jaraba?
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be willing to bet that you'd need to use something like:
    Code:
    DECLARE @result	AS INT
    
    EXECUTE sp_executesql N'SELECT @i = Count(*) FROM master.dbo.sysprocesses'
    ,  N'@i INT OUTPUT ', @result OUTPUT
    
    SELECT @result, 1 + @result  --  Just to show it worked
    When I use a revision of your code:
    Code:
    DECLARE @myVar INT
    DECLARE @str varchar(99)
    SET @str = 'select ''@myVar'' = count(*) from master.dbo.sysprocesses'
    SELECT @str
    EXEC (@str)
    SELECT @myVar, 1 + @myVar
    I get an error.

    Did I miss something?

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by KnightHasan
    According to the code that rdjabarov posted, the result of :

    select hResult=@i

    is a 0 and not the current date. Correct me rdjabarov if I am wrong(which I am not )
    You can't be possibly right, because your code DOES NOT perform variable assignment, but rather returns a resultset, which is not what the poster asked for. Pat's example is the right answer. BTW, to comment on my example, 0 means success, any other value - failure.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs up

    BTW, to comment on my example, 0 means success, any other value - failure.
    Well... the poster didn't ask for a zero.

    And I agree that Pat's right. Thx for the guidance.
    (Why do the posters usually throw in the towel? I mean why don't they say whether they got their answer or not?)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~
    Knight says:

    You can't change the past, but you can ruin a perfectly good present by worrying about the future..

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by KnightHasan
    (Why do the posters usually throw in the towel? I mean why don't they say whether they got their answer or not?)
    Sigh... Its the nature of the universe I guess. They touch off a firestorm, just to watch the consternation, then wander off chuckling at the "experts" involved in the brohouha.

    Every once in a while, one says "Thanks", then you know that you've actually helped someone. Those are what keep me coming back (plus the ability to add to the conflagration when I can).

    -PatP

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't you just love your self?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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