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]
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.
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 !!!
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
============================== 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 !
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.
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 */
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.
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
I am using the code given below which works fine for array of integers, can anyone help me to convert this code to use it for array of strings basically i want to store the search keywords in the array of strings and use them in the stored procedure.CREATE Function fnSplitter (@IDs Varchar(100) ) Returns @Tbl_IDs Table (ID Int) As Begin -- Append comma Set @IDs = @IDs + ',' -- Indexes to keep the position of searching Declare @Pos1 Int Declare @pos2 Int -- Start from first character Set @Pos1=1 Set @Pos2=1 While @Pos1<Len(@IDs) Begin Set @Pos1 = CharIndex(',',@IDs,@Pos1) Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int) -- Go to next non comma character Set @Pos2=@Pos1+1 -- Search from the next charcater Set @Pos1 = @Pos1+1 End ReturnEndCREATE PROCEDURE spSelectEmployees(@IDs Varchar(100)) AS Select * From employees Where employeeid In (Select ID From fnSplitter(@IDs))Exec spSelectEmployees '1,4,5,7,9'I want replace the above Exec statement with Exec spSelectEmployees 'sap, abap, hr, .... ' in the JobPosition Column of Employees if possible, i've been trying to change it but its not working outThanks in Advance
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
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
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though. So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>" Is there an way to accomplish this inside a stored procedure?
Hello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao CREATE PROCEDURE dbo.SendRenewalEmail @ID INT AS BEGIN exec dbo.sp_send_cdosysmail_htm @From = 'yosemite.sam@acme.com', @To = 'road.runner@acme.com', @Cc = null, @BCC = null, @Subject = 'Order ' + @ID + 'Renewal Reminder', @Body = 'Hello roadrunner....' ENDGO
I want to know that how can i write a stored procedure that takes an input param, i.e., a string and returns the count of the matching words between the input parameter and the value of a column in a table. e.g. I have a table Person with a column address. now my stored procedure matches and counts the number of words that are common between the address of the person and the input param string. I am looking forward for any help in this matter. I am using Sql server 2005.
All of a sudden hen I script out a Stored Procedure it encloses strings (edit) in Double Quotes?
For example ANDPromo.[Group] IN (''FL_Small'',''FL_Large'')
Also it generates this code that I do not want. I just was Create Procedure...
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_IncentiveReport]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'
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.
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
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?
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>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors. Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors. The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails. SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity() END and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SELECT @NewpublicationID Thanks Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
Hi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
