MrSQLDBA Posted March 23, 2015 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
JohnOne Posted March 23, 2015 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.
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