I have a table on the database with columns like the following:
Name Date Data
Joe 11/5/05 data1
Joe 11/6/05 data2
Bob 11/5/05 data3
Bob 11/8/05 data4
I want to retrieve all data from an array or list I pass in that
contains
one row for each name and a date, like below.
Name Date
Joe 11/6/05
Bob 11/7/05
I want to retrieve all rows from the first table where Name is Joe and
Date > 11/6/05 or where Name is Bob and Date is > 11/7/05. There could
be an unlimited number of name/date combinations.
Can anyone suggest a way to write a stored procedure to handle this
query.
Thanks,
Rick
Hi 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
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.
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.
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
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 !!!
Hi 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)
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.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
Hi,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
There 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.
Hi 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
I 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.
I 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?
I 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.
Parameters are passed to Stored Procedure from VB.NET Application like
Dim param As SqlParameter param = New SqlParameter("@name",DbType.String) param.Direction = ParameterDirection.Input param.Value = "ABC" comand.Parameters.Add(param)
But is there any way to specify parameters without specifying the parameter name and type like
Dim param As SqlParameter param = New SqlParameter param.Direction = ParameterDirection.Input param.Value = "ABC" comand.Parameters.Add(param)
But when i try like this i am getting an error saying that "Parameter1 is not a parameter in the procedure"... Can anybody tell me how to solve this . Thanx in advance
I 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.
Hi All, I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD. Thanks,
Hello everyone, I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance Errors 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' protected void Button1_Click(object sender, EventArgs e) { /* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */ string Interview1 = TextBox1.Text; string Interview2 = TextBox2.Text;
//Create Sql connection variable that call the connection string SqlConnection SqlConnection = new SqlConnection(GetConnectionString());
//Create a sql command to excute SQL statement against SQL server SqlCommand Command = new SqlCommand();
// Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure;
//Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand = "Summaries";
//open the command connection with the connection string Command.Connection = SqlConnection;
// Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3);
Hi Y'all, On how many ways can i pass parameters IN a stored procedure? I want to boost my performance and get rid of 4 for loops in my page load. SO how can I create a resultset (grid) with the same result as looping? I hope i made myself clear.... Thanks in advance
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>
How to write stored procedure with two parametershow to check those variables containing values or empty in SP if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one Select * from orders plz write this SP for me thanks
