MrSQLDBA Posted March 23, 2015 Share Posted March 23, 2015 I would like to simplify this multiple queries into one script. ----The First Query --- This is a example of how I will run the single query then I will go to step2 SELECT * FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' -- Result will look like this id POLICY EFFDTE 9361343 CMPNE08929 20091101 ----The second Query. I input manually the data from the first query. BEGIN DECLARE @ResultsTable TABLE (ColumnName nvarchar(200), ColumnValue nvarchar(100)) DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),@TableName nvarchar(256), @TableName2 nvarchar(256) DECLARE @PolicyPrefix nvarchar(5), @PolicyID nvarchar(10), @PolicyEffDate date --SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') SET @TableName = ' ' --These values are used to search SET @PolicyPrefix = 'CMPMO' ------notices it only the policy letter part from the search result SET @PolicyID = '08929' ------notices it only the policy number part from the search result SET @PolicyEffDate = '2009-04-01' ------notices it has dashes from the search result SET @SearchStr2 = (SELECT [systemAssignId] FROM PDCDBPRD.dbo.[CoPolicyPointer] WHERE [PolicyPrefixCd] = @PolicyPrefix AND [PolicyId] = @PolicyID AND [PolicyEffDt] = @PolicyEffDate) WHILE (@TableName IS NOT NULL) BEGIN SET @ColumnName = QUOTENAME('SystemAssignId') SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM PDCDBPRD.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_NAME) > @TableName ) SET @TableName2 = 'PDCDBPRD.dbo.' + @TableName IF @ColumnName IS NOT NULL BEGIN INSERT INTO @ResultsTable EXEC( 'SELECT ''' + @TableName + ''', LEFT(' + @ColumnName + ', 100) FROM ' + @TableName2 + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + '''' ) END END SELECT DISTINCT ColumnName, ColumnValue FROM @ResultsTable END GO Link to comment Share on other sites More sharing options...
JohnOne Posted March 23, 2015 Share Posted March 23, 2015 Is there a half decent reason you registered on an AutoIt forum to post some database question? I'm sure you'd get better help on a database forum. AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now