asked on

Convert SP in a format for debugging

Hello,
i have a stored procedure :

    SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  
   FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM   PROACT
      WHERE InvoiceNumber =@ColumnName1
)k

Open in new window

how can i put it in a format as :
--'DECLARE @SQL VARCHAR(MAX)=''

--SET @SQL
--EXECUTE (@SQL)

Vitor Montalvão

RIAS, please lose a little more time to provide us a better explanation of your issue.
You're referring a SP but you post a SELECT code then you asked about something that isn't in your code.

By the tittle and the few you posted I should assume that you want to know the query dynamically created before executing it?
If so then comment the EXEC command and use a PRINT instead:

--EXECUTE (@SQL) 
PRINT @SQL

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Hello Vitor,
Need it actually in this format where I can debug it.

      DECLARE @SQL VARCHAR(MAX)=''

            SET @SQL = 'IF EXISTS ( SELECT *
                              FROM PROARACT
                              WHERE InvoiceNumber = ' + '''' + CAST(@ColumnName1 AS VARCHAR(100)) + ''''      + ' AND LineChecked IS NULL )

                              SELECT 0 AS IsCheckedAll
                        ELSE
                              SELECT 1 AS IsCheckedAll'

            EXECUTE (@SQL)
END

Ryan Chong

try this:

DECLARE @SQL VARCHAR(MAX)=''

SET @SQL = 'SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  '+
'   FROM ( '+
'      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM   PROACT '+
 '     WHERE InvoiceNumber =@ColumnName1 '+
' )k '

EXECUTE (@SQL)

Open in new window

RIAS

ASKER

Cheers!

Pawan Kumar

Cheers Rias!!

RIAS

ASKER

Thanks Vitor,
But, got the solution quickly by Pawan.
So had to accept the answer.