Passing A List/array To An SQL Server Stored Procedure 2005

Aug 16, 2007

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

 

ALTER PROCEDURE [dbo].[ProductZoneSearct]

(

@Productid char(8),
@Proname char(8),
@radius int,
@mode varchar(5) = 'M',
@Zone nvarchar(1000),

)

AS
SET NOCOUNT ON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))


Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@Zone)


Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
 From #Product As L
 Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
  Order By DateAvailable

 

if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH'  then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@Zone+')

In short if i pass value through varibale (@Zone) then i cant get result...but if i put  direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

View 5 Replies


ADVERTISEMENT

Passing Array With Ids To Stored Procedure

Apr 1, 2004

I want to pass and array of ids to a procedure for inserting i a relation table.

I found some examples in other posts, but had problems getting them to work.

I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.

(I´m using sql server 2000), had some problem with examples with strpos then.

Any hints ?

peace.

View 2 Replies View Related

Passing An Array Of Strings To A Stored Procedure

Jun 10, 2007

Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
 
)
AS
BEGIN
SET NOCOUNT ON
 EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
 ----------------------
THis works fine inside Sql Server 2005 Query analyser.
 But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
 
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.
 

View 3 Replies View Related

Passing An Array Into A Stored Procedure And The 'IN' Clause Will Not Do The Trick

Mar 6, 2008

I have a table that looks like this:




RecordId
PictureId
KeywordId

111
212
313
421
522
623
725
817
932
1044
I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
 
Im going crazy trying to find a simple solution for this. Please advise.

View 7 Replies View Related

For Loop List Of Array In Stored Procedure

Aug 19, 2014

I would like to write a store prodecure to return a month:

My output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug

and so on..

then i create list of array like below:

The counter for insert the week one by one

DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar
SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53'
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53

SET @counter = 1

[Code] ....

View 8 Replies View Related

Passing A List To Stored Procedure

Feb 12, 2001

Hello, I'd greatly appreciated if someone can help me out on this problem:
I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:

exec sp_GetLN "smith, lee, jones, hanson"


In the stored procedure:
@strLN varchar <- "smith, lee, jones, hanson"

/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/

SELECT * from tblCustomers
WHERE LASTNAME IN (@strLN)

It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB??
Your help is greatly appreciated~

Thank you

View 2 Replies View Related

