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 !
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.
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.
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.
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 !!!
This is not obvious to me...
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct?
Is there any other way to approach the problem?
Thanks a lot. -julio
I thought that I had read somewhere that SQL 2000 allowed you to pass an array into a stored procedure. I've tried to look up some additional information pertaining to this but I haven't found anything. Did I dream this or can you pass an array to a stored procedure with SQL 2000? If you can please give me some information on this or where I can find some.
Thanks!
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins
Dear all,
i want to know how i can pass multiple values in the form of arrays to a stored procedures.
the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
'
'cmdInsSlabHmst
'
Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
Dim strCode As String
Dim i As Integer
'Dim dblSlabRate As Decimal
'Dim dblSlabFrom As Decimal
Dim strSlabRate As String
Dim strSlabFrom As String
Dim strSlabRateP As String
Dim strSlabFromP As String
Dim intCntr As Integer
'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
'OleDbConnection1.Open()
'strCode = cmdInsSlabHmst.ExecuteScalar
'OleDbConnection1.Close()
For i = 0 To dgSlabDtl.Items.Count - 1
If i = dgSlabDtl.Items.Count - 1 Then
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
Else
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
End If
strSlabRateP += strSlabRate & "~"
strSlabFromP += strSlabFrom & "~"
intCntr += 1
'If dblSlabRate <> "" And dblSlabFrom <> "" Then
'InsDtl(strCode, dblSlabFrom, dblSlabRate)
'End If
Next
If strSlabRateP <> "" And strSlabFrom <> "" Then
With cmdInsSlabHmst
.Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
.Parameters("iREMARKS").Value = txtRemarks.Text
.Parameters("iSLABFROM").Value = strSlabFromP
.Parameters("iSLABRATE").Value = strSlabRateP
.Parameters("iNOOFRECORDS").Value = intCntr
End With
OleDbConnection1.Open()
cmdInsSlabHmst.ExecuteNonQuery()
OleDbConnection1.Close()
End If
to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.
i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.
regards
subhajit
Hello,
I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.
I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.
Thanks for your help, Gary
Here is the web code:
Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)
cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))
cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue
conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()
Here is the stored procedure:
CREATE PROCEDURE s_ItemLookUp
@Field AS VARCHAR(50),
@Value AS VARCHAR(50)
AS
SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO
Someone recently tried to tell me that it is possible to pass an array to a stored procedure.
I have tried creating procedures with the 'table' datatype for the parameters but the attempts fail with a syntax error.
We currently workaround this limitation by passing the equivalent of an array to temporary tables and selecting from those tables as needed within our stored procedure but if we can circumvent this by passing an array, we'd definetly like to try that instead.
Thanks in advance for any advice you may share.
Hi guys,
I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.
Thanks in advance.
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies View RelatedCan anyone tell me the correct syntax for passing a Table Name to a Stored Procedure that uses the Table Name to do an INSERT into that Table ?
Below is my poor effort at the SQL.
Thanks.
CREATE PROCEDURE InsertOrderLine
@OrderNo INT,
@ProductID VARCHAR(15),
@UnitCost MONEY = 0,
@Quantity FLOAT = 0,
@Discount MONEY = 0,
@Tax MONEY = 0,
@TABLENAME VARCHAR(200)
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL='INSERT ' + @TABLENAME + ' (OrderNo,
ProductID, UnitCost, Quantity, Discount, Tax) VALUES
(@OrderNo + ',' + @ProductID + ',' + @UnitCost + ',' + @Quantity +
',' + @Discount + ',' + @Tax)'
GO
Hi
I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.
IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..
CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I
Thanks
VENU
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!
View 1 Replies View RelatedHow can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
View 2 Replies View RelatedDear 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
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!"; }
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 RelatedI 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 RelatedI 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?
How does one pass into a Stored Procedure an array of numbers to be used in an IN clause? If I pass "1,2" in a VARCHAR, the stored procedure sees only the first number (1 in this case).
I'm using VB and ADO.NET, but I don't know how to set up the stored procedure for an array. Is there a parsing function to do this?
CREATE PROCEDURE TestInClause( @TeamList VARCHAR)ASSELECT Name FROM Teams WHERE TeamID IN (@TeamList); /* sees only 1st number */GO
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedCan 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.
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
How would I go about storing (1 column) database results in a Array?
And then how would I go about extracting the elements from the Array?
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?
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 RelatedI 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 RelatedIn my front end, I have a procedure in which the operator selects multiple tickets. I have in mind to create a stored procedure which accepts its input parameter and plugs it into an IN() clause in an Update statement, something along these lines:
Update TicketInventory Set SalesID = 12345 Where TicketInventoryID IN( myInputParam );
Can I pass an array of Integers into a stored proc and have it work like this? Alternatively, I could pass a comma-delimited string containing the Integers. Could I plug that into the IN()? Or is there is yet another way to accomplish this?
TIA,
Arthur
Can you pass an array into a stored proc or do we have to use indiviual parameters.
If we can, any code exmaple woudl be great.
Cheers
Chris