Stored Procedure Times Out
Hello,
I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference.
Could anyone help with making the query a bit more effiecient? SELECT DISTINCT
webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname,
webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename,
IpToCountry.COUNTRY AS CountryName
FROM webStats LEFT OUTER JOIN
IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN
searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN
browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN
os ON webStats.useragent LIKE '%' + os.agentstring + '%'
WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE())
ORDER BY webStats.datestamp DESCThanks for your help.
Bart
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Running Stored Procedure Multiple Times
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this? newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam) ownrParam.Value = OwnerID colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close() colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close() and so on for 9 columns…
View Replies !
View Related
Identifying Results Sets When Stored Procedure Called Multiple Times
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
View Replies !
View Related
Calling Stored Proc. Multiple Times...
I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set. I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction. Solution 1: Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work. Solution 2: Use a temp table or table variable. The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create. Any help is very much appreciated.
View Replies !
View Related
How To Compare Two Times That Are Stored In DateTime Datatype??
I have a few queries in Access97 that need to be converted to stored procedures on an SQL Server 7. It looks like (this is shortened): .... WHERE (((resourcestable.AvailableFromDate)<Date()) AND ((resourcestable.AvailableToDate)>Date()) AND ((resourcestable.AvailableFromTime)<Time()) AND ((resourcestable.AvailableToTime)>Time())) .... In access97 it's possible to compare the time that is stored in the AvaialleFromTime-column with TIME() (which is the time at the moment right now). SQL Server doesn't have any similar TIME()-function as I've seen. I wonder how I could compare the times (without dates) to see if the time that is stored in the table is smaller or bigger than the time right now. I.e. if 12:00:00 is bigger or greater than 08:00:00. Any help is greatly appreciated! sincerely Bob Nachbar
View Replies !
View Related
Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc
Hi, I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this. We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index. Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement? Thank you, Michael Tzoanos
View Replies !
View Related
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all, I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE): (1) /////--spTopSixAnalytes.sql--/// USE ssmsExpressDB GO CREATE Procedure [dbo].[spTopSixAnalytes] AS SET ROWCOUNT 6 SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName FROM LabTests ORDER BY LabTests.Result DESC GO (2) /////--spTopSixAnalytesEXEC.sql--////////////// USE ssmsExpressDB GO EXEC spTopSixAnalytes GO 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) sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure '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.
View Replies !
View Related
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
View Related
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View Replies !
View Related
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View Replies !
View Related
System Stored Procedure Call From Within My Database Stored Procedure
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense. How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how? Thanks in advance
View Replies !
View Related
Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio What could explain this? Obviously, All three scenarios are executed against the same database and hit the exact same tables and indices. Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000). The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
View Replies !
View Related
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Hi all, I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server. Whenever I tried to right click stored procedure and select step into store procedure> i get following error "User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information" I am not sure what needs to be done on sql server side We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master. Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean? Please advise.. Thank You
View Replies !
View Related
Sql Count Using Stored Procedure Withing Stored Procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks
View Replies !
View Related
SQL Stored Procedure Issue - Search Stored Procedure
This is the Stored Procedure below -> SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/ /****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/ CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT) AS DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000) IF (@i_WildCardFlag=0)BEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END PRINT @SQLString SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)' EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric. REQUIREMENT: The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID. Please help me regarding the same. Thanks in advance. Sandeep Kumar
View Replies !
View Related
Calling Stored Procedure Fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View Replies !
View Related
Exec Twp Stored Procedure In A Main Stored Procedure
Hi there i have a stored procedure like this: CREATE PROCEDURE SP_Main AS SET NOCOUNT ON BEGIN TRANSACTION exec SP_Sub_One output exec SP_Sub_Two output IF @@ERROR = 0 BEGIN -- Success. Commit the transaction. Commit Tran END ELSE Rollback Tran return GO now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation. now i can put the "begin transation" in the two sub stored procedure's. The problem is what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two" now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the "SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it. Thanks in advance Mahmoud Manasrah
View Replies !
View Related
Execute Stored Procedure From Stored Procedure With Parameters
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC Any assistance would be greatly appreciated. Thank you, Dave
View Replies !
View Related
Calling A Stored Procedure Or Function From Another Stored Procedure
Hello people, When I am trying to call a function I made from a stored procedure of my creation as well I am getting: Running [dbo].[DeleteSetByTime]. Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous. No rows affected. (0 row(s) returned) @RETURN_VALUE = Finished running [dbo].[DeleteSetByTime]. This is my function: ALTER FUNCTION dbo.TTLValue ( ) RETURNS TABLE AS RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true' This is my stored procedure: ALTER PROCEDURE dbo.DeleteSetByTime AS BEGIN SET NOCOUNT ON DECLARE @TTL int SET @TTL = dbo.TTLValue() DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime) END CreatedTime is a datetime column and TTL is an integer column. I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it. Your help is much appreciated.
View Replies !
View Related
Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View Replies !
View Related
Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View Replies !
View Related
SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure: Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON; Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End oDataSource.ConnectionString = constr; oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span> oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId); gdvCategories.DataSourceID = oDataSource.ID; gdvCategories.DataBind(); <<--- Error occured here
View Replies !
View Related
Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql? ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error... Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24 Error converting data type varchar to numeric. ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point... EXEC [dbo].[sp_SearchCandidatesAdvanced] @LicenseType = 4, @PositionType = 4, @BeginAvailableDate = '10/10/2006', @EndAvailableDate = '10/31/2007', @EmployerLatitude = 29.346675, @EmployerLongitude = -89.42251, @Radius = 50 GO ------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced] @LicenseType int = 0, @PositionType int = 0, @BeginAvailableDate DATETIME = NULL, @EndAvailableDate DATETIME = NULL, @EmployerLatitude DECIMAL(10, 6), @EmployerLongitude DECIMAL(10, 6), @Radius INT AS SET NOCOUNT ON DECLARE @v_SQL NVARCHAR(2000) DECLARE @v_RadiusMath NVARCHAR(1000) DECLARE @earthRadius DECIMAL(10, 6) SET @earthRadius = 3963.191 -- SET @EmployerLatitude = 29.346675 -- SET @EmployerLongitude = -89.42251 -- SET @radius = 50 SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' + 'Round(' + @v_RadiusMath + ', 0) AS Distance ' + 'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' + 'WHERE ' + @v_RadiusMath + ' <= ' + @Radius IF @LicenseType <> 0 BEGIN SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType END IF @PositionType <> 0 BEGIN SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType END IF LEN(@BeginAvailableDate) > 0 BEGIN SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate END --SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance' PRINT(@v_SQL) EXEC(@v_SQL) -----------------------------------------------------------------------------------------------------------------
View Replies !
View Related
Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; } As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help! Thanks Nickdel68
View Replies !
View Related
Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same. ddave
View Replies !
View Related
Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View Replies !
View Related
Running Stored Procedure Within A Stored Procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or function 'aspnet_Users_DeleteUser' expects parameter '@UserName', which was not supplied. public static bool DeleteUser(int userID, string username) {int result = 0;using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString)) { SqlCommand myCommand = new SqlCommand("Users_DeleteUser", myConnection);myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.AddWithValue("@UserID", userID);myCommand.Parameters.AddWithValue("@UserName", username); try { myConnection.Open(); result = myCommand.ExecuteNonQuery(); myConnection.Close(); }catch (Exception ex) {throw new Exception("There was a problem attempting to connect to the database to delete the record. {0}", ex); } }return result > 0; }
View Replies !
View Related
Stored Procedure. The C# Code Run Stored Procedure.
CREATE PROCEDURE GetThreadMessages @iThreadID int AS SELECT message_id, thread_id, user_id, first_names, last_name, email, subject, body, date_submitted, category_name, category_id, last_edited FROM message_view WHERE thread_id = @iThreadID ORDER BY date_submitted asc For update ---------------------------------------------------------------------------------------- CREATE PROCEDURE UpdateThreadMessages @iThreadID int @name varchar(50) AS UPDATE message_view SET first_names = @name FROM message_view WHERE thread_id = @iThreadID Regards Sandesh For Delete ------------------------------------------------- CREATE PROCEDURE DeleteThreadMessages @iThreadID int AS DELETE FROM message_view WHERE thread_id = @iThreadID Regards Sandesh stored procedure. The c# code run stored procedure. help
View Replies !
View Related
Execute Stored Procedure From Stored Procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw
View Replies !
View Related
Calling Another Stored Procedure Within A Stored Procedure?
Hi, I have a while loop stored procedure, I need to send email for each item in the loop using a sendemail stored procedure. I have two question .. 1) I use EXEC PRODUCTION.DBO.SENDMAIL 'email@hotmail.com', 'Start', 'Job Start' in side the while loop stored procedure, but i didn't get any email or error msg. Why? 2) I try to move a file .. how do I find out if the moving is completed successfully? -- MOVE FILES SET @CMD = 'MOVE /Y ' + '"' + @ORIGINAL_FILE + '"' + ' "' + @MOVE_FILE + '"' EXEC master.dbo.xp_cmdshell @CMD Thanks for your reply in advance.
View Replies !
View Related
Call Stored Procedure From Another Stored Procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure?
View Replies !
View Related
Using Stored Procedure Result Set In Another Stored Procedure
I've been developing a stored procedure that uses a user definedfunction in the query portion of the procedure. However, since the endproduct needs to allow for dynamic table names, the UDF will not work.I've been trying to get this to work with converting the UDF to aprocedure, but I'm having no luck.Here is the background on what I'm trying to accomplish. I need toperform a sub-identity on a table, I have the normal identity set, butthere are multiple duplicates in the table and I need each set ofduplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup2).Here is what I have using the UDF (the UDF returns a variable tablewith indetity and ID for each record)********** UDF *************CREATE FUNCTION dbo.setDuplicateTransactions(@accountNumber asvarchar(50))RETURNS @dupTransactions TABLE(ID int IDENTITY,transactionID int)ASBEGININSERT @dupTransactionsSELECT t1.transactionIDFROM providerTransactions t1WHERE t1.accountNumber = @accountNumberORDER BY t1.transactionIDRETURNEND******** Stored Procedure ************CREATE PROCEDURE dbo.sp_parseTransactionsASDECLARE@accountNumber varchar(50)DECLARE temp_cursor CURSOR FORWARD_ONLY FORSELECT t1.accountNumberFROM providerTransactions t1GROUP BY t1.accountNumberHAVING MAX(isNull(t1.duplicateCount,0)) != COUNT(t1.transactionID)ORDER BY t1.accountNumberOPEN temp_cursorFETCH NEXT FROM temp_cursorINTO @accountNumberWHILE @@FETCH_STATUS = 0BEGINUPDATE providerTransactionsSET duplicateCount = t1.IDFROM setDuplicateTransactions(@accountNumber) t1,providerTransactions t2WHERE t1.transactionID = t2.transactionIDFETCH NEXT FROM temp_cursorINTO @accountNumberENDCLOSE temp_cursorDEALLOCATE temp_cursorNow this does work and accomplishes what I want. But as mentioned, Ineed to make the tables dynamic which will require the UDF to beeliminated and either the UDF portion moved to the stored procedure orcreate another stored procedure to be called by this one.I've tried moving the UDF into the procedure, however, the identitiesdid not reset on the subsequent loops. If there is a way to reset thetable variable, that would be a big help.I did move the UDF to a stored procedure, then tried to tie it into themain procedure.**** UDF as Stored Procedure ******CREATE PROCEDURE dbo.sp_setDuplicateTransactions@accountNumber as varchar(50)ASDECLARE @dupTransactions TABLE(ID int IDENTITY,transactionID int)BEGININSERT @dupTransactionsSELECT t1.transactionIDFROM providerTransactions t1WHERE t1.accountNumber = @accountNumberORDER BY t1.transactionIDENDHere is the snippet of code replacing the current UPDATE query.DECLARE @dupTransactions sysnameEXECUTE @dupTransactions = sp_setDuplicateTransactions @accountNumberEXEC('UPDATE providerTransactionsSET duplicateCount = t1.IDFROM ' + @dupTransactions + ' t1, providerTransactions t2WHERE t1.transactionID = t2.transactionID')When I run the main stored procedure I get the following error when Istop running it.(1 row(s) affected)Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '0'.(1 row(s) affected)Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '0'.(1 row(s) affected)Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '0'.(1 row(s) affected)So any help would be greatly appreciated.Thanks,Marc
View Replies !
View Related
Scheduling A Job Using A Stored Procedure Through A Stored Procedure
I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time. I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?
View Replies !
View Related
Using A Stored Procedure In A Inner Join Of A Stored Procedure
Hi, it is possible to execute a stored procedures inside of a inner join of another stored procedure? I have the problem, that I have a stored procedure which will return a resultset. This resultset should be used in a join of another stored procedure. It is possible or not? I tried something like that, but it doesn't work for me: CREATE PROCEDURE usp_Person AS SELECT A.LastName, A.FirstName, B.Street FROM Table1 A INNER JOIN EXEC usp_Address B ON A.Id = B.Id GO I am thankful for a hint or a better way.
View Replies !
View Related
Calling A Stored Procedure Within A Stored Procedure
Hi folks, I created a stored procedure called td_calculated initially for testing in hopes to later create a function from it. Problem is I'm using a few temp tables which I later found out can't be used inside of a function. However I learned that it is possible to call a stored procedure within another one. I'm just a little fuzzy on the syntax Right now td_calculated accepts @case (int) parameter and just displays a single value @td(int). I'd like to know how to allow @td to be passed to another procedure and how to call this one. Can this be done within a column similar to a scalar function?
View Replies !
View Related
Calling A Stored Procedure From Within A Stored Procedure
I need to call a stored proc(SPB) from within a stored proc(SPA). I do not want to pass SPB's resultset out of SPA, but I want to do a select on it from within SPA and return a different result set. Below is an example of how I would like to do this, please let me know of any ideas you might have. Thanks, - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - declare @viewID uniqueidentifier declare @userID uniqueidentifier declare @ProjectID uniqueidentifier set @userID = (select memberGUID from member where username = 'karlb') set @viewID = (select pk_viewDefinitionId viewDefinitionId From ViewDefinition vd Join ViewDefinitionGlobal vdg on vd.fk_viewDefinitionGlobalId = vdg.pk_viewDefinitionGlobalId and vdg.viewDefinitionGlobalName = 'Admin - Organizational Units' and vd.fk_projectId = (select projectguid from project where projectname = 'Cleveland IAS Test')) /* Here is the point where I have the problem I think you can see what I would like to do, but I need to take only distinct rows and only certain columns */ Select @ProjectID AS fk_projectid, distinct(itemID), itemName, seqNum from(exec stoActionSelectIronCladTree @viewID, @userID, null,null,null,null,null,null)
View Replies !
View Related
Calling Stored Procedure From Stored Procedure
I have a simple stored procedure that inserts a record into a table that logs changes. My question is, if this procedure fails for whatever reason will the transaction be rolled back here and in the calling procedure? Thanks. ALTER PROCEDURE dbo.cdds_User_Profile_Log_Insert @UserId uniqueidentifier, @fieldNames varchar(200), @fieldValues varchar(200) AS BEGIN TRANSACTION INSERT INTO [cdds_User_Profile_Log](Date_Time,UserId,FieldNames,FieldValues) VALUES (GETDATE(), @UserId,@fieldNames,@fieldValues) IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION Block
View Replies !
View Related
Call Stored Procedure From Stored Procedure
I know this is probably simple but I am trying to call a stored procedure from another stored procedure. Basically this is so I can group lots of table updates into one overnight procedure to run. I don't need a record return using temp tables which is the only reference I seem to be able to find about stored procedures in stored procedures. I just want it to run. Tried the obvious: exec [UStudent_Marks_Filtered] Help much appreciated.
View Replies !
View Related
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
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?
View Replies !
View Related
|
TRACKER