Passing Array From ADO.NET To Strore Procedure `???

Jan 21, 2008

Dear all,

I need to pass an Array of string from .NET in C# to a store procedure. I do not know in advance the size of the array.
When this array is pass to my store procedure, I need to copy the all content of the array into an SQL table.

Based on that I have different question :

1 - is it possible to pass an Array[] as a strore procedure parameter ?
2- If yes what is the syntax to declare an input parameter as an array ?
3- Does the array as parameter is the proper way to pass unknown amount of parameters ?
4 - If not what are the other solution ? I read some stuff on XML document but did not find any real example which explain clearly how it should be done


Thnaks for your help
regards
Serge

View 1 Replies View Related

Passing An Array To A Stored Procedures

Feb 17, 2005

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !

View 3 Replies View Related

How Can I List All Stored Procedure In A SQL 2005 Database With C# ?

Jan 8, 2008

How can I list all stored procedure in a SQL 2005 database with C# ?
Thanks

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

Passing XML From Sql Server 2000 In Stored Procedure

Jul 23, 2007

Hi,
I m wanting a stored procedure to pass an XML string from a stored procedure to my application. Whats the best way to achieve this?

Hope you can help

Thanks

Paul

View 10 Replies View Related

Passing Like Criteria In Parameter To SQL Server Stored Procedure

Dec 13, 2006

Hello All,
 I was hoping that someone had some wise words of wisdom/experience on this.  Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
  I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion  errors but I am able to test the query successfully in the SQLDatasource.  What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like.   I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
 Thank you for any assistance,
 

View 3 Replies View Related

Array In A Stored Procedure

Jun 1, 2006

Can any one help me with a sample code, which can take an array of
elements as one of it's parameters and get the value inserted into a table in a stored procedure.



Thanks in advance

vnswathi.

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

How To Impliment Array In Stored Procedure?

Apr 8, 2005

i doing an online shop project which have an shoppingcart and it stored database.
and i have the situation like this.
the products have properties such as size, color .  and customers
can buy a product with particular size or color.   and i have
the shopping cart table structure and data like following
Id(primary key)      CartId     
productId      size     
color      quantity
1              
              
1              
1              
  S          red  
      10
2              
              
1              
1              
   S         black  
   2
3              
              
1              
1              
   S         blue  
     3
4              
              
1              
1              
   M         red  
      5
5              
              
1              
1              
   L         
blue         2

all the data above is i image the customer may inputed.  And my
problem is how to use an stored procedure to updata above record 
when a customer buy the same product which is one of the product from
above(have same productId, size, color)

and i try to use the following code but it didn't work
<code>
create procedure shoppingcart_add_item
(            @cartId   int,
             @productId   int,
             @size      nvarchar(20),
             @color     nvarchar(20),
             @quantity      int
)
AS
DECLARE       @countproduct
DECLARE        @oldsize
DECLARE         @oldcolor
select @countproduct=count(productId) FROM shoppingcart WHERE productId=@productId AND cartId=@cartId
select @oldsize=size,@oldcolor=color FROM shoppingcart WHERE productId=@productId

IF @CountItems > 0  and @oldsize = @size and @oldcolor = @color

    UPDATE
        ShoppingCart
    SET
        Quantity = (@Quantity + ShoppingCart.Quantity)
    WHERE
        ProductId = @ProductId
      AND
        CartId = @CartId

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO ShoppingCart
    (
        CartId,
        ProductId,
        Quantity,
        color,
        size
    )
    VALUES
    (
        @CartId,
        @ProductId,
        @Quantity,
        @size,
        @color
    )
</CODE>

and the result from this stored procedure is not what i want, what i
try to say is can i stored all the size and color in @oldsize and
@oldcolor array.  then loop through the array to get the one i
want??????
somebody get any idea???       or don't know what i am talking about?

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 To Create An Array In A Stored Procedure

Aug 8, 2000

I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.

View 1 Replies View Related

Array Of Values Using Stored Procedure

Feb 14, 2008

how to pass array of values in stored procedure..

View 2 Replies View Related

Stored Procedure With Array Of Parameters

Nov 23, 2005

I have a table on the database with columns like the following:Name Date DataJoe 11/5/05 data1Joe 11/6/05 data2Bob 11/5/05 data3Bob 11/8/05 data4I want to retrieve all data from an array or list I pass in thatcontainsone row for each name and a date, like below.Name DateJoe 11/6/05Bob 11/7/05I want to retrieve all rows from the first table where Name is Joe andDate > 11/6/05 or where Name is Bob and Date is > 11/7/05. There couldbe an unlimited number of name/date combinations.Can anyone suggest a way to write a stored procedure to handle thisquery.Thanks,Rick

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

Can SQL Break An Array Into One String? [Stored Procedure]

Jun 14, 2006

Hello, I have a question on sql stored procedures.I have such a procedure, which returnes me rows with ID-s.Then in my asp.net page I make from that Id-s a string likeSELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? Here is it:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
 Thank youArtashes

View 2 Replies View Related

Send GUID ARRay To The Stored Procedure

Apr 28, 2008

Hi there,

My requirement is to send more than one GUID to the stored procedure.

How can I do that? If you can give me an example that will be great.

Kind regards,
Ricky

View 2 Replies View Related

Working With An Integer Array In A Stored Procedure

May 4, 2006

hi there,

i need a procedure that works with C# e.g.:

using (SqlCommand cmd = GetCommand("Procedure_Name"))

{

//i=an array of integer values

cmd.Parameters.Add("@array", SqlDbType.????!?!???).Value = i;

cmd.ExecuteScalar();

}

i need to write a stored procedure that takes as input an array of integers (amongst other values)

this procedure must loop through every integer in the array and INSERT a new record into a table.

i have never used T-SQL before.

Many thanks

View 3 Replies View Related

Passing A List Of Numbers To A Stored Procudure, Having A Size More Than 8000 Characters

Aug 1, 2007

Hi..

I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@Items varchar(100) --- List of numbers
)
AS Begin

Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)


This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP??






View 4 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

Array List In DB

Feb 14, 2005

Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,

Kishore

View 14 Replies View Related

Passing SqlDataSource Object An Array As A Parameter

Feb 22, 2007

Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
 
 
        DataTable dt = new DataTable();        DataTable dt2 = new DataTable();        DataView dv = new DataView();                        
       foreach(ListItem liOptions in ListBox1.Items)       {             if(liOptions.Selected)             {                                      SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);                   dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);                   dt2 = dv.Table;                   dt.Merge(dt2);                   dt2.Dispose();                   SqlDataSource1.SelectParameters.Clear();             }       }
        if (dt.Rows.Count > 0)        {           Graph(dt);                             //Pass original datatable (dt) to Graph();           dt.Columns.RemoveAt(2);      //Reformat datatable (dt) and remove last column before binding to Gridview1
           GridView1.DataSource = dt;           GridView1.DataBind();        } else {
    errorMessage.Text = "No data was returned!"; }

View 3 Replies View Related

Passing An Array To Query In A Table Adapter

May 23, 2007

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter.  For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this.  Any help would be greatly appericated.Thanks,Ryan. 

View 6 Replies View Related

Passing An Array And/or Variable Field Name To An SProc

Jul 20, 2005

I have 2 questions.I am trying to write a stored procedure to update a table. I am tryingto pass a variable that represents the name of the column/field andanother for the value that I am changing.For example:@FieldName VARCHAR(100)@FieldValue VARCHAR(100)ASUPDATE tblTHETABLESET @FieldName = @FieldValueFirst is it possible to use a variable as the column/field name? Ifso, how do I go about it?Also, it would be nice if I could have the @FieldName and @FieldValuevariables as arrays. Is that possible?Thank-you for any assistanceBill

View 2 Replies View Related

Transact SQL :: Passing C++ / C# Array To INSERT Command

Aug 13, 2015

I have to send an array, which are generated in C++ (or C#) to an SQL-INSERT. Something like this:

INSERT INTO Table1
(Col1,Col2)
VALUES(:a,:b)
// a and b are my arrays//:a=A,B,C,D.....//:b=1,2,3,...

Is there any way to do something like my prototype in MS SQL Server? If yes how should be my INSERT? 

View 3 Replies View Related

Passing @UserName Into Stored Procedure

Oct 11, 2006

I have successfully Create a Site, Inserting Updating and
Deleting information in my DB all with Stored Procedures, But I need the
ability to pass their username into my Stored Procedures. How and where do I
code this in my ASPX file?



 My SP would be something like this



 Create procedure test



             @UserName
varchar(50)

As



Select *

From table

Where username
= @username

 

 

All of the
data is tied to the user in one way or another but I do not know what code to
put in my page?

View 2 Replies View Related

Passing Xml To Stored Procedure For Inserting

Feb 16, 2007

Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?

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







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