Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: Mixing Dynamic SQL with non-Dynamic in Stored Proc

    I have a Stored Procedure for processing a Bill of Material.

    One column on the Assembly Table is a Function Name that contains some busniess rules.

    OK, now I'm doing a Proof of Concept and I'm stumped.

    Huuuuh!

    I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.

    Note: The function just returns a bit.

    So; here's what I had in mind ...
    Code:
    if isnull(@FnNameYN,'') <> ''
    	exec  spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output

    Code:
    CREATE PROCEDURE dbo.spinb_CheckYN 
           @FnNameYN varchar(50), 
           @InvLineID int,  
           @FnBit bit output 
    AS
    
    declare @SQL varchar(8000)
    
    set @SQL = '
    if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
    	set @FnBit = 1
    else
    	set @FnBit =  0'
    
     exec (@SQL)
    GO

    Obviously; @FnBit is not defined in @SQL so that execution will not work.
    Code:
    Server: Msg 137, Level 15, State 1, Line 4
    Must declare the variable '@FnBit'.
    Server: Msg 137, Level 15, State 1, Line 5
    Must declare the variable '@FnBit'.

    So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?


    My many thanks to anyone who can solve this riddle for me.
    Thank You!


    Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
    Hopefully a better solution comes to light.
    Code:
    ------ Vertical Build1 - Std Vanes -----------
    if @FnNameYN = 'fnb_YN_B1_14'
    BEGIN
    	if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) )  = 1
    		set @FnBit = 1
    	else
    		set @FnBit =  0
    END
    
    ------ Vertical Build1 - Scissor Vanes -----------
    if @FnNameYN = 'fnb_YN_B1_15'
    BEGIN
    	if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) )  = 1
    		set @FnBit = 1
    	else
    		set @FnBit =  0
    END
    .
    .
    .
    etc.
    Last edited by vich; 03-24-07 at 03:16.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You should consider using sp_executesql instead of exec. By using sp_executesql you can pass parameteres to the dynamic sql.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you might want to have a look at this essay too:

    http://www.sommarskog.se/dynamic_sql.html

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Great posts. Thank you guys.

    sp_ExecuteSQL. Heard of it, never looked, totally forgot, good call! Beats using the hard coded list. I have to hard code each function to the business rules anyway and my hard coded list will be faster than a late binding solution, so the hard coded list isn't an entirely bad idea. Still, one more piece to break.

    Not exactly looking for job security here but getting these to work without these hard coded business-rules functions was an Nth degree more complex on the design.

    However; I wonder. Is sp_ExecuteSQL late binding?

    jezemine, thank you. I skimmed it but it'll take a bit to read thoroughly. Well worth it.

    For now; I'm getting it working with the hard coded list but if I get time (back's against the wall for deadline) I will try getting this to work.

    Thank you Thank you!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by vich
    However; I wonder. Is sp_ExecuteSQL late binding? Hmmm.
    Er..... how do you mean? Like a client side language?

    sp_ExecuteSQL does produce cached plans and so does not necessarily recompile every time it is run. Is that what you mean?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Er..... how do you mean? Like a client side language?

    sp_ExecuteSQL does produce cached plans and so does not necessarily recompile every time it is run. Is that what you mean?
    Yes.

    Late binding = Determining the plan at run time; doesn't it?

    This is in a Bill of Material assembly build process. Figure 5,000 order line items per day (nightly run). Each line item has about 30 assemblies/components. My target run time is to make this run no more than 1 hour. It has to make the assembly (applying several branches of business rules) then apply quantities to inventory within that time frame.

    Therefore; I may need to trim the fat and if faced with a less efficient but more easily maintainable route, I may opt for higher maintenance if it makes a big difference. If it did late binding on every iteration, (and each assembly will be a different function from the one prior), that would make it un-feasible. If it indeed behaves like any other pre-compiled SQL, then it's a happy medium.
    Last edited by vich; 03-28-07 at 19:04.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    late binding is a term used in programming where you are using an interpreted language like javascript or python. what it means is that types are assigned to variables and expressions at runtime.

    this is contrasted to early binding which is what happens in a compiled language like C++, where types are bound to expressions at compile time, not runtime.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Late binding isn't a term used in SQL Server and there is no direct parallel. Whether or not a statement is cached\ cachable is the closest equivalent I guess so in that sense you used the wrong term in the right place
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2006
    Posts
    1
    Hi Vich,

    This is from BOL 2005, but it works in 2000 too (just not documenting in BOL 2000)

    Output parameters can also be used with sp_executesql. The following example retrieves a name from the AdventureWorks.HumanResources.Employee table and returns it in an output parameter.


    DECLARE @IntVariable int;
    DECLARE @SQLString nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);
    DECLARE @max_title varchar(30);

    SET @IntVariable = 197;
    SET @SQLString = N'SELECT @max_titleOUT = max(Title)
    FROM AdventureWorks.HumanResources.Employee
    WHERE ManagerID = @level';
    SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
    SELECT @max_title;

  10. #10
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Thank you for all the responses. Apologies for not jumping on all the suggestions since I found a work-around (stated above), but it's as un-elegant as it comes.

    I hard-coded the function names into a "calling function" - that has to be maintained every time I add a new business rules function.

    This "calling function" is called about 500,000 times in a single run, and each subsequent "call" will be referencing a different (database driven) function name. I will have hundreds of different possibilities.

    "Early Binding" is a term tossed around when I was studying Oracle 8. Maybe the author I read was a programmer who used inappropriate terminology meant to communicate with a programmer, but he was referring to when the script is compiled. His point was that the Script Compilation is expensive and cannot occur until all the database object names are known.

    So; I'm looking for an inexpensive (performance wise) method for accomplishing this.

    I'm no DBA and have never read up on SQL Server internals and don't have the luxury of a DBA (1 man shop here) so I'm only hoping I don't "kill the computer".

    FYI: Here's the application:

    It makes a temp table of all the possible Inventory Assembly Nodes for a manufacturing line item (on average about 100 per line). The "YN" functions are Yes/No business rules that determine if an Assembly Node (branch) is appropriate for this line item. (Not shown here is some further processing on the results).

    There are about 5,000 lines per night. This SP is executed once for each line.
    CREATE PROCEDURE dbo.spinb_BuildAssyList @InvLineID int = 3051239 , @LIAssyBatchID smallint AS
    /*
    For one line item, this will generate the Assembly table that has all valid nodes appropriate to THAT line item (YN checks done).

    */

    declare @t table (assyID int, levelnum int, Seq int, YN bit, isComponent bit)

    declare @Recordid int
    set @RecordID = 3

    declare @One bit
    set @One = 1

    -- seed the table
    insert into @t (AssyID, LevelNum, Seq, YN, isComponent)
    select AssyID, LevelNum, Seq, @One, isComponent
    from tbinb_Assys
    where tbinb_Assys.AssyID = @RecordID

    -- Populate the temp (memory) table with all possible nodes
    while @@RowCount > 0
    insert into @t (AssyID, LevelNum, Seq, YN, isComponent)
    select tbinb_Assys.AssyID
    ,tbinb_Assys.LevelNum
    ,tbinb_Assys.Seq
    ,dbo.fnb_YN_CheckName (fnNameYN,@InvLineID) -- function returns the YN results, or defaults to true if it is null
    ,tbinb_Assys.isComponent
    from tbinb_Assys
    inner join @t RecordList
    on tbinb_Assys.ParentAssyID = RecordList.AssyID
    where not exists
    (select *
    from @t CurrentRecords
    where CurrentRecords.AssyID = tbinb_Assys.AssyID)
    .
    .(SP does further processing on the resulting temp table, ultimately copying the valid node branches to a permanent table linked to that line item). Also note: All access to the temp table is serial, so indexing is not appropriate.
    .
    Where "fnNameYN" and "@InvLineID" are columns in my BOM "tbAssy" (Inventory Assembly definition) table.

    This works beautifully and populates the test run of 110,000 records in about 80 seconds.

    OK; my gut tells me that:

    1. Using a dynamically assigned function name that changes on every subsequent occurrence will cause this to recompile that many times (500,000 times). If however; CACHE is smart enough to keep around all of the 1000 or so possible variations so that each one is only compiled once on a run, that that is an incorrect assumption and I should pursue using "Dynamic SQL".

    Or better; if CACHE is smart enough to keep the partial compilation around and just plug in the function name later, then it'll probably even run faster using Dynamic SQL (the sp_execSQL...).

    2. I'm going to need to do some kind of clean-up of temp files or I'll be in trouble pretty soon. I have no idea about the ramifications. Each use of the temp file is very small (like 100 rows).

    Do I need to destroy something? Is there a "memory only" version I can use?



    jst1699 - Thank you for the explicit example. Even if I don't use it for this instance (for performance reasons), I've bookmarked it for future use.
    Last edited by vich; 04-09-07 at 16:57.

  11. #11
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Update:

    I need sp_executesql to work from within a function. Even though it's listed under "Extended Stored Procedures", it generates this message:

    Server: Msg 557, Level 16, State 2, Procedure fn_DoYNFunction_bit, Line 16
    Only functions and extended stored procedures can be executed from within a function.
    This is the function I'm attempting:
    CREATE FUNCTION dbo.fn_DoYNFunction_bit (@LineItem int, @FnName varchar(50))
    RETURNS bit AS
    BEGIN
    /*
    Uses Dynamic SQL to call the correct function and get the YN answer.

    NOTE: NOT WORKING. PROBLEM with using Extended Stored Procedure "sp_executesql" within a function (supposed to work!!! arrrg)
    */

    declare @Answer bit, @sql nvarchar(4000), @Params nvarchar(4000)

    set @sql = N'SET @Ans = dbo.' + @FnName + '(' + rtrim(convert(nvarchar(50),@LineItem)) + ')'

    SET @params = '@Ans bit OUTPUT '

    EXEC sp_executesql @sql, @params, @Ans = @Answer OUTPUT

    return @Answer

    END
    I need this to work from within a function for performance reasons ... so I can update probably 100,000 rows with a single update statement (rather than using a cursor). If there were a way to execute a stored procedure (for each row) within an SQL Update statement, I wouldn't need this. For example:


    Code:
    while @@RowCount > 0
    	insert into @t (AssyID, LevelNum, Seq, YN, isComponent)
    		select 	 tbinb_Assys.AssyID
    			,tbinb_Assys.LevelNum
    			,tbinb_Assys.Seq		
    			,dbo.fnb_YN_CheckName (fnNameYN,@InvLineID)  -- function returns the YN results, or defaults to true if it is null
    			,tbinb_Assys.isComponent		
    		from tbinb_Assys
    	    	inner join @t RecordList  
    		on tbinb_Assys.ParentAssyID = RecordList.AssyID
    		where not exists 
    			(select * 
    			from @t CurrentRecords 
    			where CurrentRecords.AssyID = tbinb_Assys.AssyID)
    I suppose my hard coded function with all the possible function names hard coded (as shown above) will have to suffice.
    Last edited by vich; 05-04-07 at 19:11.

Posting Permissions

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