?
Solved

Passing a parameter to sp_CrossTab

Posted on 2009-05-18
4
Medium Priority
?
1,279 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:Bleary-Eyed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:bljak
ID: 24413526
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
 

Author Comment

by:Bleary-Eyed
ID: 24413579
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
 
LVL 4

Accepted Solution

by:
bljak earned 2000 total points
ID: 24414349
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
 

Author Closing Comment

by:Bleary-Eyed
ID: 31582646
That did it.

Thanks
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question