RIAS
asked on
Convert SP in a format for debugging
Hello,
i have a stored procedure :
--'DECLARE @SQL VARCHAR(MAX)=''
--SET @SQL
--EXECUTE (@SQL)
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
how can i put it in a format as :--'DECLARE @SQL VARCHAR(MAX)=''
--SET @SQL
--EXECUTE (@SQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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)
ASKER
Cheers!
Cheers Rias!!
ASKER
Thanks Vitor,
But, got the solution quickly by Pawan.
So had to accept the answer.
But, got the solution quickly by Pawan.
So had to accept the answer.
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:
Open in new window