locked
Dynamic sql in functions RRS feed

  • Question

  • i have a function where i need to pass the table name and id to get the count of records from it

     

    i am using this function in the stored procedure

     

    how can i handle this type of situation using dynamic sql

    i know the fact that we cannot use dynamic sql in sp or functions

     

    is there any other way

    Thursday, December 23, 2010 6:52 AM

Answers

  • I think the best solution is to set up a view:

    CREATE VIEW category_view AS
        SELECT TableName = 'BrakeForm', PartID, RFIID, ...
        FROM   BrakeForm
        UNION  ALL
        SELECT TableName = 'WireHarness', PartID, RFIID, ...
        FROM   WireHareness
        ...

    The run the query against the view.

    You should scrap the function anyway, because data access from scalar function is a recipe for poor performance.

    And dynamic SQL is out of the question in my opinion, unless there are hundreds of these tables. But in that case, it will be very inconvenient anyway.

    I don't know much about Sharepoint, but as I understand it, it is intended to be an end in itself. I know that adding indexes to your sharepoint database is not supported. I don't know what applies to views or stored procedures, but you could of course have them in a separate database. However, if you need to write a lot of code against the sharepoint database, it may be worth considering to extract the data to a more relational format.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Proposed as answer by Naomi N Thursday, December 23, 2010 2:38 PM
    • Marked as answer by KJian_ Wednesday, December 29, 2010 8:11 AM
    Thursday, December 23, 2010 1:12 PM

