Query dynamic?

Question

Query dynamic?

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have the following query:

select CONCAT('PtdBal',(select right(substring(PerNbr,5,2)-1+100,2) from SLApplication.dbo.GLSetup)) 
       from SLApplication.dbo.AcctHist 
       where substring(Sub, 16, 6) = '189621' 
       and acct like '822%733%' 
       and PtdBal05 < 0  
       and fiscYr =  (select substring(PerNbr, 1, 4) from SLApplication.dbo.GLSetup) 
       and LedgerID = '02'

Which gives me the following result:

    | (sin nombre de columna)
----|------------------------
  1 |   PtdBal05

But it turns out that PtdBal05 is the name of the column. How can I get the records / records that the column has? Can I do this in the query without using a Store Procedure or do I necessarily have to use a Store Procedure? I would prefer that this be done in the query. Is this a dynamic query? Thanks for the help.

sql sql-server

asked by abrahamhs 01.02.2017 в 01:46

source

2 answers

0

At the end it's like this:

DECLARE @sqlCommand varchar(1000)
DECLARE @columna varchar(75)
SELECT TOP 1 @columna = Columna FROM (
    select CONCAT('PtdBal',(
                            select right(substring(PerNbr,5,2)-1+100,2) 
                            from SLApplication.dbo.GLSetup
                           )
                 ) AS Columna 
    from SLApplication.dbo.AcctHist 
) AS T
SET @sqlCommand = 'SELECT ' + @columna + ' FROM SLApplication.dbo.AcctHist 
                   where substring(Sub, 16, 6) = ''189621''
                   AND acct LIKE ''822%733%''
                   AND PtdBal05 < 0
                   AND fiscYr = (
                         SELECT substring(PerNbr, 1, 4)
                         FROM SLApplication.dbo.GLSetup
                                )
                   AND LedgerID = ''02''
'
EXEC (@sqlCommand)

Thanks

answered by 03.02.2017 в 20:32

Get all matches given a value of an array of objects Rental time control

score 0 · Accepted Answer

Assuming that the result of your query returns a name of some column and the table is always SLApplication.dbo.AcctHist , and also the result is generated with the Id = 1 , then I propose the following dynamic query:

DECLARE @sqlCommand varchar(1000)
DECLARE @columna varchar(75)

SELECT TOP 1
    @columna = Columna
FROM 
(
    SELECT CONCAT (
        'PtdBal'
        ,(
            SELECT right(substring(PerNbr, 5, 2) - 1 + 100, 2)
            FROM SLApplication.dbo.GLSetup
            )
        ) AS Columna
    FROM SLApplication.dbo.AcctHist
    WHERE substring(Sub, 16, 6) = '189621'
        AND acct LIKE '822%733%'
        AND PtdBal05 < 0
        AND fiscYr = (
            SELECT substring(PerNbr, 1, 4)
            FROM SLApplication.dbo.GLSetup
            )
        AND LedgerID = '02'
) AS T

SET @sqlCommand = 'SELECT ' + @Columna + ' FROM SLApplication.dbo.AcctHist'
EXEC (@sqlCommand)

Where in the variable @columna = Columna the name of the field is saved, so that later the dynamic query is executed.