SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General Tab:

Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;

Parameter Mapping:

Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10

Variables:

location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:

Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.

I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.

View 6 Replies


ADVERTISEMENT

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

SQL Reporting Services Issue Using Multiple Parameters Where Data Is Passed In From A Stored Procedure

Apr 15, 2008



I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure



When running the report in design mode - I can type in a parameter sting and it runs fine



In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine



as soon as I tick more than one I get an error



An error occurred during local report processing

Query execution failed for data set €˜data'

Must declare the scalar variable '@parameter'



Some info...



The dataset 'workshop' is using a sproc to return the data string?

I get multiple values back fine in the sproc using this piece of code

(select [str] from iter_charlist_to_table( @Parameter, DEFAULT) ))



I have report parameters set to Multi-Value

Looking through the online books it says...



You can define a multivalued parameter for any report parameter that you create.

However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.

Am I trying to do the impossible ?

View 1 Replies View Related

Dyanamically Passing Input Parameters To Stored Procedure By Using SSIS

May 14, 2008

Hi,

I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
ex:
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert

Uniq_Id, emp_ass_id, start_date end_date status_id





1099

1001

1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1








1100

1001

1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1








1101

1001

1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1








1102

1001

1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1








1103

1001

1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1






the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.

Thanks in advance.

View 9 Replies View Related

Optional Parameters Passed To Stored Procedure

Jul 23, 2005

In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

SQL 2012 :: SSIS - Execute Stored Procedure With Parameters Does Not Work

Jun 9, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True

When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.

SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;

However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.

I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.

Stored procedure variables:

ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

Here is my set up, what is wrong here:

I Created these Variables:

Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store

I added these parameter mappings in the Execute SQL Task

Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1

I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,

It runs the package successfully but it does not put the data into the table.

View 2 Replies View Related

SQL Server 2012 :: Can Excel Be Passed As Stored Procedure Parameter

Sep 29, 2015

Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?

View 1 Replies View Related

SQL Server 2012 :: Avoiding Temp Table With String Of IDs Passed Into Stored Procedure

Sep 26, 2014

Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.

Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] .....

The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:

CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[Code] ....

The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?

View 9 Replies View Related

Passing Parameters Into Stored Procedure

Apr 11, 2007

I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors.  How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server"            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink"            SelectCommandType="StoredProcedure">            <SelectParameters>                        <asp:Parameter Name="Zip" Type="string" />             <asp:Parameter Name="MenuCode" Type="double" />                               </SelectParameters>
 

View 1 Replies View Related

Passing Parameters To A Stored Procedure

Mar 8, 2004

Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)

The value I'm passing (@MyLSN) is 24/12359 (one LS Number)

The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:

CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)

AS

declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))

The value I'm passing (@MyLSN) is '24/12359','24/12360'

This is where the problem begins because the stored procedure now doesn't return a recordset.

If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.

So if someone could point me in the right direction then that would be much appreiciated.

Thanks in advance.

View 9 Replies View Related

Passing Parameters In A Stored Procedure

Sep 19, 2013

I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.

It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?

EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 11 Replies View Related

Passing Parameters To A Stored Procedure

Nov 22, 2006

Hey people,

I am trying to pass a parameter to a stored procedure based on a select statement. Basically it's something like this (in my head :P ):

foreach PersonID in (SELECT id FROM person)
{
MyStoredProcedure(PersonID);
}

Multiple rows will be returned from the select statement.

The code above will be in another stored procedure (not that code but an equivalent in SQL). Is there something like a PLSQL in SQL Server 2000? How can I translate that into a stored procedure for SQL?

Thanks to you all!

View 1 Replies View Related

Passing Parameters To Sqldatasource Stored Procedure

Aug 22, 2006

Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a  stored procedure.
Thanks, M.

View 4 Replies View Related

Running A Stored Procedure Without Passing Parameters

Feb 14, 2007

HI all, I'd like to run a simple stored procedure on the Event of a button click,  for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks. 

