Stored Procedure That Return Dataset(Select Query)
Oct 31, 2006
Hi all,
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem. Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to. Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e) {GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C"); }
I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop). I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset:
ALTER PROCEDURE dbo.GetVal AS
Declare @fname varchar(50) Declare @lname varchar(50) Declare @grname varchar(100) Declare @isgroup int Declare @id int Declare @ListName varchar(200)
DECLARE guests_cursor CURSOR FOR SELECT id, fname, lname, grname, b_isgroup FROM guests
OPEN guests_cursor
-- Perform the first fetch. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS =0 BEGIN if (@isgroup=1) Select @grname+'('+@lname+', '+@fname+')' as ListName else Select @lname+', '+@fname as ListName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS] GO /****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT ee.EmployeeID, EmployeeName = LastName + ', ' + FirstName FROM EmployeeInvData ee --UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
hi ng,i am newbie to sqlserver and my problem seems simple, but i didn't findinformation about it:How can i display theRETURN @xvalue of a stored procedure in the sql analyzer of the sqlserver?thanks a lot,d
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
Hi All, I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
Is there a way to do a SELECT TOP # using a variable for the #? In other words I'm doing a SELECT TOP 50* FROM DATATABLE If I pass an @value for the number SELECT TOP @value* FROM DATATABLE doesn't work I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have. Thanks in advance.
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As BEGIN BEGIN TRAN T1 SET NOCOUNT ON SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate(); UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id; SELECT * FROM #TempTable; DROP Table #TempTable; COMMIT TRAN T1 END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) { List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure; try { Command.CommandTimeout = CONNECTION_TIMEOUT;
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
Hello and thank you for taking a moment to read this message. I am simply trying to use a stored procedure to set up a dataset. For some reason when I try to fill the dataset with the data adapter I get the following error: Compiler Error Message: BC30638: Array bounds cannot appear in type specifiers.Line 86: ' Create the Data AdapterLine 87: Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) my code looks as follows for the dataset:<script runat="server">Sub ListSongs() ' Dimension Variables in order to get songs Dim myConnection2 as SQLConnection Dim myCommand2 as SQLCommand Dim intID4 As Integer 'retrieve albumn ID for track listings intID4 = Int32.Parse (Request.QueryString("id")) ' Create Instance of Connection myConnection2 = New SqlConnection( "Server=localhost;uid=jazz***;pwd=**secret**;database=Beatles" ) myConnection2.Open() 'Create Command object Dim mycommand2 AS New SQLCommand( "usp_Retrieve song_",objCon) mycommand2.CommandType = CommandType.StoredProcedure mycommand2.Parameters.Add("@ID", intID4) ' Create the Data Adapter (this is where my code fails, not really sure what to do) Dim objadapter As SQLDataAdapter(mycommand2, myconnection2) 'Use the Fill() method to create and populate a datatable object into a dataset. Table will be called dtsongs Dim objdataset As DataSet() objadapter.Fill(objdataset, "dtsongs") 'Bind the datatable object called dtsongs to our Datagrid: dgrdSongs.Datasource = objdataset.Tables("dtsongs") dgrdsongs.DataBind()</script><html><head> <title>Albumn Details</title></head><body style="FONT: 10pt verdana" bgcolor="#fce9ca"><center> <asp:DataGrid id="dgrdSongs" Runat="Server" ></ asp:DataGrid> </center></body></html> Any help or advice would be greatly appreciated. Thank You - Jason
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.
In the data tab I'm currently using "text" for command type and :
exec sp_executesql @sql, N'@p_OfferStatus VARCHAR(100)', @p_OfferStatus = @p_OfferStatus when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?
I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.
Hi all, Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere. Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this. This is what I have so far:- 1 SqlDataSource dataSrc2 = new SqlDataSource();2 dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3 dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4 dataSrc2.InsertCommand = "reportData";5 6 dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7 8 int rowsAffected;9 10 11 try12 {13 rowsAffected = dataSrc2.Insert();14 } As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data. Any help is greatly appreciated. Slainte, Sean
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
I am trying to use a dataset for the first time and I've run into a roadblock early. I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard. The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box. I went through several of the stored procedures and this is the case for all of them. The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up. Very frustrating, any thoughts? Thanks in advance, N
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
Hi, Can anyone please help me solve this problem. My functions works well with this stored procedure: CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO But when I applied the same function to the following stored procedure CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO I received this message Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error:
Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter(); Line 34: sqlDA.SelectCommand = sqlComm; Line 35: sqlDA.Fill(dataSet); Line 36: Line 37: return dataSet; The function I am using is returning a DataSet as below: public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue) { DataSet dataSet = new DataSet(); SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString()); SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect); sqlComm.CommandType = CommandType.StoredProcedure;
for (int n=0; n<paramName.Count; n++) { sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n])); }
SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = sqlComm; sqlDA.Fill(dataSet); return dataSet; } If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure? Thanks.