How To Pass Parameters In SSIS Dataflow

Apr 12, 2006

I am using a parameterized select query as displayed below to fetch values from source.

SELECT A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd,
SUM(S.Sales) AS Monthly_gross_MC_VI_amt,
SUM(S.Sales) - SUM(S.[Returns]) AS Monthly_net_MC_VI_amt,
SUM(S.SaleTxns) AS Monthly_gross_MC_VI_tran_cnt,
SUM(S.SaleTxns) - SUM(S.ReturnTxns) AS Monthly_net_MC_VI_tran_cnt
FROM Account AS A
LEFT OUTER JOIN dbKAIExtract.dbo.tblSales_STG AS S
ON A.Account_No = S.AccountNo
And S.BucketNo = ? And S.ProductCode in ('01','02')
LEFT OUTER JOIN Merchant AS M
ON A.Account_No = M.Account_no
INNER JOIN SalesRep AS SR
ON SR.Rep_SSN = isnull(A.rep_SSN,'000000000')
INNER JOIN Household AS H
ON A.Account_No = H.Account_no
LEFT OUTER JOIN BankAU AS B
ON A.Assigned_AU = B.AU_No
LEFT OUTER JOIN SalesProduct AS SP
ON A.Account_No = SP.Account_no
And SP.Reporting_Interval_Id = ?
LEFT OUTER JOIN Entitlement AS E
ON E.Account_no = A.Account_No
AND SP.Product_Cd = E.Entitlement_Card_Type
LEFT OUTER JOIN Income AS I
ON I.Account_no = A.Account_No
And I.Reporting_Interval_Id = ?
LEFT OUTER JOIN Expense AS Exp
ON Exp.Account_no = A.Account_No
And EXP.Reporting_Interval_Id = ?
LEFT OUTER JOIN Product AS P
ON P.Product_cd = SP.Product_cd
WHERE (A.current_ind = 1)
AND (SR.current_ind = 1)
GROUP BY A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd

My problem is, I am not able to assign any variables to parameterized query. Can any body guide how to assign respective variables to the parameterized query. I have the above query as a part of OLE DB Source step within Data flow task.





Thank you

Jatin

View 11 Replies


ADVERTISEMENT

Pass Parameters Into SSIS Package From A Web Application

Nov 13, 2007

Hi all,
 I wish to pass parameter from a dropdownlist in my web application to the SSIS package so as to export the user specified table.
Is using package configuration the right way to do this?
Any advice is appreciated!
 

View 8 Replies View Related

Integration Services :: Pass Parameters To SSIS Execute Process Task?

Nov 3, 2015

Got a powershell script to split a large XML file to split in smaller chunks. I have Execute ProcessTask in SSIS with: 

Executable: %windir%system32WindowsPowerShellv1.0powershell.exe

argument: -ExecutionPolicy ByPass -command ". 'C:WorkspacesSplitToytPMFile.ps1'"

I need to pass File Name as parameter to the PS script. I tried using the StandardInputVariable but it doesn't work. 

View 11 Replies View Related

Pass A Variable To A DataReader In A DataFlow Task

Feb 13, 2007

How can I pass a variable to a DataReader in a DataFlow task?

My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'

The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.

View 4 Replies View Related

How To Pass Pass The Parameter In SQL Command In SSIS Package

Jul 31, 2006

Hi

   We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

 

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )



 

Query 2: (look up )

 


SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')  AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')



 

please anyone give the sample control flow  and how to pass the parameter?

 

Thanks & regards

Jeyakumar.M

 

View 1 Replies View Related

How To Pass More Than 30 Parameters

Dec 1, 2007

hi,
I am new to sql server reporting services. When i pass more than 30 parameters ,my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.

can any one tell me how to pass more no of parameters to the Report.

Advance thanks

Srinivas Govada

View 1 Replies View Related

How To Pass A SP Parameters W/o Calling The Sp?

Dec 1, 2003

MY DBA wrote a trigger, that upon hitting the delete button for a payment,
the payment would be transferred to another table and the trigger would
call a sp as well. since the trigger is calling the sp, I should only supply the parameters.

How do you do that w/o calling the procedure?

View 1 Replies View Related

How To Pass OLAP Parameters Within A URL?

Nov 27, 2007

Hi there!




My report uses an OLAP query with a (multi-valued) parameter, the generated MDX query contains something like "...ON ROWS FROM ( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".




The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]




But I cannot get Reporting Services to accept the parameter via URL access (instead of complaining about a missing parameter value). I've already tried quoting/encoding the brackets, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.


Normal (url-encoded) query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. I think ReportServer doesn't like the way I'm trying to quote them...


How do I quote dimension parameters correctly?