View 6 Replies View Related

Passing Two Or More Parameters To An Update Stored Procedure From VB6 Using ADO

May 26, 1999

I am trying to pass two parameters to a stored procedure using the code below:

Dim cmd As New Command
Dim rs As New Recordset
Dim prm As New Parameter
Dim ttt As New Parameter

cmd.ActiveConnection = cnn1
cmd.CommandText = "{call usp_PlcwithinUID_upt(?,?)}"
cmd.CommandType = adCmdStoredProc

Set prm = New Parameter
prm.Type = adInteger
prm.Value = gsPlcCode
cmd.Parameters.Append prm

Set ttt = New Parameter
ttt.Type = adVarChar
ttt.Value = "TEST"
cmd.Parameters.Append ttt

'cmd(1) = gsPlcCode
'cmd(2) = "Test"

Set rs = cmd.Execute

It works when passing one but NOT two ?? Can anyone shed any light ??
I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?

The stored procedure just performs an update on a table.

Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.

Any clues as to how to get an update stored procedure to except multiple variables.

I have trawled through loads of documentation to no avail.

Please help.

Paul

View 1 Replies View Related

Passing SQL Clauses As Parameters To A Stored Procedure

Feb 26, 2004

Hi,

I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.

I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as

"WHERE lastname LIKE 'A%' "

"ORDER BY lastname "

I need to assign the entire clause because the query may or may not use a particular clause.

I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?

Thanks in advance for your opinion, suggestion, criticism, etc.

--Tom.

View 2 Replies View Related

Passing Different Number Of Parameters To A Stored Procedure

Jan 22, 2007

Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass 6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

View 1 Replies View Related

Passing Parameters To MySQL From A Stored Procedure

Dec 22, 2007



I am trying to pass in parameters from MSSQL Store procedure to a MySql database but it doesn't take the parameters? Any idea how to achieve this? The query works if I pass in the actual dates.

Create procedure [dbo].[MY_STORED_PROC]

@STime datetime,
@ETime datetime

AS

select *
into #MyTempTable
from openQuery(MYSQL, '
SELECT a.* FROM mytable a
where a.createdate between @STime and @ETime
')

View 2 Replies View Related

Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter

Mar 28, 2007

Hello,
I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related.
The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated)
When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
 
Code:
.
.
<td style="width: 3px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"
Width="533px">
<Columns>
<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"
SortExpression="LogId" />
<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />
<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />
<asp:TemplateField>
<ItemTemplate>
<span>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>
</ItemTemplate>
</asp:TemplateField>
 
</Columns>
<HeaderStyle BackColor="#608FC8" />
<AlternatingRowStyle BackColor="#FFFFC0" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"
SelectCommand="spViewLog" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Stored Procedure:
ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )
 
AS
SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')
RETURN
 
I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.
 Any guidance would be much appreciated.
Thank you
Lee
 
 

View 2 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View 2 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View 7 Replies View Related

Stored Procedure - Passing Date Parameters Failed

Aug 4, 2015

I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.

The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.

The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.

Here is a portion of the stored procedure:

alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)

In Excel VBA, the code passing the parameters are:

cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)

where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.

The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.

View 5 Replies View Related

Passing Parameters To A Stored Procedure In Visual Basic

Jan 5, 2007

Hi peeps,

I need some help with passing parameters to a stored procedure from my visual basic code.

Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.

I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.

The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.

The code for the procedure is listed below:


/*

Name: usp_display_all_users

Description: Displays activeuser, personid, comment from table: pswds

Userid and sort from table: people

Where the username is like the parameter supplied.

Both tables joined on personid

Author: Iain Blackwood

Modification log: Change

Description Date Changed by

Created proc 02/01/07 Iain Blackwood

*/

ALTER PROCEDURE usp_display_all_users

(

@searchStr nvarchar(128) =''

)

AS

SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment

FROM dbo.pswds INNER JOIN

dbo.people ON dbo.pswds.personid = dbo.people.personid

WHERE (dbo.people.sort LIKE @searchStr + '%')

ORDER BY dbo.people.sort

 

The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.

I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.

Private Sub fillDataGrid()


' I NEED TO:

' 1: Fill the data set with all Accounts

' 2: Diplay the Data to the data grid

' delcare a new SQL connection

sqlCon = New SqlConnection(conStr)

' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE

Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"

comStrPeople += " FROM pswds INNER JOIN"

comStrPeople += " people ON pswds.personid = people.personid"

comStrPeople += " ORDER BY sort"

' Display the command string: TEMPOARY

testlbl2.Text = comStrPeople

' Declare a new SQL data adapter

sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)

Try


' Declare a new dataset

sqlDataSet = New DataSet

' fill the sql data adapter with data from dataset: called PeoplePswds

sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")

' Fill the forms datagrid view with data from the Dataset table PeoplePswds

DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView

Catch ex As Exception


' Display suitable error message

MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)

End Try

End Sub

 

I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.

Thanx Flakkie

View 6 Replies View Related

Passing Parameters Read From An Ini File To A Stored Procedure Within DTS Package

Sep 24, 2002

Hi

I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:

[DatabaseCleaner]
! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd>
! -- <N> must be a successive number starting from 1 ...
TableToBeCleaned_1=Transactions,24,24
TableToBeCleaned_2=Payments,24,24
TableToBeCleaned_3=PresenceTickets,24,24

As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.

How can I do that? Any idea?

Thanks

mipo

View 1 Replies View Related

Problem Passing Variables As Parameters To Extended Stored Procedure

Jun 20, 2007

Hello all,



I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:



C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)



srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '';



srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '';



srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '';



sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);





Calling the XP with literal values:



EXEC xp_mytest 'one','two','three'



Output:

Params received by xp: one, two, three





Calling XP via a stored procedure:



create procedure sp_mytest

(

@myvar1 nvarchar(200),

@myvar2 nvarchar(50),

@myvar3 nvarchar(50)

)

as BEGIN

PRINT @myvar1

PRINT @myvar2

PRINT @myvar3

EXEC xp_mytest @myvar1, @myvar2, @myvar3

END



EXEC sp_mytest 'one','two','three'



Output:

one

two

three

Params received by xp: o,t,t





Any insight or assistance is greatly appreciated!!!

View 1 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

Report Based On Optional Parameters From Stored Procedure

Jan 12, 2008



I have the following stored procedure:



Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC





What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.


If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.


Any ideas?

View 12 Replies View Related

SQL Server 2012 :: Passing Sum To Stored Procedure

Sep 21, 2015

Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error

exec mysp
@param = (@var1 + @var2)

View 1 Replies View Related

SQL Server 2012 :: Get ID From Lookup Table Based On Value Passed To Function

Dec 19, 2014

I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.

I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.

If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.

The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like

colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred

So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.

View 2 Replies View Related

SQL Server 2012 :: Passing Date Parameter To Stored Procedure?

Oct 9, 2014

I am trying to pass a date parameter to a stored procedure.

I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.

Basically, this works:

DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )

But this returns an error:

DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )

What am I doing wrong?

View 8 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

Passing SSIS Package Variable To Stored Procedure As Parameter

Feb 25, 2008



I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax

in Execute SQL Task Editor I have
conn type -- ole db
connection -- some server
sql source type -- direct input
sql statement -- exec testStoredProc @timeStamp = ?

if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008'

This is the syntax I found to execute the procedure, I don't udnerstand few things about it.

1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters.

2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime

3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1.

Thank you, please help.

View 2 Replies View Related

SQL Server 2012 :: Stored Procedure With One Or More Input Parameters?

Dec 17, 2013

I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.

What would be the most proficient way of achieving this?

I had initially looked at using IF, TRY ie:

IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY

However I'm not sure this is the most efficient way to handle this.

View 2 Replies View Related







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