Dynamic expression in a view

  • blade832

    SSC Journeyman

    Points: 95

    Hi all,
    I have a question. I have a table with a query saved in a column.
    I want show in other column the count record of that query. 
    EX:

    ID    ;SELECT                            ;WHERE                             ;ORDER                       ;RECORDCOUNT
    1     ;Select * FROM MyTable      ;Column1 = 1                       ;By Column2                ;Result of (Select Count(*) where (Where Column)

    How can i have the result ?

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    This will get you the statement(s)
    SELECT [SELECT] + ' WHERE ' + [WHERE] + ' ORDER ' + [ORDER] = '; SELECT @@ROWCOUNT AS RecordCount;'
    FROM YourTableofQueries

    From there, you can use EXEC or sp_executesql to execute them.  Please provide table DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements for the MyTable and YourTableofQueries if you want a tested solution.  Beware, though: unless you control access to YourTableofQueries very carefully, you could end up with some very nasty stuff getting run in your database.

    John

  • blade832

    SSC Journeyman

    Points: 95

    I try a view like this :

    SELECT  Id, SqlWhere, SqlOrder, sp_executesql('SELECT COUNT(*) FROM MyTableOfContent WHERE ' + SqlWhere) AS RecordCount
    FROM   MyTableOfQuery

    I need RecordCount column as a preview of how many record in each query saved in table

    But sp_executesql if not a recognized function name

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    sp_executesql is a stored procedure.  You can't use it in the middle of a SELECT statement.  Like I said: table DDL and sample data, please, otherwise it's just too difficult to help you.

    John

  • blade832

    SSC Journeyman

    Points: 95


    USE [TEST]
    GO
    /****** Oggetto: Table [dbo].[MSPVISTE]  Data script: 03/16/2018 12:13:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[MSPVISTE](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Vista] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
        [SqlWhere] [varchar](2048) COLLATE Latin1_General_CI_AS NULL,
        [SqlOrder] [varchar](2048) COLLATE Latin1_General_CI_AS NULL,
        [Descrizione] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
        [Ordinamento] [int] NULL,
        [TopSelect] [int] NULL,
        [F_PuntoMittente] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
    CONSTRAINT [PK_MSPVISTE] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    ID:        41
    Vista:        VistaTest
    SqlWhere:    Committente = 'TEST'
    SqlOrder:    Priorita DESC
    Descrizione:    Test
    Ordinamento:    1
    TopSelect:    40

    'VistaTest' contain SqlSelect string from other table ("Select * FROM MSMAG") but i can bypass writing directly select statment with count function ("SELECT COUNT(*) FROM MSMAG").
    Instead, the where clause must be dynamic for each record

  • blade832

    SSC Journeyman

    Points: 95

    Im looking for UDF executing query as parameter.


    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION dbo.fnCountRecord(@SQL NVARCHAR(800))
    RETURNS int 
    AS
    BEGIN
        
        DECLARE @sqlquery AS NVARCHAR(500), @RESULT AS INT

        /* Build Transact-SQL String with parameter value */
        SET @sqlquery = '(SELECT COUNT(*) FROM ' + @SQL + ');'
        SET @RESULT = Execute(@SQLQuery)
        return @RESULT

    END

    GO

    Something like that, a function i can use in view that accept full select statment or where in paramter.
    Can someone explain me how to do? 🙂

  • Lynn Pettis

    SSC Guru

    Points: 442091

    As far as I remember you can't use dynamic SQL in a function.

  • blade832

    SSC Journeyman

    Points: 95

    Is there no way to show a preview of RECORD COUNT for each SQL STATEMENT saved in rows ? :crazy:

  • jonathan.crawford

    SSCertifiable

    Points: 6254

    Lynn is right and wrong, you can't execute the dynamic SQL in a function BUT you can build it in a function, and execute it after you've called the function to populate a variable. Use your sample function you just posted, but don't try to execute it, just return the string.

    Also, see https://ask.sqlservercentral.com/questions/44632/execute-dynamic-sql-from-within-a-function.html for other ideas

    However, the question is why you feel you need to do this? If you are trying to build a generic "tell me how many rows my query returns" query, then why go through all this torture when you could just use rownumber() or similar? If you want to trend how many of something lives in your sourcetable over time, then just write a query/stored procedure to count those things, group by whatever categories you need, and schedule that query to run on a regular basis, recording counts in a table somewhere for later analysis.

    Doing it like this opens you up to 1) not having it work all the time because you don't know how complicated users will get when feeding queries in, 2) risking SQL Injection problems (you'll definitely want to use sp_executesql to actually execute the dynamic SQL that comes back), 3) create headaches for troubleshooting badly formed queries 4) be unable to use it if you suddenly realize you need to have another join to a different table in there and you hard-coded the FROM clause in the function, and if you change it to solve the more complicated problem you'll screw up all the simple queries that people are using it for.

    Just because you can, doesn't mean you should. But, if you must, build the string in the function and execute after.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Lynn Pettis

    SSC Guru

    Points: 442091

    jonathan.crawford - Wednesday, March 21, 2018 7:20 AM

    Lynn is right and wrong, you can't execute the dynamic SQL in a function BUT you can build it in a function, and execute it after you've called the function to populate a variable. Use your sample function you just posted, but don't try to execute it, just return the string.

    Also, see https://ask.sqlservercentral.com/questions/44632/execute-dynamic-sql-from-within-a-function.html for other ideas

    However, the question is why you feel you need to do this? If you are trying to build a generic "tell me how many rows my query returns" query, then why go through all this torture when you could just use rownumber() or similar? If you want to trend how many of something lives in your sourcetable over time, then just write a query/stored procedure to count those things, group by whatever categories you need, and schedule that query to run on a regular basis, recording counts in a table somewhere for later analysis.

    Doing it like this opens you up to 1) not having it work all the time because you don't know how complicated users will get when feeding queries in, 2) risking SQL Injection problems (you'll definitely want to use sp_executesql to actually execute the dynamic SQL that comes back), 3) create headaches for troubleshooting badly formed queries 4) be unable to use it if you suddenly realize you need to have another join to a different table in there and you hard-coded the FROM clause in the function, and if you change it to solve the more complicated problem you'll screw up all the simple queries that people are using it for.

    Just because you can, doesn't mean you should. But, if you must, build the string in the function and execute after.

    Just so you know, I look at "using dynamic SQL" and "building and returning dynamic SQL" in a function as two different things.  From what I was reading it looked like the OP wanted to execute dynamic SQL in the function.

  • blade832

    SSC Journeyman

    Points: 95

    So i try to explain:

    I have a table where select statement are saved:

    (TABLEOFQUERY)
    Row ID 1) SELECT * FROM MYTABLE1
    Row ID 2) SELECT * FROM MYTABLE1 WHERE ID = 1
    Row ID 3) SELECT * FROM MY TABLE2 WHERE Value = 3

    Ecc...

    Now i want made a view (SELECT ID, STATEMENT, ROWCOUNT FROM TABLEOFQUERY) where "ROWCOUNT" is a preview of how many rows will return each statement saved in this row.

    I cant execute the select statment directly in a view and I thought about creating a function to which I would pass the selection query (modified with the count(*) instead of *) and return the result. But i can not even run it inside the function.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    You're not going to be able to do that in a view. Views can't execute procedures or dynamic SQL. Functions can't execute procedures or dynamic SQL.

    Consider using a procedure instead of a view, and do note that you're open to SQL injection attacks, if someone can insert malicious code into that table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 12 (of 12 total)

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