Thanks and best regards,
Thomas

View 3 Replies View Related

How To Pass Parameters To Subreport

Dec 26, 2007

Hi Gurus,
I want to pass 0 value to all subreport parameters in the beginning when main report is run. This is because i dont want subreport query to run.

when user clicks on '+' in main report, i want to pass the parameter values so that the subreport will run with correct parameters for that particular main report row.

My purpose of doing this is to achieve speed with subreport


Thanks a lot in advance.

Respectfuly,
Korr

View 18 Replies View Related

How To Pass Parameters To Sub Reports?

May 8, 2008



how do u pass parameters to a subreport.... i tried doin it but got an error msg sayin "Sub report cannot be displayed"
any solution????

View 4 Replies View Related

Not Able To Pass UserID Parameters

Mar 27, 2008

I have recently had a few reports that are built around the userid parameter start failing to pass the userid parameter. If I run the report and hard code the userid everything runs just fine, but I am not able to collect the userid when the user is logging into the report server. Any thoughts or suggestions?


R/ AA

View 3 Replies View Related

SqlDataSource Pass Parameters From Code-behind

Jul 15, 2007

how do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
 
  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.

View 3 Replies View Related

Pass Parameters Into Stored Procedure?

Aug 11, 2007

How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks

View 2 Replies View Related

How Do I Pass VB Variables To The Asp Parameters Of An SqlDataSource?

Feb 29, 2008

I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.

 

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"

            OldValuesParameterFormatString="original_{0}"


            SelectCommand="sp_diplomaViewQualifications"


            UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"

            DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"

            SelectCommandType="StoredProcedure">

            <UpdateParameters>

                <asp:Parameter Name="original_qualId"
/>

                <asp:Parameter Name="quantity"
/>

            </UpdateParameters>

            <DeleteParameters>

                <asp:Parameter Name="original_qualId"
/>

                <asp:Parameter Name="createdBy"  DefaultValue="TEST"/>

            </DeleteParameters>

            <SelectParameters>

                <asp:Parameter Name="masterKey"
/>

            </SelectParameters>

        </asp:SqlDataSource>

 

My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.

 

This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)

How do I pass this into the update and/or delete part
of the command field on the ASP page? 

View 3 Replies View Related

Pass Parameters To A Query Using Subquery?

Apr 23, 2012

I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)

Example

table1
id - autonumeric
id_user - id from user
dates - date of register

table2
id - user id
name - user name

query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;

subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;

This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?

View 3 Replies View Related

How To Pass Parameters To Execute SQL Tasks?

Jun 11, 2008

Hi,
I have a table with queries. I need to execute those queries and pass results into a variable. Then use that variable/result to execute other queries to make business decisions.
EXAMPLE:
TASK#1
TABLE-A :has queries below
select count(*) from employee
select count(* ) from mangers

I want to execute those queries and store results in @counts. How I execute all queries in table A and pass that to a variable?

TASK#2
Then I have another SQL task(may be SQL task) which use the value @counts make some decisions
If @count > 1 then pass
If @count <1 then fail

How can I do that?

I am still new to SSIS and not very familiar with variables. Any advice would be appreciated.

View 6 Replies View Related

Dynamic Crosstab - How To Pass Parameters

Nov 28, 2013

I am trying to use one sql store procedure but don't know how to pass the parameters. I am posting here my store procedure:-

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)

[Code] ....

My table looks like below:

emp_id, time_code, date, time_charged
RB, VAC, 20130222, 8
RB, HOL, 20131128, 8
RB, VAC, 20130311, 8

My output should be:

emp id VAC HOL
RB 16 8

View 5 Replies View Related

How To Pass Parameters From A Matrix Report?

Aug 17, 2007

In a matrix report with column and row groupings, how to pass the parameters to the drill-through report? The column grouping could open up into multiple columns and the same to the row groupings. I have a navigation link to a drill-through report on a total field in the matrix report and the drill-through report is required three parameters, one from the parameter from the matrix report (this one I can map to easily), second parameter is one of the value from the column grouping field and the third parameter is one of the value from the row grouping field. How to select those values from the column/row grouping.

Thanks

View 8 Replies View Related

Links That Pass Parameters To Other Reports?

Dec 27, 2007



How do you create a link on one report to run another report passing a parameter (or two).

View 4 Replies View Related

How To Pass A Long Parameters To The Datasource?

Apr 5, 2007

I defined the following stored Procedure as the datasource



CREATE PROCEDURE p1(@cond String)
AS
BEGIN
SELECT Id,Name from table1 Where @cond
END



@cond is a long sql , for example



Id in (select sunId from table2 where .......)



my question is

how can I pass the parameters( @cond ) to RS2005?



