none
Do an insert from a stored proc using a loop and variable params

    Question

  • I have to run this changing the 2nd parameter each time this is run

    the values i have to change it to are  (2,5,6,7,10,11,12,13,14,15,18,19)

    INSERT INTO [VFMEx]([No],[Name],[Redem],[Notices],[pen],[Balance],[countr] )

    Execute MySproc  '10/31/2010' ,2 ,1 ,1

    update  [VFMEx] set [FN] = 2 where fn is null

    I created a script copying and pasting it over and over and just manually changing the value.  but thats really sloppy, also when i run the script it works fine, but when i copy and paste into a stored proc it only does the 1st insert and update.  it runs long enough to look like its working but it doesnt

    oops last run it worked but prior 3 it didnt ?

     

     

    Thursday, December 02, 2010 8:22 PM

Answers

  • You may try using dynamic SQL, e.g.

    declare @SQL nvarchar(max)
    
    ;with Params as (select * from
    
    (
    values
    
    (2) ,
    (5),
    (6)
    (7),
    (10),
    (11)
    (12)
    ) as t (Param))
    
    select @SQL = coalesce(@SQL,'') + '
    INSERT INTO [VFMEx]([No],[Name],[Redem],[Notices],[pen],[Balance],[countr] )
    Execute MySproc ''10/31/2010'' ,' + convert(varchar(10),Param) +' ,1 ,1
    update [VFMEx] set [FN] = ' + convert(varchar(10),param) + ' where fn is null' from Params
    
    print @SQL -- to see if we created correct statement
    execute(@SQL)
    
    The cte is based on this article http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx (you can construct your table with params more traditional way with SELECT UNION ALL SELECT instead)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Willy Taveras Thursday, December 02, 2010 8:56 PM
    • Marked as answer by WIJ Thursday, December 02, 2010 9:27 PM
    • Unmarked as answer by WIJ Thursday, December 02, 2010 9:35 PM
    • Marked as answer by KJian_ Wednesday, December 08, 2010 3:41 AM
    Thursday, December 02, 2010 8:33 PM

All replies

  • I suggest creating a 2nd version of the procedure that is capable of accepting all of these numbers at one time and do this all in one pass rather looping through this and executing the procedure one record at a time.  Are you using SQL Server 2008?

    Thursday, December 02, 2010 8:28 PM
  • You may try using dynamic SQL, e.g.

    declare @SQL nvarchar(max)
    
    ;with Params as (select * from
    
    (
    values
    
    (2) ,
    (5),
    (6)
    (7),
    (10),
    (11)
    (12)
    ) as t (Param))
    
    select @SQL = coalesce(@SQL,'') + '
    INSERT INTO [VFMEx]([No],[Name],[Redem],[Notices],[pen],[Balance],[countr] )
    Execute MySproc ''10/31/2010'' ,' + convert(varchar(10),Param) +' ,1 ,1
    update [VFMEx] set [FN] = ' + convert(varchar(10),param) + ' where fn is null' from Params
    
    print @SQL -- to see if we created correct statement
    execute(@SQL)
    
    The cte is based on this article http://beyondrelational.com/blogs/madhivanan/archive/2010/08/02/values-clause-in-sql-server-2008.aspx (you can construct your table with params more traditional way with SELECT UNION ALL SELECT instead)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Willy Taveras Thursday, December 02, 2010 8:56 PM
    • Marked as answer by WIJ Thursday, December 02, 2010 9:27 PM
    • Unmarked as answer by WIJ Thursday, December 02, 2010 9:35 PM
    • Marked as answer by KJian_ Wednesday, December 08, 2010 3:41 AM
    Thursday, December 02, 2010 8:33 PM
  • Is this for sql server 2008?

    I tried :

    ;with Params as

    (

     select * from

    (

     values

     (2) ,

     (5),

     (6),

     (7),

     (10),

     (11),

     (12),

     (13),(14),(15),(18),(19)

    )

     as t (Param))

    and got the error:

    Msg 156, Level 15, State 1, Line 4

    Incorrect syntax near the keyword 'values'.

     then i went to the blog and copied code:

    select

     * from

    (

    values  

    (-1) ,  

    (1),  

    (32),  

    (523)

    )

    as t (no)

    And got the error:

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'values'.

     

    • Edited by WIJ Thursday, December 02, 2010 9:41 PM scrunchy
    Thursday, December 02, 2010 9:37 PM
  • Yes, as I indicated in my response, this syntax is for SQL 2008 and it works in my SQL 2008 R2. If you use a SQL 2005, then use

    select 1

    union all

    select 2

    etc. instead of this new slick way.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, December 02, 2010 10:05 PM
  • I guess its time for glasses  \o-o/
    Friday, December 03, 2010 5:05 PM