Array As Parameters To Procedure
Jun 1, 2005Hi friends,Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.regards,Asad Mahmood
View 2 Replies
ADVERTISEMENT
Stored Procedure With Array Of Parameters
Nov 23, 2005I 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 RelatedParameters!Blah.Label(0) - How To Iterate Through Array
Nov 27, 2007I have a multi select param that I display on the title of the report using something like so:
Parameters!Division.Label(0)
The problem is when a user selects more than one value it does not get displayed on the title. Addding Parameters!Division.Label(0) + Parameters!Division.Label(1) + Parameters!Division.Label(2) doesn't returns an error if user only selects 1 or 2 values because Parameters!Division.Label(2) is empty.
Is there a way to iterate through or do error checking on the array so that I can dynamically populate the title with the values selected by the user.
Any help would me much appreciated.
Array In A Stored Procedure
Jun 1, 2006Can 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.
Array In Store Procedure
Jul 20, 2005Dear all,Sometimes I happened the require that the number of input parametersof SP is not fixed, Can sql T-SQL handle the array (dynamic array)?Does anybody ever used an array name as the input parameter to callthe SP?thanks,Robert
View 3 Replies View RelatedArray As Procedure Parameter
Jun 27, 2006I have a shift definition table with the columns:
shift_id: shift's id
shift_name: shift's name
shift_number_of_day: shift's "position" on the day
initial_hour: shift's initial hour
final_hour: shift's final hour
The shift definition depends on the company: company A may have 2 shifts, and company B may have 3 shifts, for example.
I need to load a dimension table, dim_time, that should have a row for each hour of each day of a specific year. I would have
alternate_time_key ... shift_name ...
1/1/2006 01:00:00 GraveYard
1/1/2006 02:00:00 GraveYard
and so on, until it reaches the end of the year.
So in my procedure to load the dimension table, I would have something like
IF (@alternateTimeKeyHour >= @paramShift1InitialHour) AND (@alternateTimeKeytHour <= @paramShift1FinalHour)
BEGIN
SET @shiftName = @paramFirstShiftName;
SET @shiftNumberOfDay = 1;
END
ELSE IF (@alternateTimeKeyHour >= @paramShift2InitialHour) AND (@alternateTimeKeyHour <= @paramShift2FinalHour)
BEGIN
SET @shiftName = @paramSecondShiftName;
SET @shiftNumberOfDay = 2;
END
.
.
.
The problem is that I would have a variable number of shifts (variable number of parameters!)...
The only solution I could think was using an array, but as far as I could see it's not possible to pass
an array as a parameter to a procedure. Is this right? Is there a better solution to do this? Can anyone help me please???
Thank you!
Passing Array With Ids To Stored Procedure
Apr 1, 2004I 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.
How To Impliment Array In Stored Procedure?
Apr 8, 2005i 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?
How Array Will Pass To Stored Procedure
May 7, 2001I 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 RelatedHow To Create An Array In A Stored Procedure
Aug 8, 2000I 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 RelatedArray Of Values Using Stored Procedure
Feb 14, 2008how to pass array of values in stored procedure..
View 2 Replies View RelatedIs It Possible To Pass Array To Stored Procedure
Jan 9, 2007Dear 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
Passing Array From ADO.NET To Strore Procedure `???
Jan 21, 2008Dear 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
Passing An Array Of Strings To A Stored Procedure
Jun 10, 2007Well, 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.
Can SQL Break An Array Into One String? [Stored Procedure]
Jun 14, 2006Hello, 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
Send GUID ARRay To The Stored Procedure
Apr 28, 2008Hi 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
For Loop List Of Array In Stored Procedure
Aug 19, 2014I 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] ....
Working With An Integer Array In A Stored Procedure
May 4, 2006hi 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
ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure
Mar 12, 2008Hi 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
RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.
Jan 25, 2007All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
Passing An Array Into A Stored Procedure And The 'IN' Clause Will Not Do The Trick
Mar 6, 2008I 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.
Passing A List/array To An SQL Server Stored Procedure 2005
Aug 16, 2007Hi, 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 !!!
How Would I Send A String Array As A Integer Array?
Jun 25, 2007I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
Sep 20, 2007I 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
SQL Procedure Parameters
Jul 20, 2005Hi everyone,I need to create a procedure that would take the two parameters @FieldNameand @FieldValue and replaces it in my SQL statement without using Execbecause Exec can take at most 128 characters.CREATE PROCEDURE Get_Person_By_Field@FieldName varchar(20),@FieldValue varchar(100)AS......INSERT INTO#TempPerson(PID,Title,PhNum1,PhExt1,PrimaryEmail,F ullName,CompanyName) SELECT PID,Title, PhNum1, PhExt1,PrimaryEmail,FullName, CompanyName FROMPerson LEFT JOIN Company ON Person.CID = Company.CID WHERE@FieldName='@FieldValue'Thank youMaz.
View 1 Replies View RelatedStored Procedure Parameters
Oct 3, 2006Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like: SqlCommand sc = new SqlCommand("AddToMyTable"); try { sc.CommandType = CommandType.StoredProcedure; sc.Connection = new SqlConnection("myconnectionstringhere"); SqlParameter param; param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50); param.Value = "something"; ..... here I repeate "add" and value assignment for all my parameters }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad
A Stored Procedure With Many Parameters
Jan 1, 2008There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable:
1. After each click by user , the insert stored procedure will be called
2. All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
Stored Procedure Parameters
Mar 31, 2005Hi
what's the difference between using parameters in these two ways:
SELECT
@PortalName = Portals.PortalName ...
and
SELECT
Portals.PortalName...
first one will NOT be a result set, and second one will
Whats the basic difference between that? I need to use a NextResult method within a .cs filebut it is not working
thanks
Regarding Stored Procedure Parameters
Jun 29, 2007I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters.
If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.
Now what I want is even I enter one value this must be excute………….i think some validations should be done in
Stored procedure's 'where' clause.but I don’t know how
i would be very happy if someone guides me with sample code for this.
Stored Procedure Parameters
Jun 16, 2008I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?
Thanks.
Stored Procedure With Parameters
Dec 6, 2006Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?
Example, my stored procedure is:
create my_sp @inputvar varchar(12)
as
select @inputvar
go
then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter
next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt
is it possible to do that way? Or is there any other way to do so?
Thanks a bunch,
nicoart.
Regarding Stored Procedure Parameters
Jun 29, 2007I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.
Passing Parameters To A Procedure
May 30, 2008
How does we pass dynamic number of parameters to a procedure?
Eg.
DECALRE @IDs varchar(50);
SET @IDs = '1,2,3'
SELECT Description FROM MyTable WHERE ID IN (@IDs )
Here ID is int. When I tested this, it shows an error 'Cannot covert '1,2,3' to int'. No errors while it is one number ie '1'
How I can manage this type of problem in a stored procedrue that receive the @IDs as parameters?