.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Pass Multi-Value parameters to Stored Procedure problem

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server
 

Hello

I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

AS
BEGIN
SET NOCOUNT ON;
 
WITH cte AS
(
SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
),
cte2 as
(
SELECT [ID],[First Name],[Last Name] FROM Sta


View Complete Post


More Related Resource Links

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

  
The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

How do I call an SSRS Report from a stored procedure, and pass parameters?

  

Hi all,

I have a Report, that I want to run ad-hoc from a Stored Procedure. 

I want to render this report to PDF format, and save it to a drive.

The difference between this post and most threads I've seen is I don't want to run a stored proc within the report and send parameters to the stored proc.

I DO want to call a report FROM the stored proc, and send parameters TO the report.

How can I do this?

Many thanks,

Jason


Pass parameters into multi stored procedures

  

Hello,

I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :

 

        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

Pass parameters into multi stored procedures

  

Hello,

I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :

 

        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

how to pass dataset from one stored procedure to another stored procedure

  
I'm wondering how a dataset returned by a stored procedure can be passed to another stored procedure.     mark it as answer if it answered your question :)

problem for using webservice (sql parameter stored Procedure)

  
i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

SQL 2010 SP1 does not fill in Stored Procedure Parameters

  
I am executing a Stored Procedure in Server Management Studio.  The Procedure gives the expected results but it does not fill in the parameters?  I have stop and restarted the server to no avail. Please help? Thanks. STORED PROCEDURE USE   [char68003f] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectDonorXiD] ( @DonorID varchar(50) , @ContactTitle varchar(7)=null OUTPUT, @ContactFirstName varchar(40)=null OUTPUT, @ContactLastName varchar(40)=null OUTPUT, @ContactAddress1 varchar(40)=null OUTPUT, @ContactAddress2 varchar(40)=null OUTPUT, @ContactCity varchar(30)=null OUTPUT, @ContactStateCode varchar(2)=null OUTPUT, @ContactZipCode varchar(10)=null OUTPUT, @ContactPhone varchar(30)=null OUTPUT, @ContactEmail varchar(80)=null OUTPUT, @BillingTitle varchar(7)=null OUTPUT, @BillingFirstName varchar(40)=null OUTPUT, @BillingLastName varchar(40)=null OUTPUT, @BillingAddress1 varchar(40)=null OUTPUT, @BillingAddress2 varchar(40)=null OUTPUT, @BillingCity varchar(30)=null OUTPUT, @BillingStateCode varchar(2)=null OUTPUT, @BillingZipCode varchar(10)=null OUTPUT, @BillingPhone varchar(30)=null OUTPUT, @BillingEmail varchar(80)=null OUTPUT ) AS Select ContactTitle, ContactFirstName, ContactLastName, ContactAddress1, ContactAddress2, ContactCity, ContactStateCode, ContactZipCode, ContactPhone, ContactEmail, BillingTitle, Bil

Capture Stored Procedure Parameters in Trace

  
Hi, I want to capture each execution of all stored procedures and the parameters supplied, I'm going to do this via a trace on RPC:starting, capturing TextData, Database and starttime. I'll need to parse the TextData column to get the information that I want. I assume I also need to capture nested stored procedure calls , i.e. SP: starting as well. Does this sound correct and is this the best way of extracting this information ?Sean

Can I pass a value for a output variable in a stored procedure in ADO.NET?

  
I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

How to pass multi-value parameters to DDS (data driven subscription)?

  
Using RS2005, how should multivalue parameters be stored in a database field so that a data driven subscription can properly read and use them?  I have so far had no luck using syntax of: 1)  parm1, parm2 2)  parm1,parm2 Do single qutoes need to explicitly wrap the values?  Can you please provide an example and a SQL INSERT statement using parm1 and parm2 to demonstrate what to store in the database field?  Thanks!!

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(

Entity Framework - stored procedure mapping, excecuting SP with float parameters separated by commas

  

Hi.

Im using Entity framework with POCO entity generator template + SQL Server. I have mapping to stored procedure that takes two floats as input.

After executing function from code with two doubles as parameters: 1.23 and 4.56, SQL Profiler shows:

exec storedProcedureName @arg1 = 1,23, @arg2= 4,56

This is causing an error because it looks like that stored procedure takes 4 arguments, but it should take only two and there should be dots insted of commas in these floats.

My SQL Server collation is SQL_Latin1_General_CP1_CI_AS. I tried to set:

           Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
           Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

in DataContext Constructor, and di

Pass list to stored procedure

  

Hi,

I'm looking to pass a list into a stored procedure, stored as List<myClass>.  I've passed a table in before but I'm not sure how to pass a list - anyone help?



unable to pass dynamic dates to stored procedure with pivot

  

hi All,

                  I am unable to date as dynamic parameter to stored procdure with pivot.i am getting

error 

Msg 8114, Level 16, State 1, Procedure Sample, Line 3

Error converting data type nvarchar to datetime.

Msg 473, Level 16, State 1, Procedure Sample, Line 3

The incorrect value "@date1" is supplied in the PIVOT operator.

below is my stored procdure

 

create

procedure Sample(@date1 datetime,@date2 datetime)

as

SELECT

Please help URGENT - how to pass XML from aspx page to Stored procedure

  

Hi all,

I would like to take your help for a small task of mine. I have dataset whose contents have been converted as xml, the contents of which needs to be sent to a stored procedure. How do i go about creating methods in the data layer and the stored procedure.

What should be parameter type in the data layer's method and what should be the parameter type in the stored proc. I dont want to use a varchar at the stored proc level because it is limited to a length of only 8000 characters. Please suggest a solution

I would be glad if someone could post some sample code.


Problem with Oracle Stored Procedure/Web Form(C#)

  

Hi!

I receive the following message when trying to insert a record into a table via Web Form(C#):

Error: Invalid operation. Connection closed.

The button event, when clicked, should connect to the database and then call the stored procedure and
insert a new record according to the text input on the Web Form. Hence, returning the new flight id (TID).

Note: Some entries can be null.

Frontend-Code:

{code}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Add_Flight.aspx.cs" Inherits="Add_Flight" %>   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { width: 89%; } .style2 { height: 23px; } .style3 { width: 360px; } .style4 { width: 137px; } .style5 { height: 23px; width: 137px; } .style6 { width: 95px; } .style7 { height: 23px; width: 95px; } .style8 { width: 200px; } .style9 { width: 129px; } </style> </head> <body> <form id="form1" runat="server"> <div> <table cellspacing="3" class="styl

JDBC - stored procedure won't allow setting parameters by name

  

Hi;

When I try to call CallableStatement.setString( name, value ) it throws an exception. Is there something I have to do to either change the parameter names or set them up? I use the full "@Beginning_Date" as the name.

thanks - dave


Very funny video - What's your Metaphor?
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend