We help IT Professionals succeed at work.

DYNAMIC SQL

Larry Brister
Larry Brister asked
on
My attached code works fine if I pass in a single id (1333)

However..the goal is to be able to pass in a single, a NULL (for all) or a means to do a where dealID IN (@dealID) for a group of id's.

Right now...
1333 returns the correct record
NULL retruns :
onversion failed when converting the varchar value 'NULL' to data type int.


And
DECLARE @dealID int
set @dealID = '1,2,3,4,5'
Returns:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '1,2,3,4,5' to data type int.


use portal
go
DECLARE @dealID int
set @dealID = NULL
DECLARE @columns VARCHAR(max)  
SELECT @columns = COALESCE(@columns + ',', '') + '[' + eventType + ']'  
FROM CRMPROD_01.dbo.P_Events_Table_Types
DECLARE @query VARCHAR(max)  
  
SET @query = '  
SELECT *  
FROM (  
        select	pd.dealId, 
				pett.eventType, 
				pet.eventData  
			from		CRMPROD_01.dbo.P_Deals pd
			cross join	CRMPROD_01.dbo.P_Events_Table_Types pett
			left join	CRMPROD_01.dbo.P_Events_Table pet
						on pet.dealId = pd.dealId 
						and pet.eventTypeID = pett.eventTypeID  
			where pett.active = ''true'' and pd.dealID = ''' + ISNull(str(@dealID),'NULL') + ''' 
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  
EXECUTE(@query)

Open in new window

Comment
Watch Question

View Solutions Only

cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
change

DECLARE @dealID int

to

DECLARE @dealID varchar(max)
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Also change this line

                  where pett.active = ''true'' and pd.dealID = ''' + ISNull(str(@dealID),'NULL') + '''

to

                  where pett.active = ''true'' ' + ISNull(' and pd.dealID = ''' + @dealID + '''', '')
AneeshDatabase Consultant
Top Expert 2009

Commented:
DECLARE @dealID VARCHAR(3000)
set @dealID = NULL
DECLARE @columns VARCHAR(max)  
SELECT @columns = COALESCE(@columns + ',', '') + '[' + eventType + ']'  
FROM CRMPROD_01.dbo.P_Events_Table_Types
DECLARE @query VARCHAR(max)  
 
SET @query = '  
SELECT *  
FROM (  
        select      pd.dealId,
                        pett.eventType,
                        pet.eventData  
                  from            CRMPROD_01.dbo.P_Deals pd
                  cross join      CRMPROD_01.dbo.P_Events_Table_Types pett
                  left join      CRMPROD_01.dbo.P_Events_Table pet
                                    on pet.dealId = pd.dealId
                                    and pet.eventTypeID = pett.eventTypeID  
                  where pett.active = ''true'' and pd.dealID IN ''' + ISNull(str(@dealID),-9999999) + '''
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  
EXECUTE(@query)
SinghAmandeep

Commented:
Use this :

use portal
go
DECLARE @dealID int
set @dealID = NULL
DECLARE @columns VARCHAR(max)  
SELECT @columns = COALESCE(@columns + ',', '') + '[' + eventType + ']'  
FROM CRMPROD_01.dbo.P_Events_Table_Types
DECLARE @query VARCHAR(max)  
 
SET @query = '  
SELECT *  
FROM (  
        select      pd.dealId,
                        pett.eventType,
                        pet.eventData  
                  from            CRMPROD_01.dbo.P_Deals pd
                  cross join      CRMPROD_01.dbo.P_Events_Table_Types pett
                  left join      CRMPROD_01.dbo.P_Events_Table pet
                                    on pet.dealId = pd.dealId
                                    and pet.eventTypeID = pett.eventTypeID  
                  where pett.active = ''true'' and pd.dealID in ''' + ISNull(str(@dealID),'NULL') + '''
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  
EXECUTE(@query)
SinghAmandeep

Commented:
Also change @dealID to nvarchar(2000)
Larry Bristersr. Developer

Author

Commented:
cyberkiwi: Got this error:
\
 aneeshatengail" Got this error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '-9999999'.
SinghAmandeep: Got this error

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'NULL'.
 
Larry Bristersr. Developer

Author

Commented:
cyberkiwi...your error

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 27
Unclosed quotation mark after the character string '
EXECUTE(@query)
'.
AneeshDatabase Consultant
Top Expert 2009

Commented:
SET @query = '  
SELECT *  
FROM (  
        select      pd.dealId,
                        pett.eventType,
                        pet.eventData  
                  from            CRMPROD_01.dbo.P_Deals pd
                  cross join      CRMPROD_01.dbo.P_Events_Table_Types pett
                  left join      CRMPROD_01.dbo.P_Events_Table pet
                                    on pet.dealId = pd.dealId
                                    and pet.eventTypeID = pett.eventTypeID  
                  where pett.active = ''true'' and pd.dealID IN (' + ISNull((@dealID),-999999) + ')
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  


Aneesh
AneeshDatabase Consultant
Top Expert 2009
Commented:
or like this

SET @query = '  
SELECT *  
FROM (  
        select      pd.dealId,
                        pett.eventType,
                        pet.eventData  
                  from            CRMPROD_01.dbo.P_Deals pd
                  cross join      CRMPROD_01.dbo.P_Events_Table_Types pett
                  left join      CRMPROD_01.dbo.P_Events_Table pet
                                    on pet.dealId = pd.dealId
                                    and pet.eventTypeID = pett.eventTypeID  
                  where pett.active = ''true'' and pd.dealID IN (' + ISNull(@dealID,'null') + ')
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  
Larry Bristersr. Developer

Author

Commented:
aneeshattingal:
Both of those work for '1' or '1,2,3,4,5'
But not for @dealID = NULL
Nor for @dealID = 'NULL'
Larry Bristersr. Developer

Author

Commented:
On @dealID = NULL I want all records returned
cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
One minor correction. I also added convert(varchar(max) around the eventType in case it is not a varchar
use portal
go
DECLARE @dealID varchar(max)
set @dealID = NULL
DECLARE @columns VARCHAR(max)  
SELECT @columns = COALESCE(@columns + ',', '') + '[' + convert(varchar(max),eventType) + ']'  
FROM CRMPROD_01.dbo.P_Events_Table_Types
DECLARE @query VARCHAR(max)  
  
SET @query = '  
SELECT *  
FROM (  
        select	pd.dealId, 
				pett.eventType, 
				pet.eventData  
			from		CRMPROD_01.dbo.P_Deals pd
			cross join	CRMPROD_01.dbo.P_Events_Table_Types pett
			left join	CRMPROD_01.dbo.P_Events_Table pet
						on pet.dealId = pd.dealId 
						and pet.eventTypeID = pett.eventTypeID  
                  where pett.active = ''true'' ' + ISNull(' and pd.dealID = ''' + @dealID + '''', '') + '
) Pv  
PIVOT  
(  
MAX(eventData)  
FOR [eventType]  
IN (' + @columns + ')  
)  
AS p'  
EXECUTE(@query)

Open in new window

Larry Bristersr. Developer

Author

Commented:
Hey everyone,
  I got this working using aneeshattingal and the last post from cyberkiwi:
Unless there are objections or a narrowing of the solution into a single select with no IF I would like to split points?

use portal  
go  
DECLARE @dealID varchar(max)  
set @dealID = NULL
DECLARE @columns VARCHAR(max)    
SELECT @columns = COALESCE(@columns + ',', '') + '[' + convert(varchar(max),eventType) + ']'    
FROM CRMPROD_01.dbo.P_Events_Table_Types  
DECLARE @query VARCHAR(max)    


If @dealID is null
begin
SET @query = '    
SELECT *    
FROM (    
        select  pd.dealId,   
                                pett.eventType,   
                                pet.eventData    
                        from            CRMPROD_01.dbo.P_Deals pd  
                        cross join      CRMPROD_01.dbo.P_Events_Table_Types pett  
                        left join       CRMPROD_01.dbo.P_Events_Table pet  
                                                on pet.dealId = pd.dealId   
                                                and pet.eventTypeID = pett.eventTypeID    
                  where pett.active = ''true'' ' + ISNull(' and pd.dealID = ''' + @dealID + '''', '') + '  
) Pv    
PIVOT    
(    
MAX(eventData)    
FOR [eventType]    
IN (' + @columns + ')    
)    
AS p'    
EXECUTE(@query)
End

Else
Begin
SET @query = '  
SELECT *  
FROM	(  
        select	pd.dealId, 
				pett.eventType, 
				pet.eventData  
		from	CRMPROD_01.dbo.P_Deals pd
					cross join	CRMPROD_01.dbo.P_Events_Table_Types pett
					left join	CRMPROD_01.dbo.P_Events_Table pet
						on pet.dealId = pd.dealId 
						and pet.eventTypeID = pett.eventTypeID  
		where pett.active = ''true'' and pd.dealID IN (' + ISNull(@dealID,'NULL') + ') 
		) Pv  
PIVOT  
		(  
		MAX(eventData)  
		FOR [eventType]  
		IN (' + @columns + ')  
		)  
AS p'   
EXECUTE(@query) 
End

Open in new window

cyberkiwi
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
I don't mind splitting the points, but your "NULL = show all" condition is catered for in my last comment without using the IF block and 2 almost identical SQL statements.