How To Return Multiple Values From Stored Procedures
Jun 22, 2007How to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies
ADVERTISEMENT
Return Distinct Values From Stored Procedures
Aug 17, 2006I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies View RelatedHow Stored Procedures Return Multiple Result Sets?
Jan 28, 2007hi
i read that stored procedures can return multiple result sets?how is that?
thanks in advance.
Multiple Return Values From Stored Procdure.
Feb 1, 2007I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?
View 4 Replies View RelatedJDBC: Calling A Stored Procedure With Multiple Return Values.
Jul 23, 2005Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies View RelatedTransact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015I 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)
How can I code a stored procedure to do this?
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems 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?
View 3 Replies View RelatedMultiple Return Values
Jun 2, 2004I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
Return Multiple Values From A Function
Jun 19, 2007searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?
e.g., I want to do this:
select id, data, whatever, dbo.fnMyFunction(id, whatever) from table
and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction
possible? easier way? thanks in advance...
Return Multiple Values In 1 Row Sep Column
Mar 20, 2014I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.
So my result should look something like this:
EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child
How do I do this with SQL?
How To Return Multiple Table Values From A Function
May 5, 2004Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End
This Works Fine :-
And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @cnt Int
Set @cnt = 1
While @cnt <= 2
If @cnt =1
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @cnt = @cnt + 1
End
If @cnt =2
Begin
Insert @k2
Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @cnt2 = @cnt2 + 1
End
End
Return
End
Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.
Thanks.
How To Return A Table With Stored Procedures?
Jun 20, 2007i have a procedure like below but it dosen't return any tableSELECT Hardwares.HWID, Hardwares.Title
FROM Hardwares INNER JOIN
Category ON Hardwares.CategoryID = Category.CategoryID
WHERE (Category.Type LIKE '%Hardware%') AND (Category.Title = @Title)
SQL Server 2012 :: Return Values With Multiple Entries Only
May 9, 2014Very basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
Stored Procedures - How To Return ERRORS To VB.NET Code
Jun 6, 2008Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
View 2 Replies View RelatedStored Procedures Return Ing Mutiple Rows
Jul 29, 2006hello. I am trying to speed up my asp.net caledar.
I need a Stored Procedure that takes a Parameter.
the Parameter will be a Date.
then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar.
the table looks like this
classid
classname
classdate
classtime
(there are more than one class happening on the same day)
I need the retuen value to look like this
"Yoga 9:00am" & "<p>" & "Jazz 11:00am" & <p>
can someone help me or point me to a sample. this is really hard. thank you
SQL 2005 Stored Procedures Return No Rows
Sep 19, 2006I am trying to execute a simple SELECT statement in a stored procedure from within Visual Studio 2005. The procedure executes but returns no rows. (There are rows in the table and a VIEW using the same SELECT statement does return the rows.)
I added a PRINT statement to the stored procedure to see if it executed and it did print my text in the output window as expected.
The SQL server is running on Windows Server 2003 with latest service packs and patches. I had detached the database while doing maintenance on our system and re-attached it later.
I can not find any reason why it refuses to return rows.
Larry
Question On Return Codes And Rollback For Stored Procedures.
Oct 19, 2001I need to execute 10 stored procedures one after the other in a sequence,but need to roll back if any one of them fails and discontinue further execution.Example: if sp#3 fails it should roll back alll the changes made by sp# 1 and sp# 2 and also should not continue executing the rest of them. Any ideas?
Thanks.
Stored Procedures - Using Return Value From Dynamically Created SQL Statement
Jul 29, 2007Hello,
This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.
I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:
Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.
If I hard code a statement, like
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).
But if I modify the dynamically created SQL Statement from earlier on to:
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".
Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)
So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?
Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures
Oct 30, 2005Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function
Return Values With Stored Procedure
Jun 4, 2004Hello Group
I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.
Thank you
Michael
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 ),
@fldFullName varchar( 75 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM [tbUsers]
Where fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO
code
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim strFullName As String
' Variable Declaration
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
' Set conn equal to the conn. string we setup in the web.config
sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
myConn = New SqlConnection(sqlConn)
' We are going to use the stored procedure setup earlier
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
' Set the default return parameter
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
' Add SQL Parameters
myCmd.Parameters.Add("@fldUsername", strUsername)
myCmd.Parameters.Add("@fldPassword", strPassword)
myCmd.Parameters.Add("@fldFullName", strFullName)
' Open SQL and Execute the query
' Then set intResult equal to the default return parameter
' Close the SQL connection
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
Session("strFullName") = strFullName
myConn.Close()
Response.Write(strFullName)
' If..then..else to check the userid.
' If the intResult is less than 0 then there is an error
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
' Return the userid
Return intResult
End Function
Please Help... Stored PRocedure Return Values
May 9, 2008Hi,
I am trying to get the combination of results in my stored procedure, I am not getting what I need. Following are the things which I need to return from my stored proc.
1. I need to select distinct categories and their record count
2. I also need to select the records from the table.
3. Need to send both category, record counts and records.
First is it possible in stored procedure?
Following is helpful information.
Data in tables looks like this.
prod id, prod_name, prod_category
1, T shirts, Mens Apparel
2 , Shirts, Mens Apparel
3 , Pants , Mens Apparel
4, Tops , Women Wear
5, Bangles, Women Wear
And in User Interface I need to show like this.
Mens Apparel (3)
1 T Shirts
2 Shirts
3 Pants
Women Wear (2)
4 Tops
5 Bangles
Please help me if there is any way to return the complete data structure using stored procedure. If I do something in java code, I can get this, but I am trying to get directly from stored procedure only.
Thanks in advance...
Chandrasekhar
Return Stored Procudure Values
Nov 5, 2005I have a quick question for you about SQL stored procedures. If I'min a stored procedure and want to call another stored procedure andreturn values from the second stored procedure what is the procedure?I know you do the following to run the second stored procedure and passin any parameters:EXEC GetAuthorBooks @AuthorIDSo if I wanted the GetAuthorBooks to return all the books for an authorand then populate a temp table in the original stored procedure, how doI return those records, and populate the temp table?What do I have to add to this line: EXEC GetAuthorBooks @AuthorID?
View 4 Replies View RelatedHow To Return More Than Two Values From A Stored Procedure..
Aug 13, 2007
Hi,
I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.
How can I do that. I'm able to get only 1 value if i use the return key word.
Eg:
create proc test1 as
Begin
Declare scount int
Declare scount2 int
-- statements in stored procedure
return scount
return scount2
End
create proc test2
Declare variables...
Exec Test1
// here i want the values (scount and scount2 ), processed in stored procedure Test1 .
How can i get this.. please let me know..
Thanks,
srikanth
Return Values From A Crosstab Stored Procedure
Jul 20, 2005I came across an article in SQL Mag about Crosstab Queries. It worksgreat in Query Analyzer, but I'm stuck on how to use it in an AccessADP. I need to use it as a Recordsource in a form and report. Cansomeone tell me how to use it, and please try to be as descriptive aspossible. I'm new to Stored Procedures.Thanks*****************************************CREATE PROC sp_CrossTab@table AS sysname, -- Table to crosstab@onrows AS nvarchar(128), -- Grouping key values (on rows)@onrowsalias AS sysname = NULL, -- Alias for grouping column@oncols AS nvarchar(128), -- Destination columns (on columns)@sumcol AS sysname = NULL -- Data cellsASDECLARE@sql AS varchar(8000),@NEWLINE AS char(1)SET @NEWLINE = CHAR(10)-- step 1: beginning of SQL stringSET @sql ='SELECT' + @NEWLINE +' ' + @onrows +CASEWHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsaliasELSE ''ENDCREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)DECLARE @keyssql AS varchar(1000)SET @keyssql ='INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @tableEXEC (@keyssql)DECLARE @key AS nvarchar(100)SELECT @key = MIN(keyvalue) FROM #keysWHILE @key IS NOT NULLBEGINSET @sql = @sql + ',' + @NEWLINE +' SUM(CASE CAST(' + @oncols +' AS nvarchar(100))' + @NEWLINE +' WHEN N''' + @key +''' THEN ' + CASEWHEN @sumcol IS NULL THEN '1'ELSE @sumcolEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS c' + @keySELECT @key = MIN(keyvalue) FROM #keysWHERE keyvalue > @keyENDSET @sql = @sql + @NEWLINE +'FROM ' + @table + @NEWLINE +'GROUP BY ' + @onrows + @NEWLINE +'ORDER BY ' + @onrows-- PRINT @sql + @NEWLINE -- For debugEXEC (@sql)GO
View 6 Replies View RelatedTransact SQL :: Stored Procedure Return Values
Sep 23, 2015If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer? Is that simply the success flag?
View 5 Replies View RelatedStored Procs, Error Handling And Return Values
Sep 24, 2007Can anyone let me know the prefered method for handling stored procedure errors. I usually trap the error in the stored proc and then return a value using an output parameter
e.g stored proc
if @@error <> 0 beginset @returnValue = -1returnend
c#com.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;con.Open();com.ExecuteNonQuery();
int result = (int)com.Parameters[0].Value;
if (result == -1){//throw exception}else{//do whatever}
Passing Null Values To Stored Procedures
Aug 29, 2006Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies View RelatedProblem Returning Two Values From Stored Procedures
Jan 29, 2004Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS
declare @TillId int
declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO
Proper Use Of Null Values In Stored Procedures
Oct 25, 2000If I have a stored proc which has 2 input variables and for example
the 2nd one is null or not provided what is the best way to turn this null
into a wildcard. If the second one is an int and not provided should I
but a checknull statement in the proc and convert this to a wildcard %. I believe if I do I need to change the where clause "where field1 = @var "
to where field1 like @var". Any input appreciated
Comma Separated Values To Stored Procedures
Jul 4, 2006Hi All,i hv created a sp asCreate proc P @iClientid varchar (100)asBeginselect * from clients where CONVERT(VACHAR(100),iClientid) in(@iclientid)endwhere iclientid = int data type in the clients table.now if i pass @iclientid as @iclientid = '49,12,112'but this statement throws an conversion error ( int to char error).is there any way to fetch records from a select statement using astring???Thanks in Advance.
View 3 Replies View RelatedReturn Column Names Where Values Were Not Found [stored Procedure]
Sep 5, 2004I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
Retrieving Scalar Or Calculated Values From Stored Procedures With C#
Feb 4, 2007I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
View 5 Replies View RelatedReturn Multiple Resultsets From Stored Procedure
Mar 11, 2008I have read a lot in favor and recommendation of returning multiple resultsets.
But now I have to implement it with a scenario.
I have a Parent Table named "Books" and a Child one named "Volumes".
A book can have multiple volumes.
Now I want to display the list of Books with their Volumes pagewise.
How can I implement that procdure.