print out the stored procedure with parameter

[yan19454]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



ALTER PROCEDURE [dbo].[get_Quiz_log_by_emp_Avg]
@DateFrom datetime,
@DateTo_in datetime,
@strName varchar(26),
@dtDOB datetime,
@debug bit =0
AS
declare @DateTo datetime

select @DateTo = @DateTo_in+1

--DECLARE @sql nvarchar(4000)

SELECT AVG(CAST(SUBSTRING(A.Score, 1, LEN(A.Score) - 1) AS int)) AS AvgScore, A.[Quiz Name], HREMP.[FULL NAME], COUNT(*) AS CountT, SUM(CAST(SUBSTRING(A.Score, 1,
LEN(A.Score) - 1) AS int)) AS SumT
FROM (SELECT *
FROM [Quiz Log]
WHERE ([Quiz Log].Score <> 'Waived') AND ([Quiz Log].Score <> '0%') and [Quiz Log].[Employee Name]='& @strName &' and ([Access Quiz Time] >= @DateFrom and [Access Quiz Time] < @DateTo)) A INNER JOIN
HREMP ON A.[Employee Name] = HREMP.[FULL NAME] AND A.DOB = HREMP.DOB
GROUP BY A.[Quiz Name], HREMP.[FULL NAME] order by a.[Quiz Name]

IF @Debug = 1

BEGIN

PRINT PRINT ''

END

I have the following stored procedure.

I want to print the out the SQL select statement while I tested with parameter. Do you
how can I accomplish it ? Thx.


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[SQLBill]

Add PRINT @sql to your code. Put it right after the last line of the SELECT.

-SQLBill

Posting advice: FAQ481-4875

 

[okiiyama]

This would look good as text:

select sqlstr from(
select 	1 as 'a', 'SELECT AVG(CAST(SUBSTRING(A.Score, 1, LEN(A.Score) - 1) AS int)) AS AvgScore, A.[Quiz Name], 'as sqlstr
union
select  2 as 'a', 'HREMP.[FULL NAME], COUNT(*) AS CountT, SUM(CAST(SUBSTRING(A.Score, 1, LEN(A.Score) - 1) AS int)) AS SumT 'as sqlstr
union
select  3 as 'a', 'FROM (	SELECT *' as sqlstr
union 
select  4 as 'a', '	FROM [Quiz Log]' as sqlstr
union
select  5 as 'a', '	WHERE ([Quiz Log].Score <> ''Waived'') AND ([Quiz Log].Score <> ''0%'') ' as sqlstr
union
select  6 as 'a', '	and  [Quiz Log].[Employee Name]=''& @strName &'' ' as sqlstr
union
select  7 as 'a', '	and ([Access Quiz Time] >= @DateFrom and [Access Quiz Time] < '+@DateTo+')) A ' as sqlstr
union
select 	8 as 'a', 'INNER JOIN HREMP ON A.[Employee Name] = HREMP.[FULL NAME] AND A.DOB = HREMP.DOB ' as sqlstr
union 
select  9 as 'a', 'GROUP BY A.[Quiz Name], HREMP.[FULL NAME] ' as sqlstr
union
select 10 as 'a', 'order by a.[Quiz Name]' as sqlstr) a
order by a