thanks!





View 1 Replies View Related

How To Pass Multiple Parameters In SSRS

Apr 25, 2008


Hi,

I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.

Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.

Please help.

Regards,
Sachin

View 4 Replies View Related

How To Pass Parameters To IE From SQL Reporting Server

Aug 27, 2007

I will like to configure the Report or the Report server to pass parameters to Inetrnet Explorer when creating the report.
Is there a way to do that ?

View 1 Replies View Related

How To Pass Different Parameters To Same Child Package.

May 8, 2008

Hi,

I'm having Parent package, which has For each loop which returns Val1 , Val2 , Val3 and Val 4

I want to create child package which accepts one parameter and call this package inside For each loop.
Inside loop i want to call 4 child packages(same pakage) simultaneously.

If i create child package which access parent package variable, i will have to modify modify each child package. I cannot reused child package.


How can i create a generic child package ?



Thanks,
Sandeep.

View 6 Replies View Related

Possible Pass Parameters On Jump To URL Properties?

Jun 22, 2006

Hi,

I use the " JUMP to URL" properties and the code as follow:

=SWITCH(Fields!KpiCode.Value=Fields!KpiCode.Value,void(window.open('"+First(Fields!ServerString.Value,
"HostInfo")+"/L3/"+Fields!KpiCode.Value+".aspx','"+First(Fields!PropertyString.Value,
"HostInfo")+"'))")

I want to know is any way or any possible way could pass the report
parameter on this kind of porperties or this code?
For some requirement, I just can use Jump to URL, but hope can pass the
parameters on "jump to url" properties.

Thanks for any advice!
Angi

View 13 Replies View Related

Help With CustomComponent In SSIS-DataFlow

Mar 3, 2006

Hello
Trying to figure out a clever solution for splitting multivalued columns out into n-columns.
For that I've build a custom component in SSIS using

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/4dc0f631-8fd6-4007-b573-ca67f58ca068.htm as an example.

I need to be able to add columns to the OutputCollection in designtime, but the designer returns an error:
Error at Data Flow Task [Uppercase [5910]]: The component "Uppercase" (5910)
does not allow setting output column datatype properties.

How do I enable the designer to accept designtime changes in the
columncollection?

Kind regards

View 1 Replies View Related

SSIS Dataflow Performance

Feb 19, 2008



I created a dataflow that transferred about 1 million records from a SQL database on one server to a differend SQL database on the same server. The processing took about 30 minutes. I used the Fast Load option.

I then created a "Execute SQL Task" and wrote a "SELECT * INTO TABLE" and this processing took about 30 - 60 seconds.

Can someone tell me why creating a Data Flow Tak would take so much longer or give differences between the two options above? Can someone give some pointers on how to make a Data Flow task more efficient?

Thanks.

View 11 Replies View Related

SSIS Dataflow Vs SQL2K DDQ

Mar 24, 2006

I am trying to recode a SQL2K DDQ into an SSIS Dataflow. I have no issue recoding it in SSIS except I am not sure that I am doing it correct way.

My DDQ has source,destination, update/delete/insert statements and few lookups. I have used activex script for the transformation because I have atleasst 10 If conditions. For every "if condition" the destination columns are popuated with different lookups/source columns and constant values.

Now When I start doing it with SSIS I have to use at least 10 Conditional split. and then at least one lookup,one OLEDBCommand and one OLEDB Destination for each of them . that brings my count of DF objects to 30-40

It makes my data flow to complex with two many objects. Earleir I could do whole of this in one sngle DDQ. It makes me think if I am doing it the correct way. should I be using Activex Script Task to these kind of activity.



Any advice would be appreciated.



Cheers,

siaj

View 7 Replies View Related

HELP--SSIS Dataflow Task

Nov 21, 2006

Need help regarding ssis dataflow task

I need to create a ssis package. I want to import the data from a flat file to a table.

Lets say, the table has 5 columns -- col1, col2, col3, col4 , col5.(Assume that all columns can be NULLABLE) The datafile contains the data related to only three columns say col1, col2, col3. So when I use dataflow task to import the data from the file to the table, I will only get three columns, col1, col2, col3. Columns col4, col5 will be NULL.
However, I want to populate columns col4, col5 with some values which are stored in the variable.

IS there any way to do this??

Any help would be appreciated.

Thanks

View 3 Replies View Related

SSIS Dataflow Designer Bug

Jul 19, 2006

When I drop a new component onto the design surface, it appears with dotted lines around it, as it is selected.

But, the F2 key (the hotkey for rename) does not work.

I have to click on some other component, and then back on the new component, and then the F2 key works.

I remember reporting this bug back in the beta cycle, but it is still present even in the release -- I actually think Ã?'m using the SP1 version of 2005, but see version info below to be sure:


Remote terminal services broke my copy buffer again, as is so often does, so pasting in the version info failed -- falling back to manual typing -- SSIS 9.00.2047.00.

View 2 Replies View Related

SSIS Dataflow Problem

Jul 19, 2006

It just occurs to me that it might not be a bad idea to post again the problems in SSIS that I found back in the beta cycle which do not seem to have been fixed, to increase the chance that someone will notice them.

(During the beta cycle, I spent hours trying to get bugs posted into the beta bug system, but the beta web bug site had so many problems that I could never get it to work, unfortunately, so I fell back to just posting my bugs and hoping some developers would notice them. Kirk had a thread for bugs and RFEs at one point, where I posted some of the important ones.)

Here is another fairly bad one:


The dialog that pops up to show fields in the dataflow which have been orphaned, after some fields have been removed earlier in the dataflow, and which allows the user to rename them or delete them, has a very bad display problem:

It shows the box names before the field names, so if the box names are descriptive, the field names are far off-screeen, and it is very difficult to get them to display on screen.

So the user starts off with all the items in the drop-down being indistinguishable, as they all start with the box names, and the important part of the strings are far off-screen at the right -- the actual field names.


I don't recall if I actually ever posted this one; I may not have.

View 1 Replies View Related

SSIS Dataflow Help! Job Succes Without Doing Anything

Sep 13, 2007

Okay I 'm not sure what to do next
(S2K5 64 bit SP2)
Migrated across simple DTS which picks data up from MSaccess and ships it into a database table!

I switched the 64bit run time to false and happy days it shipped the information across.

I then decided I wanted to deploy the package so I created a deployconfig.xml with all properties ticked against my two connections in connection manager. ( the MSacessdb and the S2k5 database )

after that I switched the deployment util to true.

then I fired the deployment manifest


Then created a job to schedule it fails with








Executed as user: SEA-SRV-00009SYSTEM. ...00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:09:33 PM Error: 2007-09-10 16:09:38.70 Code: 0xC0202009 Source: Trackitnew Connection manager "TRACKIT" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2007-09-10 16:09:38.72 Code: 0xC020801C Source: Data Flow Task Task Collection [22] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TRACKIT" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-09-10 16:09:38.72 Code: ... The package execution fa... The step failed.

SO firstly I changed the surface area config to enable xp_cmdshell

created another job but ran TSQL
xp_cmdshell 'dtexec /FILE "D:DTSTrackit.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW'

I ran the job and it says succesful but when I do a query on the table there is no data could anyone please point me in the right direction? Thanks






View 17 Replies View Related

How To Pass Parameters To Stored Procedure That Use SQLDataSpurce

Feb 19, 2008

I have stored procedure that expects 2 input parameters (@UserID uniqidentifier and @TeamID int). My datasource is SQLDataSource. So i need to pass parameters to SP..When i add parameters ans execute i got an error "....stored procedure  expects @TeamId parameter, which was not supplied" But i pass them. How should i pass parameters? Maybe the reason is some mismatching of parametrs.  1 Parameter [] param = new Parameter[2];
2
3 param[0] = new Parameter();
4 param[0].Name = "@TeamID";
5 param[0].Type = TypeCode.Int32;
6 param[0].Direction = ParameterDirection.Input;
7 param[0].DefaultValue = "1";
8
9 param[1] = new Parameter();
10 param[1].Name = "@UserID";
11 param[1].Direction = ParameterDirection.Input;
12 param[1].DefaultValue = "edf26fd8-d7cd-4b32-a18a-fc888cac63ef";
13 param[1].Type = TypeCode.String;
14
15 dataSource = new SqlDataSource();
16 dataSource.ID = "Source";
17 dataSource.ConnectionString = settings.ToString();
18
19 dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
20 dataSource.DataSourceMode = SqlDataSourceMode.DataReader;
21 dataSource.SelectCommand = "dbo.GetAprfAssessmentTeamData";
22
23
24 dataSource.SelectParameters.Add(param[0]);
25 dataSource.SelectParameters.Add(param[1]);
26
 

View 4 Replies View Related

Pass Multiple Parameters To Stored Procedure

Mar 26, 2008

Hi,
I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample,
CREATE PROCEDURE [dbo].[display_customer]
@agentID varchar(20), 
@customer1 varchar(20),
@customer2 varchar(20),
.....            -- Here I do know how many customers for each agent
AS
SELECT  name, city, state, zip
FROM rep_customer
WHERE agent = @agentID and (name = @customer1 or name = @customer2)
Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure?
Thanks a lot!
 

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved