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


ADVERTISEMENT

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

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

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

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

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

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

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

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

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),)
ASSET 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, PhoneFrom [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 View Related

Create An Array With T-SQL

Oct 10, 2007

I'm using SQL Server 2005 and have have a table similar to the following:

Name Week Role
Bob 1 Primary
Bob 2 Backup
Bob 3 Primary
Joe 1 Backup
Joe 2 Backup
Mike 1 Primary
Mike 2 Backup



Is there a t-SQL query that will combine the role and week into an array and return a table like:

Name Role
Bob Primary: 1, 3
Backup: 2
Joe Backup: 1, 2
Mike Primary: 1
Backup: 2

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Create Array Of Columns

Feb 18, 2008

Absolute beginner to SQL here...

I'm try to create an array of columns and then refer to these columns at the asp.net level.

Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.

Thanks in advance.

Larry

View 6 Replies View Related

Create An Array In A Result Field

Sep 12, 2006

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier.  Here is few result samples I receive from this code I am using now.  
ReqNo              ProcID
7102005          1409
7102005          1796
7139003          1411
7139003          6097
7261030          1409
7261030          1796
7268303          3998
7268303          4000
 
I would like to create a single row for each "ReqNo" and have a field that will an array of the  "ProcID" results I receive.  In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field?  I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table. 
 
So, ideally my result would be:
ReqNo             ProcSet
7102005          1409, 1796
7139003          1411, 6097
7261030          1409, 1796
7268303          3998, 4000
 
Is this possible?


declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
 
select
 @startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
 
select
@enddate = dateadd (dd, 1 , @startdate)
 
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
 
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
 
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
 
order by
pp_req_no, pp_cproc_id_r

 

View 4 Replies View Related

How Can I Create Stored Procedure?

Feb 13, 2008

 forumid    questid   answerid   answer     replyby   replyon    1            1            1            xxxx        aaa        01/01/08(mm/dd/yy)    1            1            2            yyyy        bbb        01/02/08    2            1            1            zzzz        ccc        01/02/08    1            1            3            hhhh         bbb        01/04/08    2            1            2            uuuu         vvv         01/04/08    1            2            1            tttt            ooo        01/05/08suppose i give forumid value=1 i want following answer recent reply and no.of replies  forumid    questid   answerid  no.of.reply  answer     replyby   replyon            1            1            3            3               hhhh        bbb       01/04/08      1            2            1            1               ttt            ooo        01/05/08 

View 14 Replies View Related

Create Stored Procedure

Apr 21, 2004

Hi!

I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.

Please help me!

Mike.

View 5 Replies View Related

How To Create Rdl Using Stored Procedure Which Use

Apr 3, 2008

Hi

I am sql server 2005.

how to create rdl using stored procedure which uses parameter


Thank you.

View 1 Replies View Related

Create Stored Procedure

May 6, 2008

Is there a way to create a stored procedure that accepts a column name and returns the name of the tables that have that column name?

View 2 Replies View Related

Create A Stored Procedure

May 7, 2008

I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:

create proc spTheNameOfTheProcedure
as
select table_name from information_schema.columns
where column_name = XXXXXX

no sure what to put where I have XXXXX

View 2 Replies View Related

Create Stored Procedure

Dec 22, 2007

Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007

If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)


results would be 10_12_2007,10_13_2007.

So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3

View 1 Replies View Related

How To Create A PDF From A Stored Procedure

Sep 18, 2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please


Thanks in advance for your help

View 5 Replies View Related

How To Create Array Column And How To Retrive In Sqlserver

May 9, 2005

hi
i am using database sqlserver,i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server
1.how to create array column in a table using sqlserver
if possible how can i use select query  for that

View 4 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

Stuck Trying To Create A Stored Procedure

May 3, 2005

Hi

Let me start by saying that SQL is
not my strong suit so please bear with me.

Here's my situation
I'm building a property portfolio system with db structure as follows

tblProperty (The master table - linked 1 to many via propertyID)
tblValuations (columns valuationID, propertyID, currentValue, valuationDate)

For each property in tblProperty the valuations table can hold multiple valuation entries.

I need to have a summary that shows the number of properties a user has
listed and crucially I need to be able to find the latest
valuation for each property & add them together to retrieve the
total value of a users portfolio

I'm totally stumped with this so any help/advice would be gratefully received

Lee

 

View 1 Replies View Related

Grant &#34;create Stored Procedure&#34;

Feb 24, 2000

Hi!
How do I grant rights to an user for creating stored procedures (using T-SQL)?
Thanks,
Fabio

View 1 Replies View Related

VB5 Create Temporary Stored Procedure

Sep 3, 1998

When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?

The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240

View 4 Replies View Related

Create Stored Procedure Without Admin Right

Mar 12, 2001

I have no right to connect to my hosting company using MS SQL2000 client.
And I have to use stored procedure, can any body tell me is it possible to
create stored procedure ( I need is complex one with loops of if loops).
If possible please list the syntax.

View 2 Replies View Related







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