All replies

  • You can use dynamic SQL in stored procedures.

    See uspProductSearch example in: http://www.sqlusa.com/bestpractices/dynamicsql/

     

     


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    Thursday, December 23, 2010 7:14 AM
  • Try something like:

    declare @tableName nvarchar(50),@qry nvarchar(500)
    set @tableName='Tablename'
    set @qry=N' select count(*) from ' + @tableName
    exec sp_executesql @qry

     

     

     

     


    mobin
    Thursday, December 23, 2010 7:17 AM
  • Tried this but not working
    Thursday, December 23, 2010 7:26 AM
  •  

    TO ALL:

    For quick assistance post as much information as possible. The more the better.

    Posting just "not working" makes assisting you very difficult.

    Thanks. 


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    Thursday, December 23, 2010 7:34 AM
  • > i have a function where i need to pass the table name and id to get the count of records from it

    Rather than asking for something that you already know that can't be done, please tell us the base problem you are trying to solve, and we might be able to help you with a solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, December 23, 2010 7:55 AM
  • My Function code is below. i want to use this function in sp by passing the table name and id, 
    but if you look into the function i am actually hardcoding the table names, is there any way to do it dynamically
    ALTER FUNCTION [dbo].[GetRFITotalPartCount] 
    (
     @RFIID int,
     @CategoryTableName nchar(100)
    )
    RETURNS int
    AS
    BEGIN
    
     DECLARE @result int
     
     SET @result = 0
     
     IF @CategoryTableName = 'BrakeForm'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM BrakeForm WHERE RFIID = @RFIID)
     ELSE IF @CategoryTableName = 'WireHarness'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM WireHarness WHERE RFIID = @RFIID)
     
     ELSE IF @CategoryTableName = 'SheetMetal'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM SheetMetal WHERE RFIID = @RFIID)
     
     ELSE IF @CategoryTableName = 'Bearing'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM Bearing WHERE RFIID = @RFIID)
     
     ELSE IF @CategoryTableName = 'Hose'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM Hose WHERE RFIID = @RFIID)
     
     ELSE IF @CategoryTableName = 'Annunciator'
      SELECT @result = (SELECT COUNT(DISTINCT PartId) FROM Annunciator WHERE RFIID = @RFIID)
     
     RETURN @result
    
    END
    
    Thursday, December 23, 2010 8:19 AM
  • Hi  Varun,

    It seems that the "Part catgegory" is a property of the entity "part" and therefore should be modeled as a column in a "Parts" table instead of having separate tables for sheetmetals, bearings, hoses etc. If you change the design this way, your query becomes very simple...

    SELECT COUNT(...) FROM Parts WHERE Category =... AND RFIID = ... 

    This way you can easily accomodate new parts as well without changing the schema of the database.

    HTH 


    Ami Levin, SQL Server MVP. CTO, DBSophic LTD. - http://www.dbsophic.com/ --Performance is the most significant driver in maintaining data and service availability.--
    Thursday, December 23, 2010 8:40 AM
  • Hi Ami

     

    Thanks very much for the reply,

    I agree with what you say but i cannot change the schema as this is the schema of the Sharepoint 2010, Each list in sharepoint is a table in the the database

    so we dont have flexibility to change the schema

    Is there any othere solution without changing the schema

    Thursday, December 23, 2010 8:48 AM
  • but if you look into the function i am actually hardcoding the table names, is there any way to do it dynamically

    Use a dynamic SQL stored procedure.  Link: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

     


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    Thursday, December 23, 2010 9:12 AM
  • My Stored proc is below, Please let me know how can i use dynamic sql here. i have already place my function code in the above post. 
    
    please check there
    
    ALTER PROCEDURE [dbo].[GetAllRFI]
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	DECLARE @x int
    
    
    SELECT
     rfi.RFIID, 
     rfi.StartDate, 
     rfi.EndDate, 
     
     dbo.GetRFITotalPercentComplete(rfi.RFIID, cat.CategoryTableName) as [PercentComplete],
     rfi.Title, 
     rfi.Status, 
     rfi.MarkComplete, 
     dbo.GetRFITotalPartCount(rfi.RFIID, cat.CategoryTableName) as [PartCount],
     cat.CategoryTableName, 
     cat.CategoryName,
     dbo.GetRFITotalSupplierCount(rfi.RFIID, cat.CategoryTableName) as [SupplierCount]
    FROM
     RFIInformation rfi
     JOIN Categories cat ON rfi.CategoryID = cat.CategoryID
     
    ORDER BY 
     rfi.StartDate DESC
    
    END
    
    Thursday, December 23, 2010 9:18 AM
  • Just follow  uspProductSearch dynamic SQL stored procedure example in: http://www.sqlusa.com/bestpractices/dynamicsql/

    Let us know if you succeed.

    The following link also has an example:

    http://www.codeproject.com/KB/database/Building_Dynamic_SQL.aspx


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    Thursday, December 23, 2010 9:28 AM
  • My Function code is below. i want to use this function in sp by passing the table name and id,

      but if you look into the function i am actually hardcoding the table names, is there any way to do it dynamically


    I know you are looking for a dynamic sql alternative for this function. But I'll advise you against that. What you are doing currently is much better than dynamic sql. Though the length of the function becomes too big, it would be much faster in execution than any dynamic sql alternative.

    Instead of the IF..ELSEIF.. structure, you may consider CASE statements though. That would be more readable; but then that's just a personal preference.

     

     


    Pradeep, Microsoft MVP (Visual Basic)
    Thursday, December 23, 2010 9:48 AM
  • You can write ur work in SP instead of function like this:---

    ALTER PROCEDURE [dbo].[GetAllRFI]
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -----------------------------
       
        declare @CategoryTableName varchar(100), @RFIID int=1
        set @CategoryTableName='employee'
        declare @result bigint
        declare @st varchar(2000)
        set @st = ' select @result=count(DISTINCT PartId) from ' + @CategoryTableName +' WHERE RFIID = ' + @RFIID + ' '
       
        --print @st
        --exec (@st)

        create table #test (cnt bigint)
        insert into #test
        exec (@st)


        select @result=cnt from #test
        --print @result

        drop table #test

        --------------------------
       
        DECLARE @x int

       
    SELECT
     rfi.RFIID,
     rfi.StartDate,
     rfi.EndDate,
     
     dbo.GetRFITotalPercentComplete(rfi.RFIID, cat.CategoryTableName) as [PercentComplete],
     rfi.Title,
     rfi.Status,
     rfi.MarkComplete,
     @result as [PartCount],
     cat.CategoryTableName,
     cat.CategoryName,
     dbo.GetRFITotalSupplierCount(rfi.RFIID, cat.CategoryTableName) as [SupplierCount]
    FROM
     RFIInformation rfi
     JOIN Categories cat ON rfi.CategoryID = cat.CategoryID
     
    ORDER BY
     rfi.StartDate DESC

    END
    ---------------


    --------------------------------- Devender Bijania
    Thursday, December 23, 2010 11:09 AM
  • I think the best solution is to set up a view:

    CREATE VIEW category_view AS
        SELECT TableName = 'BrakeForm', PartID, RFIID, ...
        FROM   BrakeForm
        UNION  ALL
        SELECT TableName = 'WireHarness', PartID, RFIID, ...
        FROM   WireHareness
        ...

    The run the query against the view.

    You should scrap the function anyway, because data access from scalar function is a recipe for poor performance.

    And dynamic SQL is out of the question in my opinion, unless there are hundreds of these tables. But in that case, it will be very inconvenient anyway.

    I don't know much about Sharepoint, but as I understand it, it is intended to be an end in itself. I know that adding indexes to your sharepoint database is not supported. I don't know what applies to views or stored procedures, but you could of course have them in a separate database. However, if you need to write a lot of code against the sharepoint database, it may be worth considering to extract the data to a more relational format.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Proposed as answer by Naomi N Thursday, December 23, 2010 2:38 PM
    • Marked as answer by KJian_ Wednesday, December 29, 2010 8:11 AM
    Thursday, December 23, 2010 1:12 PM
  • Hi

    If You use count function , sql server is going to scan all tables or cluster index and this operation is very heavy to get all records count. I think flowing function should be best way to get record count for a table

     

    Regards

    CREATE FUNCTION GetRecordCountForATable(@TableName varchar(255))
    returns bigint
    as
    begin
    return
    (
    SELECT 
     ISNULL(
     SUM (
    		 CASE 
    			 WHEN (index_id < 2) THEN row_count 
    			 ELSE 0 
    		 END 
    	 ) ,0)
     
     FROM sys.dm_db_partition_stats 
     WHERE object_id = OBJECT_ID(@TableName)
    ) 
    end 
    GO
    
    Or
    CREATE FUNCTION GetRecordCountForATable2(@TableName varchar(255))
    returns bigint
    as
    begin
    return
    (
    SELECT 
     ISNULL(SUM (rows) ,0)
     
     FROM sysindexes
     WHERE id = OBJECT_ID(@TableName) and indid<2
    ) 
    end 
    GO
    Note : At the first function uses dmv and dmvs required some special permission (VIEW DATABASE STATE or VIEW SERVER STATE), If you dont give VIEW DATABASE STATE permissions you can use second function
    Thursday, December 23, 2010 2:24 PM
  • > If You use count function , sql server is going to scan all tables or
    cluster index and this operation is very heavy to get all records count. I
    think flowing function should be best way to get record count for a table

    Varun Kumar wanted count distinct occurrances of a value, which you cannot
    get from sys.dm_db_partition_stats.

    There is also an issue with permissions if the procedure is intended to be
    used by plain users.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, December 23, 2010 11:14 PM