I am trying to insert data from one table (or Storeproc) to temp table. Temp table column count doesn't match from source table column count. I want insert source data into temp table from beginning columns and remaining columns will empty strings columns.That means if Temp Table has 10 columns and source table has only 3 columns then insert first 3 columns into temp table and remaining 7 columns will be empty or null.Example CREATE TABLE #VarTemp(Col1 nvarchar(256),Col2 nvarchar(256),Col3 nvarchar(256),Col4 nvarchar(256),Col5 nvarchar(256),Col6 nvarchar(256),Col7 nvarchar(256),Col8 nvarchar(256),Col9 nvarchar(256),Col10 nvarchar(256)) INSERT into #VarTemp EXEC sp_FindStringInTable 'Nareshbhai%', 'dbo', 'UserstoClean'; SELECT * FROM #VarTemp
If I execute separate EXEC sp_FindStringInTable 'Nareshbhai%', 'dbo', 'UserstoClean';
I am getting Constitid FirstName LastName email status pincode 3151502 Nareshbhai Desai desanar@iit.edu Inactive desai
Error is There was an error. Check to make sure object exists. (0 row(s) affected) Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
sp_FindStringInTable
isALTER PROCEDURE [dbo].[sp_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 CATCHGO