Help Retrieving Values From 2 Similar Stored Procs
May 1, 2006
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
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 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.
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution. A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure. I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP) SELECT @MessageCode AS MessageCode, @MessageText AS MessageText I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset. In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened. Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
I'm generating a list of parameters needed by stored procedures, and I'd like to know which ones have default values assigned to them. To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value, 'COLUMN_DEF' always returns as NULL, even when that column has a default value assigned to it. i.e. CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd) begin print 'Date Lies Between Boundary Limits' select * from membership where uid=@uid end else begin if(@pipe=1) begin if(@plan_id=1) begin print 'Monthly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid select * from membership where uid=@uid end else begin print 'Weekly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid select * from membership where uid=@uid end end end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City 21 X PA Chn 2 Sathish PA Chn 3 Shiva A Cbe 17 Venkat M Hyd 22 Y SM Cbe 18 Vignesh SA Hyd
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?
Or is there another way for me to do this?
This is what I have so far...What am I missing that this is not showing me results?
Select Firstname, Lastname, Region From Employees WHERE Region LIKE 'null%';
Hi - I'm new to SSRS/SQL and have a situation I can't figure out. I've tried a number of things, but I'm not sure the best way to return a calculated column based on the structure of my tables.
I have a primary Detail table that tracks product testing results. The Detail records need to be calculated against a "Year" table that contains a set of values by year. This table contains a single record for each year. There isn't a direct key that joins these two tables.
The Detail table looks like: Record ID: 9999 PO Number: 12345 Date: 12/1/2007 Test Result: 1.52
The Year table looks like: Record ID: 1 Start Date: 10/6/2007 End Date: 6/14/2008 LowVal1: 1.0 HighVal1: 1.49 Incentive1: .05 LowVal2: 1.50 HighVal2: 1.59 Incentive2: .06 and so on...
The Detail records need to find the correct Year record based on the Detail date, find the correct value within the LowVal - HighVal range based on the Test Result value, and then multiply the Test Result by the correct Incentive value and return that value.
I was able to find the correct LowVal-HighVal range using an Expression in the report column. Now that I need multiple years, I can't figure out the best way to configure the query.
Any suggestions would be appreciated. Thanks in advance!
( select min(cast(f.valuestr as float)) from bvfindings f where f.ObjectName = 'Hematocrit' and f.sessionid = s.sessionid and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)), f.valuetime from bvfindings f where f.ObjectName = 'Hb'
Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.
Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.
Is there any way to compare two similar databases (A & B) stored procedure. I have to find stored procedure in second database B with respect to the difference.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
First off, this is a cross post which is also located here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND GO
CREATE FUNCTION RECTANGULAR_XSECTION (@fWidth float, @fHeight float)
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
The Above is my database, I need help in retrieving the X_Co and the Y_Co using values of rcv_A, rcv_B and rcv_C to compare with the Mean_A, Mean_B, Mean_C. The values of rcv_A, rcv_B and rcv_C are instances of values that are not exact of the mean columns , and we want is to compare it against our database and retrieve the row that is the closest to the rcv_A, rcv_B and rcv_C.
Here is an example of what i need. Let's say my rcv_A = 71, rcv_B = 73 and rcv_C = 70.8, so the row with mean value closest would be row 1, followed by row 2, then row 3.
So the result i hope to retrieve is in order of the closest value and i only need the X_Co and Y_Co. This is what i want
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
( --define the parameters that are needed @userID char(32), @userName varchar(50), @status char(10), @type char(10), @password varchar(50), @firstName varchar(100), @lastName varchar(100), @email varchar(200), @domain varchar(50), @pwdExpiry int, @badLogin int, @lastLogin datetime, @full varchar(8000), @read varchar(8000), @noaccess varchar(8000) ) AS BEGIN TRAN tmp1 --First, insert the user into the system INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate) values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user declare @arrayValue char(32) declare @rightID char(32) declare @sepPos int while patindex('%,%',@full)<>0 BEGIN select @sepPos = patindex('%,%' , @full) select @arrayValue = left(@full, @sepPos - 1) -- replace the value with an empty string select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id declare @strID char(32),@tmpStr varchar(40) set @tmpStr = newid(); set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write') if(@rightID IS NOT NULL) --insert the records that are full access INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID) VALUES(@strID, @arrayValue, @rightID, @userID) else RAISERROR(50100,15,1) END COMMIT TRAN tmp1 IF @@TRANCOUNT > 0 RAISERROR(50101,15,1) ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo ( @VendorNum nvarchar(24) ) AS
SELECT P.ProductID AS ProductID, P.ProductShortName AS ProductName, P.ProductDesc AS ProductDesc, U.UnitDesc AS Unit, P.VendorProductNumber AS VendorNumber, V.VendorName AS VendorName, P.Price AS Price, P.ImageThumb AS ImageThumb
FROM tblProducts AS P INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE V.VendorsVendorNo = @VendorNum AND P.Inactive = 0
ORDER BY ProductName, VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name from store (server A) where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine. Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime exec serverB.dbo.sp_max_date_from_store @maxdate select store_name from store --(server A) where date_created >= @store
Any help or suggestions would be greatly appreciated! trevorb
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors) Management is looking at stored procs to improve performance, but I am wondering if the db is in this state will there really be a gain. I am pushing for normalization first, but if anybody has any ideas or opinions I would appreciate
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
