• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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

0
countrymeister
Asked:
countrymeister
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now