Passing a parameter to sp_CrossTab

Hello Experts,

We're using the sp_CrossTab proc that is described here
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829_mem1,00.html

Here is how we have it set up.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[usp_PivotTable_PM_Cal_Detail_Schedule]
AS
SET NOCOUNT ON
Execute sp_crosstab
'SELECT * FROM tblFlexGridColVal',
'FlexGridName = ^ToolDetailActivity^ ',
'Activity|Performed By|Performed On|Results|Cert|Technician',
'FieldName',
'FieldValue',
'Max'


We need to pass a dynamic parmeter (@EventID) from a calling VB 6.0 (using ADO) client and use this @EventID as part of the WHERE clause portion of the main parameter passed into sp_CrossTab. For example, the intent is to acheive this:

ALTER PROCEDURE [dbo].[usp_PivotTable_PM_Cal_Detail_Schedule]

(
@EventID as nvarchar(50)
)

AS
SET NOCOUNT ON
Execute sp_crosstab
'SELECT * FROM tblFlexGridColVal',
'FlexGridName = ^ToolDetailActivity^  AND EventID='+ @EventID +',
'Activity|Performed By|Performed On|Results|Cert|Technician',
'FieldName',
'FieldValue',
'Max'


My question is, how do we embed this @EventID into the main parameter string and accomplish our objective?
Bleary-EyedAsked:
Who is Participating?
 
bljakConnect With a Mentor Commented:
How about putting everything inside as parameter?

declare @sp_param nvarchar(2000)
set @sp_param = 'SELECT * FROM tblFlexGridColVal'',
''FlexGridName = ^ToolDetailActivity^  AND EventID='+ @EventID =',
''Activity|Performed By|Performed On|Results|Cert|Technician'',
''FieldName'',
''FieldValue'',
''Max'''
 
 
Execute sp_crosstab @sp_param

Open in new window

0
 
bljakCommented:
maybe you can make whole param to sp_crosstab as variable having something like

declare @sp_param nvarchar(2000)
set @sp_param = 'FlexGridName = ^ToolDetailActivity^  AND EventID='+ @EventID
 
Execute sp_crosstab
'SELECT * FROM tblFlexGridColVal',
@sp_param,
'Activity|Performed By|Performed On|Results|Cert|Technician',
'FieldName',
'FieldValue',
'Max'

Open in new window

0
 
Bleary-EyedAuthor Commented:
Well, the way sp_CrossTab works is it takes one string paramter only, which is subsequently parsed (in sp_CrossTab) into several paramters. The paramter passed into sp_CrossTab is simply a string, delimited appropriatly to facilitate the internal parsing. So I don't think your suggestion would work.
0
 
Bleary-EyedAuthor Commented:
That did it.

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