Sign in to follow this  
Followers 0
MrSQLDBA

How to run and return this Multiple variables query?

2 posts in this topic

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

Share this post


Link to post
Share on other sites



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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0