[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Passing a parameter to sp_CrossTab

Posted on 2009-05-18
4
Medium Priority
?
1,297 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
  • 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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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