Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015
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)
If 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?
Hello 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
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
' 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
Hi, 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.
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 .
I 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
I 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.
Query: SELECT m.MemberID, vw.Category, vw.Type, FROM dbo.TestVW vw JOIN dbo.TestMember m ON vw.MemberKey = m.MemberKey WHERE vw.Type = 'test' GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.
SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date FROM tblVehicles WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To]) AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
Hello all, I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be? I have a SqlDataSource" <asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)" UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure"> <SelectParameters> <asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="Action" Type="Byte" DefaultValue="2" /> </UpdateParameters> </asp:SqlDataSource> And my SP:/* 09-19-07 Used to update affiliate profile */
CREATE PROCEDURE spUpsertAffiliateProfile @Action tinyint, @AffiliateId int, @MemberId int = -1, @CategoryId int, @WebAddress varchar(50), @Email varchar(50), @Comments varchar(1500) AS
SET NOCOUNT ON
-- Find errors first, check is not needed if deleting IF @Action <> 3 IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%') BEGIN SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END IF @Action = 1 IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId) BEGIN SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id RETURN END
IF @Action = 1 AND @AffiliateId = 0-- insert BEGIN INSERT INTO tAffiliateMaster (member_id, category_id, profile_web_address, profile_email_1, comments) VALUES (@MemberId, @CategoryId, @WebAddress, @Email, @Comments)
SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id RETURN END
ELSE IF @Action = 2 AND @AffiliateId > 0-- update BEGIN UPDATE tAffiliateMaster
SET category_id= @CategoryId, profile_web_address=@WebAddress, profile_email_1=@Email, comments=@Comments
WHERE affiliate_id = @AffiliateId AND member_id = @MemberId
SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id RETURN END
ELSE IF @Action = 3 AND @AffiliateId > 0-- delete BEGIN DELETE tAffiliateMaster
WHERE affiliate_id = @AffiliateId
SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id RETURN END GO
My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event? Thanks!
So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that).
(Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this:
where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol] ( @vCat NVARCHAR(4000) ) RETURNS @PList AS BEGIN SELECT @PList += N', [' + [ColumnName] +']' FROM [ETL].[TableDef] WHERE [IsActive] = 1 AND [Category] = @vCat RETURN; END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
If I try to run the code below, and even one of the values in the INNER JOIN statements is NULL, the DataReader ends up with zero rows. What I need is to see the results even if one or more of INNER JOIN statements has a NULL value. For example, if I want info on asset# 2104, and there's no value in the DriverID field, I need the rest of the data to display and just have the lblDriverName by blank. Is that possible?
<code> Sub BindSearchGrid() Dim searchUnitID As String Dim searchQuery As String searchUnitID = tbSearchUnitID.Text lblIDNum.Text = searchUnitID searchQuery = "SELECT * FROM Assets " & _ "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _ "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _ "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _ "INNER JOIN AssetCategories ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _ "INNER JOIN Store ON Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '" & searchUnitID & "'"
Dim myReader As SqlDataReader myReader = Data.queryDB(searchQuery) While myReader.Read If Not IsDBNull(myReader("Store Name")) Then lblStrID.Text = myReader("Store Name") If Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text = myReader("AssetCategory") If Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text = myReader("Condition Description") If Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text = myReader("DepartmentName") If Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit ID") If Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year") If Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make") If Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model") If Not IsDBNull(myReader("Mileage")) Then lblMile.Text = myReader("Mileage") If Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text = myReader("Vin Number") If Not IsDBNull(myReader("License Number")) Then lblLicNum.Text = myReader("License Number") If Not IsDBNull(myReader("Name")) Then lblDriverName.Text = myReader("Name") If Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text = myReader("DateAcquired") If Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text = myReader("DateSold") If Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text = myReader("PurchasePrice") If Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text = myReader("NextSchedMaint") If Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR") If Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW") If Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text = myReader("Crane Capacity") If Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text = myReader("Crane Certification") If Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text = myReader("Repair Cost") If Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text = myReader("Estimate Replacement") If Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text = myReader("SalvageValue") If Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text = myReader("CurrentValue") If Not IsDBNull(myReader("Comments")) Then lblCom.Text = myReader("Comments") If Not IsDBNull(myReader("Description")) Then lblDesc.Text = myReader("Description")
I 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?
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 have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID, A.ANI, A.PID, B.RD FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
I 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!
I 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?
Can 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}
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.
I have the following stored procedure, to insert or update a record and return the id field, however the procedure returns two results sets, one empty if it's a new record - is there a way to supress the empty results set?
ALTER PROCEDURE [dbo].[AddNode] @Name VARCHAR(15), @Thumbprint VARCHAR(40), @new_identity [uniqueidentifier] = NULL OUTPUT AS BEGIN UPDATE dbo.NODES
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 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.
