none
Dynamic query execution fails RRS feed

  • Question

  • I create a stored procedure where functions are created dynamically. When I print the query then it s executed succesfully in new query windows but if I execute it then its giving error.

    Please find the stored procedures below

                  

    CREATE procedure [dbo].[cre

    atedynamicfunction1_1] 
    @SchemaName varchar(Max)
    as 
    begin

    Declare @SQL varchar(max);

    set @SQL= '
    CREATE FUNCTION ['+@SchemaName+'].[fn_Btl_To_PhCase] 
    (   
    @ItemId INT,
    @value INT
    )

    RETURNS numeric(18,2)
    AS
    BEGIN
    DECLARE @nob numeric(18,2), @cs numeric(18,2)
    SELECT @nob = nob FROM sfa_item_master WHERE item_id = @ItemId;
    SET @cs = (@value / @nob)
    RETURN @cs
    END


    GO

    CREATE FUNCTION ['+@SchemaName+'].[fn_rev_Calculate_nob] 
    (   
    @ItemId INT,
    @value INT
    )

    RETURNS numeric(18,2)
    AS
    BEGIN
    DECLARE @nob INT, @rem INT, @cs INT
    DECLARE @decvalue numeric(18,2);

    SELECT @nob = nob FROM sfa_item_master WHERE item_id = @ItemId

    SET @cs = (@value / @nob)
    SET @rem = (@value % @nob)

    IF(@rem = 0)
    BEGIN
    SET @decvalue = CONVERT(NUMERIC(18,2), @cs)
    END
    ELSE IF(@rem<=10)
    BEGIN
    SET @decvalue = CONVERT(NUMERIC(18,2), (CONVERT(VARCHAR, @cs)) + ''.0'' + (CONVERT(VARCHAR, @rem)))
    END
    ELSE
    BEGIN
    SET @decvalue = CONVERT(NUMERIC(18,2), (CONVERT(VARCHAR, @cs)) + ''.'' + (CONVERT(VARCHAR, @rem)))
    END

    RETURN @decvalue
    END


    GO


    CREATE FUNCTION ['+@SchemaName+'].[fnCalcDistanceKM](@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)  
    RETURNS FLOAT  
    AS  
    BEGIN  
       RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371  
    END


    GO


    CREATE FUNCTION ['+@SchemaName+'].[fnGetAllHierarchyDistributor](@id int)
    RETURNS @Childs TABLE (
    DistID bigint

    AS
    BEGIN

    ;WITH ret AS(  
    SELECT  *  
    FROM  sfa_distributor_master
    WHERE dist_id = CONVERT(VARCHAR,@id)
    UNION ALL  
    SELECT t.*  
    FROM  sfa_distributor_master t INNER JOIN  
    ret r ON t.parent_dist_id = CONVERT(VARCHAR,r.dist_id) 
    and t.parent_dist_id<>t.dist_id)

    INSERT INTO @Childs (DistID)
    SELECT dist_id
    FROM    ret

       RETURN;
    END;

            GO


    Create FUNCTION ['+@SchemaName+'].[fnGetAllHierarchyZone](@id int)
    RETURNS @Childs TABLE (
    ZoneID bigint

    AS
    BEGIN

    ;WITH ret AS(  
    SELECT  *  
    FROM  sfa_zone_master
    WHERE zone_id = CONVERT(VARCHAR,@id)
    UNION ALL  
    SELECT t.*  
    FROM  sfa_zone_master t INNER JOIN  
    ret r ON t.parent_zone_id = CONVERT(VARCHAR,r.zone_id) 
    and t.parent_zone_id<>t.zone_id)

    INSERT INTO @Childs (ZoneID)
    SELECT zone_id
    FROM    ret

       RETURN;
    END;




    '


    --print @SQL

    EXEC (@SQL)

    end

    Please find the below error

    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 18 [Batch Start Line 2]
    Incorrect syntax near 'GO'.
    Msg 134, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 30 [Batch Start Line 2]
    The variable name '@nob' has already been declared. Variable names must be unique within a query batch or stored procedure.
    Msg 137, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 35 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 37 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 156, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 41 [Batch Start Line 2]
    Incorrect syntax near the keyword 'ELSE'.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 41 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 43 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 47 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 54 [Batch Start Line 2]
    Incorrect syntax near 'GO'.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 61 [Batch Start Line 2]
    Must declare the scalar variable "@lat1".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 65 [Batch Start Line 2]
    Incorrect syntax near 'GO'.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 78 [Batch Start Line 2]
    Must declare the scalar variable "@id".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 83 [Batch Start Line 2]
    Incorrect syntax near ')'.
    Msg 1087, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 85 [Batch Start Line 2]
    Must declare the table variable "@Childs".
    Msg 1075, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 89 [Batch Start Line 2]
    RETURN statements in scalar valued functions must include an argument.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 105 [Batch Start Line 2]
    Must declare the scalar variable "@id".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 110 [Batch Start Line 2]
    Incorrect syntax near ')'.
    Msg 1087, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 112 [Batch Start Line 2]
    Must declare the table variable "@Childs".
    Msg 1075, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 116 [Batch Start Line 2]
    RETURN statements in scalar valued functions must include an argument.

    (1 row affected)

    If I use print(@sql) and then run the output then its working properly

    Please help. 

    Thursday, April 18, 2019 6:11 AM

Answers

  • CREATE FUNCTION' must be the first statement in a query batch.

    That's what I already wrote, it must be the first and only in a Batch; your approach with one dynamic SQL Statement is simply not possible.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SouravDutta Friday, April 19, 2019 5:13 AM
    Thursday, April 18, 2019 9:55 AM
    Moderator

All replies

  • SQL Server Utilities Statements - GO is not a valid SQL command, it's interpreted by Tools like SSMS or SqlCmd; you can not use it in dynamic SQL.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, April 18, 2019 6:39 AM
    Moderator
  • Thanks. Then what command should I use instead of "go" command
    Thursday, April 18, 2019 6:44 AM
  • No command at all, just remove GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 18, 2019 6:47 AM
    Moderator
  • Your approach with one dynamic SQL Statement is simply not possible, a Create FUNCTION must be the first and only Statement in a Batch.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, April 18, 2019 6:51 AM
    Moderator
  • Hi Olaf, Then how could I achive this by which I can create dynamic function like I written in stored procedure.
    Thursday, April 18, 2019 9:29 AM
  • Hi Uri,

    I remove GO but still getting below error

    Msg 156, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 18 [Batch Start Line 2]
    Incorrect syntax near the keyword 'CREATE'.
    Msg 111, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 18 [Batch Start Line 2]
    'CREATE FUNCTION' must be the first statement in a query batch.
    Msg 134, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 28 [Batch Start Line 2]
    The variable name '@nob' has already been declared. Variable names must be unique within a query batch or stored procedure.
    Msg 137, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 33 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 35 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 156, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 39 [Batch Start Line 2]
    Incorrect syntax near the keyword 'ELSE'.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 39 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 41 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 45 [Batch Start Line 2]
    Must declare the scalar variable "@rem".
    Msg 111, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 54 [Batch Start Line 2]
    'CREATE FUNCTION' must be the first statement in a query batch.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 58 [Batch Start Line 2]
    Must declare the scalar variable "@lat1".
    Msg 111, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 63 [Batch Start Line 2]
    'CREATE FUNCTION' must be the first statement in a query batch.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 73 [Batch Start Line 2]
    Must declare the scalar variable "@id".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 78 [Batch Start Line 2]
    Incorrect syntax near ')'.
    Msg 1087, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 80 [Batch Start Line 2]
    Must declare the table variable "@Childs".
    Msg 1075, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 84 [Batch Start Line 2]
    RETURN statements in scalar valued functions must include an argument.
    Msg 111, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 88 [Batch Start Line 2]
    'CREATE FUNCTION' must be the first statement in a query batch.
    Msg 137, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 98 [Batch Start Line 2]
    Must declare the scalar variable "@id".
    Msg 102, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 103 [Batch Start Line 2]
    Incorrect syntax near ')'.
    Msg 1087, Level 15, State 2, Procedure fn_Btl_To_PhCase, Line 105 [Batch Start Line 2]
    Must declare the table variable "@Childs".
    Msg 1075, Level 15, State 1, Procedure fn_Btl_To_PhCase, Line 109 [Batch Start Line 2]
    RETURN statements in scalar valued functions must include an argument.

    (1 row affected)

    Thursday, April 18, 2019 9:30 AM
  • It is not possible to create function inside Stored Proc. You will have to create function separately and call it inside your SP.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 18, 2019 9:35 AM
    Moderator
  • CREATE FUNCTION' must be the first statement in a query batch.

    That's what I already wrote, it must be the first and only in a Batch; your approach with one dynamic SQL Statement is simply not possible.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by SouravDutta Friday, April 19, 2019 5:13 AM
    Thursday, April 18, 2019 9:55 AM
    Moderator
  • Thank you all. Now its working.
    Friday, April 19, 2019 5:13 AM