Link to home
Start Free TrialLog in
Avatar of countrymeister
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
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

Open in new window

SOLUTION
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of countrymeister
countrymeister

ASKER

Sachinpatil10d
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

Hi Sachin,

Where you creating table "tmpResults12345"?
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 +  '
Hi,

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

Open in new window

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,101) + '
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)

Open in new window