Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Setting variables in dynamic sql statements

    Hi all,
    This is my first posting at dBforums.
    I have dynamic sql statement below:

    SET @sql = 'SELECT @pai = ' + @campo_pai +
    ' FROM ' + @tabela +
    ' WHERE ' + @campo_filho + ' = ' + @current

    As you can see, i want to assign to local stored procedure variable @pai result of my query. How can i do that?
    It is not working using code above.
    My database is MS SQL Server 2000.

    Thanks in advance,
    Daniel.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    you can declare all your variables outside the statement, then wrap in the exec('') the actual code. As you can see in the where clause I have passed a variable field. You should be able to do the same. Just make sure you place all the single quotes, around the Y is actuall two single quotes and not double quote.

    declate @m as varchar(1)
    Set @m = 'something'

    exec('
    select c.f1, t.f2
    from table1 t, table2 c
    where t.[' + @month + '] = ''Y'' and
    t.id = c.id ')

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Hi MKKMG,
    It is very easy to use variables at WHERE clause. My problem is that i can't assign a value to a variable in a dynamic sql statement. If my statement was not dynamic i could do this:

    SET @my_var = (SELECT id FROM my_table WHERE active = 'S')

    or

    SELECT @my_var = id FROM my_table WHERE active = 'S'

    I want that feature, but instead of a "static" sql statement i have to use a dynamic sql statement.

    Hope i made myself clear.

    Regards,
    Daniel.


    Originally posted by mkkmg
    you can declare all your variables outside the statement, then wrap in the exec('') the actual code. As you can see in the where clause I have passed a variable field. You should be able to do the same. Just make sure you place all the single quotes, around the Y is actuall two single quotes and not double quote.

    declate @m as varchar(1)
    Set @m = 'something'

    exec('
    select c.f1, t.f2
    from table1 t, table2 c
    where t.[' + @month + '] = ''Y'' and
    t.id = c.id ')

  4. #4
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    it upsets me that you could not understand how to pass the variables dynamically, if you are so good at placing them in the where clause then you should now how to place them anywhere. Simple as that.

    declare @pai as varchar(30)
    declare @tabela as varchar(30)

    set @pai = 'your field that you are passing'
    set @tabela = 'your table'

    exec('SELECT [' + @pai+ '] FROM [' + @tabela + ']')


    did not bother to do the where since you already know how.

    but you should be able to piece together the rest

Posting Permissions

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