How Many Records A Stored Procedure Returns?
Oct 11, 2007Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.
ADVERTISEMENT
Stored Procedure Returns No Value
Mar 1, 2006Hi,I have a stored procedure (the code is below) that I use to retrieveone value from my database. I tested the code in Query Analyzer, and itworks (I get the value I was looking for). However, when I call thesame code from the stored procedure, I get no value. The code that isexecuted is the same and the input parameter is the same. Does anybodyhave an idea?The code:SELECT Top 1CharID,CharName,CategoryName,CharDescription,UserIDFROM [Character]WHERE CharName='Character'-- returns the right valueThe Stored Procedure:CREATE PROCEDURE SpCharacters_SelectOneByUserName@UserName NVarCharASSELECT Top 1CharID,CharName,CategoryName,CharDescription,UserIDFROM CharacterWHERE CharName=@UserNameGO-- returns no value
View 2 Replies View RelatedStored Procedure Returns -1 For A Bit Field
Apr 15, 2008Hello,I have a stored procedure: -- Get an individual league match by IDALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]( @LeagueMatchID int)ASSET NOCOUNT ONSELECT * FROM mb_LeagueMatch WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchIDThe mb_LeagueMatch table has a column named IsActive that is a bit datatype.The value for all rows is set to true (in database explorer in visual studio 2005).When I execute the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.Has anyone seen this before?Thanks,Howard
View 4 Replies View RelatedStored Procedure Returns Boolean???
Sep 30, 2004hi,
i just want to check whether the given data exists in the database. i am using stored procedure. is there any way to know whether that value exists in database without the need to select anything?
Is It Possible To Have A Stored Procedure That Returns 2 Values?
Oct 5, 2004Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.
LIKE In Stored Procedure Returns No Record
Jun 29, 2005I have following stored procedure:
-------------
CREATE PROCEDURE dbo.Test
@name as char(36)
as
select a, b
from testtable
where name LIKE @name +'%'
------------
when I run the select statement from query analyzer,
select a, b from testtable where name LIKE 'a%'
it returns records.
But when I call the stored procedure from query analyzer,
exec Test 'a'
it returns no record.
What might be wrong?
Any help is appreciated.
SQL Stored Procedure Returns Duplicates
Mar 23, 2007I am new to SQL and SQL Server world. There must be a simple solutionto this, but I'm not seeing it. I am trying to create a crystalreport (v8.5) using a stored procedure from SQL Server (v2000) inorder to report from two databases and to enable parameters.When I create the stored procedure, it joins multiple one-to-manyrelationship tables. This results in repeated/duplicate records. Isthis an issue that should be solved within the stored procedure, or isthis inevitable? If latter, how do you eliminate the duplicates inCrystal Reports?Let's say we have three different tables - Event, Food, Equipment.Each event may have multiple food and multiple equipments; some eventsmay not have food and/or equipments. The stored procedure outcome maylook like this:Event Food Food_Qty EquipmentEquipment_QtyEvent1 Food2 10 Equipment51Event1 Food4 10NULL NULLEvent2 Food4 50 Equipment210Event2 Food4 50 Equipment52Event2 Food1 12 Equipment210Event2 Food1 12 Equipment52As you can see in Event2, for each Food variations, Equipment valuesrepeat. When I am creating a Crystal Reports, I have the duplicationproblem.What I would like to see in the report is either:Event1Food2, 10 Equipment5, 1Food4, 10Event2Food4, 50 Equipment2, 10Food1, 12 Equipment5, 2OR:Event1Food2, 10Food4, 10Equipment5, 1Event2Food4, 50Food1, 12Equipment2, 10Equipment5, 2Attempt1: Using "Eliminate Duplicate Record" option does not work withthe Equipment section since CR does not recognize "Equipment2" in thethird line of the table and "Equipment2" in the fifth line of thetable as duplicates.Event1 Food2, 10 Equipment5, 1Food4, 10Event2 Food4, 50 Equipment2, 10Equipment5, 2Food1, 12 Equipment2, 10(duplication)Equipment5, 2(duplication)Attempt2: I created group for each category (Event, Food, Equipment),put the data in Group Headers and used "Suppress Section" to eliminateif the same equipments are listed more than once within the Foodgroup. This eliminated the duplication, but the items do not aligncorrectly.Event1 Food2, 10 Equipment5, 1Food4, 10Event2 Food4, 50 Equipment2, 10Equipment5, 2Food1, 12 (I want this to appear right below the'Food4, 50' line)I would really appreciate any suggestions! Thank you in advance.
View 4 Replies View RelatedStored Procedure Returns Duplicates
Apr 10, 2007I am trying to create a report in Crystal Reports (v 8.5). I have astored procedure to pull data from two databases and parameters.There are multiple one-to-many relationships and the stored procedurereturns duplicates; e.g., one schedule may have multiple resources,supplies, and/or orders (and one order may have multiple foods). Isthere a way to stop the duplication?The stored procedure looks like this:************************************************** **********************************SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOCREATE PROCEDURE usp_rpt1 (@start_date smalldatetime,@end_date smalldatetime,@rpt_type varchar(3),@rpt_id int)ASset nocount on--Set up some string variables to build the selection query for theparameters supplieddeclare @fields varchar(255)declare @tables varchar(255)declare @where varchar(2000)CREATE TABLE #tmp_sched(sched_id int, rpt_type_desc varchar(100),rpt_id int)set end_date = midnight of next daySELECT @end_date = DATEADD(day,1,@end_date)SELECT @end_date = CONVERT(smalldatetime,CONVERT(varchar(4),YEAR(@end_date)) + '-'+CONVERT(varchar(2),MONTH(@end_date)) + '-'+CONVERT(varchar(2),DAY(@end_date))IF @rpt_type = 'LOC'INSERT INTO #tmp_schedSELECT DISTINCT s.sched_id, l.loc_desc, l.loc_idFROM tbl_sched sLEFT JOIN tbl_sched_res_date srd ON s.sched_id = srd.sched_idLEFT JOIN tbl_res r ON srd.res_id = r.res_idLEFT JOIN tbl_grp g ON r.grp_id = g.grp_idLEFT JOIN tbl_loc l ON g.loc_id = l.loc_idWHERE l.loc_id = CONVERT(varchar(12),@rpt_id)AND g.obsolete_flag = 0AND r.obsolete_flag = 0ANd l.obsolete_flag = 0AND s.deleted_flag = 0AND srd.mtg_start_date_local >=CONVERT(varchar(20), @start_date, 1)AND srd.mtg_start_date_local <CONVERT(varchar(20), @end_date+1,1)IF @rpt_type = 'GRP'INSERT INTO #tmp_schedSELECT DISTINCT s.sched_id, g.grp_desc, g.grp_idFROM tbl_sched sLEFT JOIN tbl_sched_res_date srd ON s.sched_id =srd.sched_idLEFT JOIN tbl_res r ON srd.res_id = r.res_idLEFT JOIN tbl_grp g ON r.grp_id = g.grp_idWHERE (g.grp_id = CONVERT(varchar(12),@rpt_id)OR g.parent_grp_id =CONVERT(varchar(12),@rpt_id))AND g.obsolete_flag = 0AND r.obsolete_flag = 0AND s.deleted_flag = 0AND srd.mtg_start_date_local >=CONVERT(varchar(20), @start_date, 1)AND srd.mtg_start_date_local <CONVERT(varchar(20), @end_date+1,1)IF @rpt_type = 'RES'INSERT INTO #tmp_schedSELECT DISTINCT s.sched_id, r.res_desc, r.res_idFROM tbl_sched sLEFT JOIN tbl_sched_res_date srd ON s.sched_id =srd.sched_idLEFT JOIN tbl_res r ON srd.res_id = r.res_idWHERE r.res_id = CONVERT(varchar(12),@rpt_id)AND r.obsolete_flag = 0AND s.deleted_flag = 0AND srd.mtg_start_date_local >=CONVERT(varchar(20), @start_date, 1)AND srd.mtg_start_date_local <CONVERT(varchar(20), @end_date+1, 1)IF @rpt_type = 'REG'INSERT INTO #tmp_schedSELECT DISTINCT s.sched_id, reg.region_desc,reg.region_idFROM tbl_sched sLEFT JOIN tbl_sched_res_date srd ON s.sched_id =srd.sched_idLEFT JOIN tbl_res r ON srd.res_id = r.res_idLEFT JOIN tbl_grp g ON r.grp_id = g.grp_idLEFT JOIN tbl_loc l ON g.loc_id = l.loc_idLEFT JOIN tbl_region reg ON l.loc_id = reg.region_idWHERE reg.region_id = CONVERT(varchar(12),@rpt_id)AND reg.obsolete_flag = 0AND l.obsolete_flag = 0AND g.obsolete_flag = 0AND r.obsolete_flag = 0AND s.deleted_flag = 0AND srd.mtg_start_date_local >=CONVERT(varchar(20), @start_date, 1)AND srd.mtg_start_date_local <CONVERT(varchar(20), @end_date+1, 1)IF @rpt_type NOT IN ('LOC','GRP','RES','REG')INSERT INTO #tmp_schedSELECT DISTINCT s.sched_id, g.grp_desc, g.grp_idFROM tbl_sched sLEFT JOIN tbl_sched_res_date srd ON s.sched_id =srd.sched_idLEFT JOIN tbl_res r ON srd.res_id = r.res_idLEFT JOIN tbl_grp g ON r.grp_id = g.grp_idWHERE (g.grp_id = 0 OR g.parent_grp_id = 0)AND g.obsolete_flag = 0AND r.obsolete_flag = 0AND s.deleted_flag = 0AND srd.mtg_start_date_local >=CONVERT(varchar(20), @start_date, 1)AND srd.mtg_start_date_local <CONVERT(varchar(20), @end_date+1,1)--This is the selection for our reportSELECT Description = ts.rpt_type_desc,Date = CONVERT(varchar(12),srd.mtg_start_date_local,101), StartTime = srd.mtg_start_date_local,EndTime = srd.mtg_end_date_local,SchedID = s.sched_id,MeetingTitle = s.sched_desc,ResourceUsed = r.res_desc,ResourceSetup = su.setup_desc + ' (' +CONVERT(varchar(10),rs.capacity) + ')',NumberOfAttendees = Attendees.string_value,OrderID = ord.order_id,FoodQty = CONVERT (int,oi.order_qty),FoodDesc = i.item_name,Side = sidei.item_name,MeetingDesc = ord.order_desc,Supplies = suppliesudf.udf_desc,SuppliesVal = supplies.value,AccountCode = ord.order_user_acct_code,host.string_value as MeetingHost,CateringNotes = ord.order_notes,FoodNotes = oi.order_notesFROM #tmp_sched tsJOIN tbl_sched s ON ts.sched_id = s.sched_idJOIN tbl_sched_res_date srd ON ts.sched_id = srd.sched_idJOIN tbl_res r ON srd.res_id = r.res_idJOIN tbl_sched_res_setup srs ON s.sched_id = srs.sched_id andr.res_id = srs.res_idLEFT JOIN tbl_res_setup rs ON srs.setup_id = rs.setup_id ANDsrs.res_id = rs.res_idLEFT JOIN tbl_setup su ON rs.setup_id = su.setup_idLEFT JOIN tbl_sched_request_tab_val supplies ON s.sched_id =supplies.sched_idAND ((supplies.request_tab_id =(SELECT request_tab_id FROM tbl_request_tab WHERE(request_tab_hdr = 'A) Meeting Supplies')))OR (supplies.request_tab_id =(SELECT request_tab_id FROM tbl_request_tab WHERE(request_tab_hdr = 'Mtg Supplies-PEMC'))))AND (CONVERT(varchar, supplies.value) NOT IN ('0', ''))LEFT JOIN tbl_udf suppliesudf ON supplies.udf_id =suppliesudf.udf_idJOIN tbl_sched_udf_val attendees ON attendees.sched_id = s.sched_idAND attendees.udf_id =(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'Number ofAttendees') --UDF For No of AttendeesJOIN tbl_sched_udf_val host ON host.sched_id = s.sched_idAND host.udf_id =(SELECT udf_id FROM tbl_udf WHERE udf_desc = 'MeetingHost') --UDF For meeting host nameLEFT JOIN RSCatering.dbo.tbl_Order ord ON ord.order_sched_id =s.sched_id --Our link to table in other databaseJOIN RSCatering.dbo.tbl_order_item oi ON ord.order_id =oi.order_idLEFT JOIN RSCatering.dbo.tbl_menu_item mi ON oi.menu_item_id =mi.menu_item_idLEFT JOIN RSCatering.dbo.tbl_item i ON mi.item_id = i.item_idLEFT JOIN RSCatering.dbo.tbl_order_item_sides side ONoi.order_item_id = side.order_item_idLEFT JOIN RSCatering.dbo.tbl_item sidei ON side.item_id =sidei.item_idWHERE ord.deleted_flag = 0 AND oi.deleted_flag = 0ORDER BYts.rpt_type_desc,srd.mtg_start_date_local,srd.mtg_ end_date_local,r.res_descDROP TABLE #tmp_schedGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO************************************************** ****************************************The simplified result looks like:Sched2 Resource1 Supply1 Order5Sched2 Resource1 Supply1 Order6Sched2 Resource1 Supply3 Order5Sched2 Resource1 Supply3 Order6Sched2 Resource2 Supply1 Order5Sched2 Resource2 Supply1 Order6Sched2 Resource2 Supply3 Order5Sched2 Resource2 Supply3 Order6However, I want the result to look like:Sched2 Resource1 Supply1 Order5Sched2 Resource2 Supply3 Order6Any suggestion is greatly appreciated.
View 6 Replies View RelatedStored Procedure Returns Different Data When You Run It In T-SQL
Dec 13, 2007I have a stored procedure
Code Block
CREATE PROCEDURE WEA_SelectEmployeeListByCourseOrProject
@ProjectID int,
@CourseID int,
@blnIsSearch int,
@strUserName nvarchar(20)
AS
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @strRole nvarchar(15),
@ContactID int
SELECT @strRole = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName
Select DISTINCT Contact.ContactID ID, UPPER(Surname + 'gd ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR
, UPPER(ISNULL(ContactReference,'')) ContactReference
FROM Contact
LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID
WHERE RUEmployee=1
AND
( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID))
AND
(@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID)
AND
(
(@strRole = 'MIS_TUTOR' AND
(AssignedEmployee.ContactID = @ContactID
OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1)
OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID))
)
OR
(@strRole <> 'MIS_TUTOR')
)
SET CONCAT_NULL_YIELDS_NULL ON
GO
now if i run this stored procedure in Query Analyzer like so...
exec wea_SelectEmployeeListByCourseOrProject 0,0,1,'K_T'
i get 48 records returned.
but if i lift the SQL out of the stored procedure and run it in Query Analyzer like so....
Code Block
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @ProjectID int,
@CourseID int,
@blnIsSearch int,
@strUserName nvarchar(20)
SET @ProjectID = 0
SET @CourseID = 0
SET @blnIsSearch = 1
SET @strUserName = 'K_T'
DECLARE @Role nvarchar(15),
@ContactID int
SELECT @Role = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName
PRINT @ContactID
Select DISTINCT Contact.ContactID ID, UPPER(Surname + ' ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR
, UPPER(ISNULL(ContactReference,'')) ContactReference
FROM Contact
LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID
WHERE RUEmployee=1
AND
( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) -- the above line was modified to make sure only employees explicitly assigned to a project are brought back. unless it's a search
AND
(@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID)
AND
(
(@Role = 'MIS_TUTOR'
AND ( (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1)))
OR
AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID)
)
OR
(@Role <> 'MIS_TUTOR')
)
SET CONCAT_NULL_YIELDS_NULL ON
i only get 5 records returned???
so why do i get a difference when its the same SQL??
Username 'K_T' is of role 'MIS_TUTOR' therefore @Role = 'MIS_TUTOR'
any help on unravelling this mystery is appreciated!
Cheers,
Craig
Calling SQL 2005 Stored Procedure That Returns A Value
Apr 14, 2007I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.
I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).
I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.
I am calling the SP as follows:
Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val) Dim result As String Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString Using con As New System.Data.SqlClient.SqlConnection(cs) con.Open() Dim cmd As New System.Data.SqlClient.SqlCommand() cmd.Connection = con cmd.CommandType = Data.CommandType.StoredProcedure cmd.CommandText = "CreateLogEntry" cmd.Parameters.Add("@ChID", Data.SqlDbType.Int) cmd.Parameters("@ChID").Value = ID cmd.Parameters.Add("@Value", Data.SqlDbType.Int) cmd.Parameters("@Value").Value = val result = cmd.ExecuteNonQuery().ToString End Using End Sub
I have tried amending the ExecuteNonQuery line to ExecuteReader()
Any help appreciated
Regards
Clive
Calling A Stored Procedure That Does An Insert And Returns One Value
Feb 3, 2004Hi !
I have trying to do an insert with a subroutine that calls a stored procedure, but it doesn’ t run. The page loads properly but nothing is inserted in the table of the database, no errors appears after submit the form.
<asp:Button id="SubmitButton" OnClick="Send_data" Text="Submit" runat="server"/>
Here is the code:
Sub Send_data(Sender As Object, E As EventArgs)
Dim CmdInsert As New SqlCommand("new_user1", strConnection)
CmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = CmdInsert
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "User_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters.Add(New SqlParameter("@e_mail", SqlDbType.varchar, 50, "e_mail"))
CmdInsert.Parameters("@e_mail").Value = mail.Text()
End Sub
What it lacks?
Calling A Stored Procedure, Returns Empty
Nov 30, 2005hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing
dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
OLE DB Command And Stored Procedure That Returns Value And/or Error
May 23, 2006Guys,
could someone please tell me : am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ
Stored Procedure That Returns The Next Value In Sequence For A Column
Mar 9, 2008 I have a Column called SaleID in some tables in a Database.The SaleID column has the int datatype. I need a stored procedure that returns the next value in sequence for the SaleID column.
For Example,
If the last value inserted into the SaleID column was 1022 the stored procedure should return 1023
If the last value inserted into the SaleID column was 1023 the stored procedure should return 1024.
If the last value inserted into the SaleID column was 1024 the stored procedure should return 1025.
Also an exclusive lock should be maintained while the the stored procedure is running.
I am using SQL Server 2005.
SqlDataSource With Stored Procedure With Parameters Returns No Data
Mar 21, 2007I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand.
Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed.
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
Help Getting My If Statement To Reconize That My Stored Procedure Returns DBNULL
Mar 31, 2008Ok well i have a stored procedure that returns an integer between 1 and 5. My problem now is i want to check to see if the table will return NULL and if so i don't want to assign the value to my variable otherwise it'll trow an error. My code is listed below but i keep getting the error "Conversion from type 'DBNull' to type 'Integer' is not valid." i've also tried If getoptionpicked.Parameters("@optionpicked").Value = Nothing ThenIf getoptionpicked.Parameters("@optionpicked").Value Is system.dbnull Then below is the rest of the code If getoptionpicked.Parameters("@optionpicked").Value Is Nothing Then Else optionpicked = getoptionpicked.Parameters("@optionpicked").Value If optionpicked = 1 Then option1.Checked = True ElseIf optionpicked = 2 Then option2.Checked = True ElseIf optionpicked = 3 Then option3.Checked = True ElseIf optionpicked = 4 Then option4.Checked = True ElseIf optionpicked = 5 Then option5.Checked = True Else End If End If
View 1 Replies View RelatedHow To Speed Up Stored Procedure That Returns 35,000 Plus Rows Of Data?
May 8, 2008I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows. It's a super simple query that returns a client list. It's just that there is soooooo many rows, it's super slow when the page loads.
View 4 Replies View RelatedValid Stored Procedure Returns Error In ASP.net Application
Jan 17, 2005I have a stored procedure that works when executed in query analyzer. (It is also way too long to post here) When called from my application ado.net returns the error:
Invalid object name #idTable
If I run the proc in query analyzer using the same parameters (copied from quickwatch while debugging) there is no error.
While very complicated, this procedure runs quickly so timing out is not an issue.
Does anyone know why a proc would run in query analyzer and not in an asp.net/c# application?
Thank you.
DataRead Of Stored Procedure Returns Column Name Instead Of Null
Jan 6, 2006I have a stored procedure like "select * from ThisTable"
I'm doing a dataread like:
Dim val as String = dateRead("column_from_ThisTable")
If the value in the column is not null everything works great, but if the value is null, instead of getting a value of "" which I expect, I get the column name??? In this case "column_from_ThisTable"
How do I make sure I get "" returned when the value in the column is db.null?
Stored Procedure Returns Unnecessary Conversion Error
Oct 22, 2007The Query:
Code:
AS
DECLARE@UPLIDCount int
DECLARE @OldUPLIDCount int
SELECT @UPLIDCount = (SELECT Count(UPLID)/1000 AS adjcount
FROM tblProvLicSpecloc
WHERE DelDate is null
OR DelDate > GETDATE())
IF EXISTS(SELECT var FROM tblDMaxVars WHERE var = 'UPLID Count')
BEGIN
SELECT @OldUPLIDCount = (SELECT var FROM tblDMaxVars WHERE var = 'UPLID Count')
IF @UPLIDCount > @OldUPLIDCount
BEGIN
UPDATE tblDMaxVars
SET value = '' + CAST((@UPLIDCount*1000) AS nvarchar(1000)) + ''
WHERE var = 'UPLID Count'
END
END
ELSE
BEGIN
INSERT INTO tblDMaxVars (var, value, description)
VALUES ('UPLID Count', '' + CAST((@UPLIDCount*1000) AS nvarchar(1000)) + '', 'counts UPLID records and rounds down to the nearest thousand')
END
GO
The table tblDMaxVars only has three columns, none of which are integers, yet I still return this error:
Code:
Syntax error converting the varchar value 'UPLID Count' to a column of data type int.
Please help.
Data Binding To A Stored Procedure That Returns Two Result Sets
Mar 6, 2007Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset
B) Build a “pager� of some sort using the values from the first resultset
Problem In Executing Stored Procedure With Temp Table Returns Result Sets
Mar 23, 2006Hi,
we are facing problem in executing a stored procedure from Java Session Bean,
coding is below.
pst = con.prepareStatement("EXEC testProcedure ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}
same sp working perfectly with SQL Server 2000
am getting a error message
com.microsoft.sqlserver.jdbc.SQLServer
Exception: A server cursor cannot be opened on the given statement or statements
. Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure
@countrCode varchar(3)
as
select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU
Its really very urgent. Please help me...!
Rgds,
Venkatesh.
Java Code To Retrieve Data From Stored Procedure Which Returns Cursor Varying Output?
May 11, 2015java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies View RelatedUsing Stored Procedure Records
Jun 2, 2008How can I use the data returned from a stored procedure within another stored procedure?
For example, I trying to something along these lines:
select * from tbl_Test
union all
select * from (exec sp_Test)
Inserting Records Via Stored Procedure
Mar 23, 2006I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
Return A Set Of Records From A Stored Procedure
Nov 14, 2000What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?
Thanks ahead of time!
G.
Counting Records In A Stored Procedure
Jul 14, 2004I am trying to count records in my stored procedure. Can someone please help me.
these are the two procedures I am using
Alter Procedure usp_rptQualityReport As
SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))
ORDER BY
tblFailureReasons.FailureReason
Alter Procedure usp_rptQualityReport2 As
exec usp_rtpQualityReport
SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,
(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel
FROM usp_rptQualityReport;
Stored Procedure To Insert Records Into Next Row
Sep 20, 2014Creating a stored procedure to insert records into multiple rows.
Let's say we have 3 colums in procedure to insert, col3 has a max limit of 1000, if col3 data exceeds limit it has insert into next row and vice versa.
Stored Procedure For Archiving Records
Mar 27, 2008I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved
1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column
Pls provide me with full stored procedure code.
C.R.P RAJAN
Get Records After Executing A Stored Procedure
Dec 1, 2006Hi All,
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
Am I missing something here ? Please Advice
Thanks in Advance
Sp Returns Records In Qa, But Not In Adp
Dec 3, 2001I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".
To repeat, the sp runs fine in QA, just not in access.
Any thoughts, anybody? Many thanks. David
Alter Procedure spDailyMicroLabel
@Date Datetime = null
AS
IF @Date IS NULL
BEGIN
SET @Date=getdate()
SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters
SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay
END
Stored Procedure For Archiving The Records In Another Table
Mar 27, 2008I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved
1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column
Pls provide me with full stored procedure code.
Stored Procedure Producing Duplicate Records
Feb 11, 2004Hi,
I have written the following stored procedure:
alter proc GetProducts
@prodcatint=null
as
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
Does anybody have any suggestions.
Thank you in advance,
poc1010