Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to fetch count ? Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 12:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:03 AM
Points: 91, Visits: 57
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
Post #1566225
Posted Wednesday, April 30, 2014 1:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1566236
Posted Wednesday, April 30, 2014 1:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:03 AM
Points: 91, Visits: 57
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.
Post #1566237
Posted Wednesday, April 30, 2014 1:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 13,620, Visits: 10,506
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;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1566239
Posted Wednesday, April 30, 2014 1:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:03 AM
Points: 91, Visits: 57
Thank you Sir
Post #1566246
Posted Wednesday, April 30, 2014 1:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1566248
Posted Wednesday, April 30, 2014 1:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 790, Visits: 645
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
Post #1566249
Posted Wednesday, April 30, 2014 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 7,095, Visits: 6,915
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.

Post #1566273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse