March 16, 2018 at 3:49 am
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 ?
March 16, 2018 at 4:23 am
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
March 16, 2018 at 4:41 am
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
March 16, 2018 at 4:58 am
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
March 16, 2018 at 5:25 am
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
March 20, 2018 at 8:39 am
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? 🙂
March 20, 2018 at 8:49 am
As far as I remember you can't use dynamic SQL in a function.
March 20, 2018 at 9:39 am
Is there no way to show a preview of RECORD COUNT for each SQL STATEMENT saved in rows ? :crazy:
March 21, 2018 at 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.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 21, 2018 at 7:29 am
jonathan.crawford - Wednesday, March 21, 2018 7:20 AMLynn 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.
March 21, 2018 at 7:38 am
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.
March 21, 2018 at 9:16 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply