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


ADVERTISEMENT

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

How To Pass Table_name And Col_name As Parameters To Stored Procedure

Apr 30, 2004

HI,

I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
DECLARE FETCHER CURSOR SCROLL
FOR
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
OPEN FETCHER

WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FETCHER
CLOSE FETCHER


WHICH IS WORKING FINE WHEN I EXECUTE :-

EXEC MY_PROC

BUT I WANT TO PASS BOTH TABLE_NAMES AND COL_NAMES AS PARAMETERS :-

LIKE :-

EXEC MY_PROC [TABLE_NAME],[COL_NAME{1.....n}]

SO THAT I CAN CHANGE BOTH THE TABLE_NAME AND COL_NAME
HOW TO DO THIS ?

tHIS IS ONE MORE PROBLEM THAT I AM FACING.PLS HELP


tHANKS.

View 14 Replies View Related

Creating Stored Procedure With Temp Table - Pass 6 Parameters

Sep 9, 2014

I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:

ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as

Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table

Once I have created the Temp table following query I need to run

SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)

The above query might return null result or a date .

In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"

View 3 Replies View Related

How To Pass Values To A Stored Procedure Parameters By Using Script Task

Nov 27, 2007



Hi All,

I have One package that it contains one Execute SQL task in that i have placed a Stored procedure .
Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task "
How can i pass that table column values to that stores procedure thru using Script Task?

Regards,
Maruthi..

View 3 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:


CREATE PROC sp_test @in_company_code nvarchar(1024)
AS

select company_code, name, description
from member_company
where company_code in (@in_company_code)


However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?

Thanks!

View 2 Replies View Related

Multiple Parameters To A Stored Procedure

Sep 6, 2006

Hi All,

I have a database with very heavy volume of data.

I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

Thanks in advance,

HHA

View 4 Replies View Related

Integration Services :: Pass Multiple Parameter Values To SSIS Package In 2012 Through Stored Procedure?

Jul 9, 2015

we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..

Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .

1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??)
2.What are the options to pass multiple parameter values to ssis package through stored procedure.?

View 4 Replies View Related

Need Help In Understanding Multiple Parameters Sent To A Stored Procedure

Apr 12, 2006

OK, 1st, I have looked at every article that has come back on a "Stored Procedures" Search on this site, and am more confused than when I started looking for my answer.
This is what I need to do:
I need to pass a search sentence to a stored procedure, have the stored procedure break up the space delimited string and then do a "like" and "contains" in the WHERE statement, on what was sent to the stored procedure, and then return the results to a gridview for the person to select which item best answers their search.
I am just totally lost with using a stored procedure. I have done this in webmatrix when I coded it all into the aspx page, or into the codepage, but I have never done it with a stored procedure on the sql server, never sent a varible to a stored procedure... and am totlaly lost, or just do not understand how to do it.
Any help would be great.
Thanks in advance.
D4D

View 2 Replies View Related

How To Execute A Stored Procedure With Multiple Parameters In VB

Aug 3, 2012

Here is my stored procedure:

ALTER PROCEDURE dbo.SP_UpdateFixedRev
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@Number int,
@FixedRev money
)
AS
BEGIN
/* SET NOCOUNT ON */
Update Ticket set FixedRev = @FixedRev where Number = @Number;
End

Here is my code:

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand
dbConn.ConnectionString = connStr 'connStr is class-level vrbl
dbConn.Open()
dbComm = dbConn.CreateCommand
dbComm.Parameters.Add("@Number", OleDbType.Integer).Value = txtDatabaseTicketNo.Text
dbComm.Parameters.Add("@FixedRev", OleDbType.Currency).Value = txtFixedRev.Text
dbComm.CommandText = "SP_UpdateFixedRev"
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()

However its not updating my database when I run the app from a button click event.

View 7 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

What's The Best Way To Create A Stored Procedure That Queries With Multiple Parameters?

Nov 21, 2007

 
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search.  I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc).  How would I do it, or would I just be better off using several "IF" statements?  Thanks...
 CREATE PROCEDURE intranet_search_GetEmployeesBySearch
(
@FirstName NVarChar(100),
@LastName NVarChar(100),
@Phone NVarChar(50),
@Cell NVarChar(100),
@Pager NVarChar(100),
@Ext NVarChar(50),
@Email NVarChar(100),
@Department NVarChar(200),
@Position NVarChar(100),
@IsManager Bit
)
AS
BEGIN

SET NOCOUNT ON;




END
GO 

View 8 Replies View Related

Creating A Stored Procedure With Parameters And Multiple Sql-server Queries

Jan 23, 2008

I need to create a stored procedure that will have about 10-15 queries and take 3 parameters.
 the variables will be: @lastmonth, @curryear and @id
@lastmonth should inherit Session variable intlastmonth
@curryear should inherit Session variable intCurrYear
@id should inherit Session id
 One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "'  AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id")
The rest of the queries will be similar and use all 3 variables as well.
How can I go about this and how will queries be seperated.
 

View 2 Replies View Related

What Is Wrong With This Code? Selecting Data With Stored Procedure With Multiple Parameters

Sep 22, 2005

oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent)  'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.

View 2 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

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

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 Multiple Parameters Across Reports 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 summarized count link.


It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized 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.

View 3 Replies View Related

Sub: How To Pass Multiple Parameters Across Reports 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 summarized count link.



It is a sort of drilled down report. I am not getting how to pass the respective Ids (more

than one) to the next report when user clicks on the summarized 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.

View 1 Replies View Related

Analysis :: How To Pass Apply SSRS Parameters On Multiple Dimension Attributes

Oct 13, 2015

How @StartDate and @EndDate parameters must added to the MDX query for usage in SSRS data set. strtomember can be used like

SELECT (
strtomember(@StartDate) : strtomember(@EndDate)
) ON COLUMNS FROM [Cube]

How can i specify that sub-select must work on the [Fact A] and [Fact B] rundate? strtomember(@StartDate) does not specify on which attribute this sub select is going to work. Any pointers ?

FROM
(
SELECT
(
[Fact B].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00]
) ON COLUMNS FROM
(
SELECT
( [Fact A].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM [Cube]
)
)

View 2 Replies View Related

SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?

Jan 21, 2014

On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.

The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.

For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.

I changed the procdure to do nothing (return 1 in first line).

So with all parameters set from

command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end

it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.

When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)

SP:StmtCompleted -- Encrypted Text.

As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.

View 5 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

How To Pass A Variable To The Stored Procedure?

Feb 22, 2007

Hi,
i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3)
My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens:
comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details
Maybe is my stored procedure wrong?
Thanks
T.
Here is it:----------
ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN
SET @iLoopNumber = 1
SET @amountr
While (@iLoopNumber <= @amount)
BEGIN
INSERT INTO table(field1,...)
VALUES (....))
 SET @iLoopNumber = @iLoopNumber +1
End
COMMIT TRAN
 

View 3 Replies View Related

Can't Pass 0 In Stored Procedure Parameter

Sep 19, 2005

Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
    cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
    cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If

If  the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.

View 1 Replies View Related

How To Pass Xml File To A Stored Procedure

Nov 30, 2005

hi,i am passing a xml file name to the stored procedure.   the SP  parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.

View 1 Replies View Related

How Do You Pass A Parameter To A Stored Procedure

Mar 9, 2006

How can I pass a parameter to a stored procedure using Visual Web Developer 2005?  I have created a SQLDataSource that calls the SP. 
Thanks
--R

View 1 Replies View Related

How Array Will Pass To Stored Procedure

May 7, 2001

I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.

View 3 Replies View Related

How Do I Use Pass Data To/from Stored Procedure

May 2, 2006

Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob

View 4 Replies View Related

Is It Possible To Pass Array To Stored Procedure

Jan 9, 2007

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me



thanks

View 3 Replies View Related

VB.NET Stored Procedure, Can't Pass Param

Jun 18, 2007

Hello,



I have a VB.NET stored procedure as below:




Code Snippet

Partial Public Class StoredProcedures

Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)

'here I perform update statement using "paramInOut" passed form calling code

.......

'then I return value to the calling code

paramInOut = 555

End Sub

End Class



Calling code specifies a parameter like this:






Code Snippet

Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)

param.Direction = ParameterDirection.InputOutput

param.Value = 999

cmd.Parameters.Add(param)





When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?

Any help would be appreciated.

Thanks a lot,Fly.

View 4 Replies View Related

Pass XML To Stored Procedure In SQL Server2000

Feb 1, 2008



Hi,
I want to pass an xml file to stored procedure in SQL Server 2000 as a parameter.
Can we pass the xml file path as a parameter? if not then how can it be done.
I want to serialize data to XML and then pass it to a procedure to insert data.

Kindly help ASAP.
Thanks

View 4 Replies View Related

How To Pass DateTime To A Stored Procedure

May 16, 2008

When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".





Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();

SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs

if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}




I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').

View 3 Replies View Related







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