Post reply

how to get a variable?

  • October 24, 2011 at 7:10 am

    #250595

    i have a variable having a integer value...

    i need to use the variable in the execute sql task to write a insert statement....

    pls help me to do that...

    Thanks,
    Charmer

  • October 24, 2011 at 8:35 am

    #1398710

    Have you read the entry in Books On Line (SQL help file) for the item

    sp_executesql (Transact-SQL) It has multiple examples of T-SQL code to accomplish what I think you want to do.

    Or for an online reference

    http://msdn.microsoft.com/en-us/library/ms175170(v=SQL.100).aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • October 24, 2011 at 8:55 am

    #1398729

    what i need is single insert statement...

    insert into BigCounter(CounterID) select ?

    im just passing the parameter value by a variable through parameter mapping...

    or

    i should need T_SQL statement top get this done..?

    is there any other way...? because i'm going to do this in a client machine so i can't create stored procedure over there...

    Thanks,
    Charmer

  • October 25, 2011 at 6:14 am

    #1399056

    Hi,

    You can try like this, In Execute Sql task Editor -> SqlStatement give statement as below

    INSERT INTO [dbo].[BigCounter]

    (CounterID)

    VALUES (?)

    In parameter mapping Add a parameter choose the variable name(which u created earlier i.e int), Direction Input, Data Type as numeric and Parameter Name as 0 click Ok

    Now Execute the task and you will able to insert int value in variable to Bigcounter table.

    Hope this helps... ! I tried it works for me 🙂

  • November 16, 2011 at 11:23 am

    #1408773

    In addition, you can use the long data type in case you experience any issues there. Also you can give your variable inside the task a more friendly name

    INSERT INTO table (column)

    values @myValue

    and just make sure you use @myValue for the parameter name property in the parameter mapping dialogue (paramater is still 0).

    ----------------------------------------------------

  • November 17, 2011 at 1:32 am

    #1409013

    Thanks guys....

    Thanks,
    Charmer

  • November 17, 2011 at 12:36 pm

    #1409465

    Quick correction to my prior post. Use the '?' as the place holder when using direct input. But you can still give the parameter a friendly name in the Parameter Mapping dialogue.

    If you ever needed to use more variable to do more fancy work in the SQL query ( like using variable1, then variable2, then variable1 again ) it is better to call a stored procedure. Not sure how much freedom you have to do this, but here goes:

    Example::

    create procedure myproc(@v1 int, @v2 int)

    as

    begin

    set nocount on;

    insert into table(colm1, colm2)

    select @v1, getdate()

    union all

    select @v2, getdate(),

    union all

    select @v2*2,getdate()

    END

    enter

    exec myproc ?, ?

    in the SQLStatement field

    ----------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply