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

LVL 1
countrymeisterAsked:
Who is Participating?
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Try with this....
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

0
 
sachinpatil10dCommented:
try this

--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

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

For that you need to create a function.

Your code will be

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 (Select items from dbo.FN_GENERATETABLE(@OrderCodes,','))
) 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


- Bhavesh
GO
/****** Object:  UserDefinedFunction [dbo].[FN_GENERATETABLE]    Script Date: 10/14/2010 19:00:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FN_GENERATETABLE]( @STRING VARCHAR(8000),@DELIMITER CHAR(1) )
        RETURNS @RESULTS TABLE (ITEMS VARCHAR(8000))
AS


BEGIN

        --IF @STRING IS NULL OR @STRING='' RETURN
    SET @STRING =       LTRIM(RTRIM( @STRING ))
        
    DECLARE @INDEX INT
        DECLARE @SLICE VARCHAR(8000)
        DECLARE @SLICEPER VARCHAR(8000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1 
    
        WHILE @INDEX !=0
        BEGIN   
                -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
                SELECT @INDEX = CHARINDEX(@DELIMITER,@STRING)
                -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
                IF @INDEX !=0
                        SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
                ELSE
                        SELECT @SLICE = @STRING
                                
                -- PUT THE ITEM INTO THE RESULTS SET
                INSERT INTO @RESULTS(ITEMS) VALUES(@SLICE)
                -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
                SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
                        
                -- BREAK OUT IF WE ARE DONE
                IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
countrymeisterAuthor Commented:
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
0
 
sachinpatil10dCommented:
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

0
 
Bhavesh ShahLead AnalysistCommented:
Hi Sachin,

Where you creating table "tmpResults12345"?
0
 
countrymeisterAuthor Commented:
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 +  '
0
 
Bhavesh ShahLead AnalysistCommented:
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

0
 
countrymeisterAuthor Commented:
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) + '
0
 
Anil GolamariCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.