countrymeister
asked on
Dynamic SQL
I have a stored procedure where I am using a pivot function , I have a parameter @OrderCodes
that is comma delimited.
If I try to replace the (A, B, C, D) with (@OrderCodes) , the stored procedure fails to compile.
What other option do I have to stick the comma delimited value into the brackets
that is comma delimited.
If I try to replace the (A, B, C, D) with (@OrderCodes) , the stored procedure fails to compile.
What other option do I have to stick the comma delimited value into the brackets
USE [OrderDB]
GO
--drop procedure spGetOrderReport
/****** Object: StoredProcedure [dbo].[spGetOrderReport] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGetOrderReport]
@StartDate datetime,
@EndDate datetime ,
@OrderCodes varchar(1000)
AS
BEGIN
SET NOCOUNT ON
SELECT * INTO #tmpResults
FROM (
select * from
(select Order , convert(varchar,EntryDate,101) As EntryDate, Result, ResultTest from dbo.tblOrders )
p
pivot
(
max(Result) for ResultTest in (A,B,C,D)
) as pvt
where pvt.EntryDate IS NOT NULL
and pvt.EntryDate BETWEEN @StartDate AND @EndDate
) result
select * from #tmpResults
order by Order , EntryDate
END
go
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this
CREATE PROCEDURE [dbo].[spGetOrderReport]
@StartDate datetime,
@EndDate datetime ,
@OrderCodes varchar(1000)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[tmpResults12345]') AND type = 'U')
drop table tmpResults12345
declare @strSql nvarchar(2000)
set @strSql ='SELECT * INTO #tmpResults
FROM (
select * from
(select [Order] , convert(varchar,EntryDate,101) As EntryDate, Result, ResultTest from dbo.tblOrders )
p
pivot
(
max(Result) for ResultTest in ('+@OrderCodes+')
) as pvt
where pvt.EntryDate IS NOT NULL
and pvt.EntryDate BETWEEN @StartDate AND @EndDate
) result'
exec sp_executesql @strSql
select * from tmpResults12345
order by [Order] , EntryDate
drop table tmpResults12345
END
go
Hi Sachin,
Where you creating table "tmpResults12345"?
Where you creating table "tmpResults12345"?
ASKER
I get the following error
Conversion failed when converting date and/or time from character string.
I tried to do this to the date clause, because of the above error, but that did not help
and pvt.EntryDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + '
Conversion failed when converting date and/or time from character string.
I tried to do this to the date clause, because of the above error, but that did not help
and pvt.EntryDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + '
Hi,
your 2nd solution should work.
your 2nd solution should work.
USE [OrderDB]
GO
--drop procedure spGetOrderReport
/****** Object: StoredProcedure [dbo].[spGetOrderReport] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spGetOrderReport]
@StartDate datetime,
@EndDate datetime ,
@OrderCodes varchar(1000)
AS
BEGIN
SET NOCOUNT ON
declare @sql varchar(max)
set @sql= '
SELECT * INTO #tmpResults
FROM (
select * from
(select Order , convert(varchar,EntryDate,101) As EntryDate, Result, ResultTest from dbo.tblOrders )
p
pivot
(
max(Result) for ResultTest in ('+@OrderCodes+')
) as pvt
where pvt.EntryDate IS NOT NULL
and pvt.EntryDate BETWEEN ' + @StartDate + 'AND ' + @EndDate +'
) result
select * from #tmpResults
order by Order , EntryDate
'
print(@sql)
EXEC(@sql)
END
go
ASKER
It does not work, I still get the error
Also tried this, which also does not work
and pvt.EntryDate BETWEEN ' + convert(varchar,@StartDate ,101) + ' AND ' + convert(varchar,@EndDate,1 01) + '
Also tried this, which also does not work
and pvt.EntryDate BETWEEN ' + convert(varchar,@StartDate
Try using this Dynamic SQL.
Declare @OrderCodes varchar(1000)
set @OrderCodes = 'a,b,c,d'
declare @StartDate varchar(25)
, @EndDate varchar(25)
set @StartDate = '1900-01-01'
set @EndDate = getdate()
Declare @SQL varchar(max)
set @SQL = ''
set @SQL = @SQL + CHAR(10)
+'SELECT * INTO #tmpResults'
+ CHAR(10)+ 'FROM ('
+ CHAR(10)+ 'select * from'
+ CHAR(10)+ '(select Order , convert(varchar,EntryDate,101) As EntryDate, Result, ResultTest from dbo.tblOrders )'
+ CHAR(10)+ 'p'
+ CHAR(10)+ 'pivot'
+ CHAR(10)+ '('
+ CHAR(10)+ 'max(Result) for ResultTest in( ' + @OrderCodes +')'
+ CHAR(10)+ ') as pvt'
+ CHAR(10)+ 'where pvt.EntryDate IS NOT NULL'
+ CHAR(10)+ 'and pvt.EntryDate BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''
+ CHAR(10)+ ') result'
+CHAR(10)++CHAR(10)+'select * from #tmpResults'
+CHAR(10)+'order by Order , EntryDate'
print @SQL
EXEC(@SQL)
ASKER
I am not sure if I explained my question clearly, I need to replace the (A,B, C, D) with the values I get from the parameter @OrderCodes
Bhavesh,
Your example does not work , you cannot do (At least I could not compile) with a nested select in the pivot clause. I t requires an ID