How to fetch count ?

Question

How to fetch count ?

nikku

Ten Centuries

Points: 1103
More actions
April 30, 2014 at 12:39 am

#290770

Hi All,
I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like
'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.
I used execute @queryfromvariable but it does not gives me count..
Please let me know if anybody has done this type of requirement before.
Thanks in advance 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Replace * with count(*) in your query

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

nikku Ten Centuries Points: 1103 More actions · Answer 2

thanks for the reply sir but column contains following types of query

like

select * from table

select col 1, col 2, col 3 from table

etc

so I cannot change modify the query in colume.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Since you control the string that is to be executed, you can add some extra SQL to it.

For example:

DECLARE @SQLStatement NVARCHAR(1000) = '';

DECLARE @ID INT = 0;

SELECT @SQLStatement = Query, @ID = ID FROM TableA WHERE ToExecute = 1; -- fetch one single statement from table A

SET @SQLStatement = @SQLStatement + CHAR(13) + CHAR(10)

+ 'IF @@ROWCOUNT = 0' + CHAR(13) + CHAR(10)

+ 'BEGIN' + CHAR(13) + CHAR(10)

+ 'INSERT INTO TableB VALUES(' + CONVERT(VARCHAR(10),@ID) + ',''Fail'')' + CHAR(13) + CHAR(10)

+ 'END' + CHAR(13) + CHAR(10)

+ 'ELSE' + CHAR(13) + CHAR(10)

+ 'BEGIN' + CHAR(13) + CHAR(10)

+ 'INSERT INTO TableB VALUES(' + CONVERT(VARCHAR(10),@ID) + ',''Success'')' + CHAR(13) + CHAR(10)

+ 'END' + CHAR(13) + CHAR(10);

--print @SQLStatement;

EXEC sp_executesql @SQLStatement;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nikku Ten Centuries Points: 1103 More actions · Answer 4

nikku

Ten Centuries

Points: 1103

April 30, 2014 at 1:25 am

#1709718

Thank you Sir 🙂

thava SSCrazy Points: 2275 More actions · Answer 5

i am not sure what you are doing, anyway here is my contribution hope this will help what you want to do

USE tempdb

GO

IF OBJECT_ID('dbo.T1' ) IS NOT NULL

BEGIN

DROP TABLE dbo.T1

DROP TABLE dbo.T2

DROP TABLE dbo.T3

DROP TABLE dbo.T4

DROP TABLE dbo.TableA

DROP TABLE dbo.TableB

END

CREATE TABLE T1 (id INT)

CREATE TABLE T2 (id INT)

CREATE TABLE T3 (id INT)

CREATE TABLE T4 (id INT)

CREATE TABLE TableA (id INT IDENTITY(1,1), QUERY VARCHAR(250))

CREATE TABLE TableB (id INT , QUERYStatus VARCHAR(5))

GO

INSERT INTO dbo.T1 (id) VALUES (1),(1000)

GO

INSERT INTO dbo.T4 (id) VALUES (1),(1000)

GO

INSERT INTO dbo.TableA

([QUERY])

VALUES

('Select * from T1'),

('Select * from T2'),

('Select * from T3'),

('Select * from T4')

GO

DECLARE @id INT , @QUERY VARCHAR(250)

DECLARE @CQuery AS NVARCHAR(300)

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR

SELECT id, [QUERY]

FROM dbo.TableA

OPEN my_cursor

FETCH FROM my_cursor INTO @id, @QUERY

WHILE @@FETCH_STATUS = 0

BEGIN

SET @CQuery = 'IF EXISTS('+@QUERY+')

SELECT '+cast(@id AS VARCHAR)+' as ID, ''Pass'' as STatus

ELSE

SELECT '+cast(@id AS VARCHAR)+' as ID, ''Fail'' as STatus'

INSERT INTO TableB

EXEC sp_executesql @CQuery

FETCH FROM my_cursor INTO @id, @QUERY

END

CLOSE my_cursor

DEALLOCATE my_cursor

SELECT * FROM dbo.TableB AS tb

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 6

IF OBJECT_ID('tempdb..#temp')IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp

(

ID INT,

ID1 INT

)

INSERT INTO #temp

SELECT 1,2

UNION

SELECT 2,1

IF OBJECT_ID('tempdb..#temp2')IS NOT NULL

DROP TABLE #temp2;

CREATE TABLE #temp2

(

ID INT,

ID1 INT

)

DECLARE @tbl TABLE

(

ID INT IDENTITY(1,1),

Query NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT 'SELECT * FROM #temp' UNION

SELECT 'SELECT ID FROM #temp' UNION

SELECT 'SELECT ID1 FROM #temp' UNION

SELECT 'SELECT ID,ID1 FROM #temp2'

DECLARE @sql NVARCHAR(1000),@MININT INT,@MaxINT INT

SELECT @MinINT = MIN(ID),@MaxINT = MAX(ID)

FROM @tbl

WHILE(@Minint <=@MaxINT)

BEGIN

SELECT @sql = ''

SELECT @sql = 'IF EXISTS ('+Query+')BEGIN SELECT '+CAST(@MinINt AS NVARCHAR(100))+',''PASS'' END ELSE BEGIN SELECT '+CAST(@MinINt AS NVARCHAR(100))+',''FAIL'' END'

FROM @tbl

where ID = @MinINT

PRINT @sql

EXEC (@SQL)

SELECT @MinINT = @MININT +1

END

Regards,
Mitesh OSwal
+918698619998

David Burrows SSC Guru Points: 65142 More actions · Answer 7

DECLARE @Result char(4)

EXECUTE (@sql)

SET @Result = CASE WHEN @@ROWCOUNT=0 THEN 'Fail' ELSE 'Pass' END

UPDATE tableB SET [column] = @Result WHERE ....

Far away is close at hand in the images of elsewhere.
Anon.