How to run and return this Multiple variables query?

[MrSQLDBA 0]

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 1,598]

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.