top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Nested WITH clauses for Common Table Expressions in SQL Server

+1 vote
354 views 
WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y
posted Jul 28, 2017 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

code in SQL Server which search for a particular string in all the columns of a table

Searching and finding a string value for all columns of table in SQL.

Once again this is where T-SQL comes in handy along with the use of system tables or system views. The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar).

The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:

stringToFind - this is the string you are looking for. This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.
schema - this is the schema owner of the object
table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table

The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.

USE master 
GO 

CREATE PROCEDURE query_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS 

DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @cursor VARCHAR(8000) 

BEGIN TRY 
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
   SET @where = '' 

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = ''' + @schema + ''' 
   AND TABLE_NAME = ''' + @table + ''' 
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 

   EXEC (@cursor) 

   OPEN col_cursor    
   FETCH NEXT FROM col_cursor INTO @columnName    

   WHILE @@FETCH_STATUS = 0    
   BEGIN    
       IF @where <> '' 
           SET @where = @where + ' OR' 

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
       FETCH NEXT FROM col_cursor INTO @columnName    
   END    

   CLOSE col_cursor    
   DEALLOCATE col_cursor  

   SET @sqlCommand = @sqlCommand + @where 
   --PRINT @sqlCommand 
   EXEC (@sqlCommand)  
END TRY 
BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.' 
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
   BEGIN 
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
   END 
END CATCH

Once the stored procedure has been created you can run some tests.

Here are some tests that were done against the QueryHome database.

Find the value 'Irv%' in the Person.Address table.

USE QueryHome
GO 
EXEC query_FindStringInTable 'Irv%', 'Person', 'Address'

Table

 READ MORE
...