[T-SQL] List of DataBase Name for associated Table used in Stored Procs. |
- List of DataBase Name for associated Table used in Stored Procs.
- Comparing row counts and displaying results from greatest row count from a single table
- Error in creating function
- trigger is working for manual insert and not working for application isnerts
| List of DataBase Name for associated Table used in Stored Procs. Posted: 27 Aug 2013 01:40 AM PDT Hi,I want to list down the table name and which database it belongs which are used in the Stored Procedure.for exmple:if my Stored proc has two tables tbl1 and tbl2 and db2.tbl1 and db1.tbl2 then i want to list down those tables and Database name.Plz suggest thnx. |
| Comparing row counts and displaying results from greatest row count from a single table Posted: 26 Aug 2013 01:47 PM PDT insert into CallData ([800num], CompanyName) values ('8009874321', 'cars');insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');insert into CallData ([800num], CompanyName) values ('8009874321', 'Newsales');insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales');insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');insert into CallData ([800num], CompanyName) values ('8009870000', 'BenaSales2');Requirement 1: When the 800num matches I would like to return a single row, the row with the greatest row count. Requirement 2: If the 800num row count matches then return both rows.Results would be:8009874321 Newsales8009870000 BenaSales8009870000 BenaSales2 |
| Posted: 26 Aug 2013 07:15 PM PDT Hi all,I have created a function but I am getting an error:Cannot perform alter on 'dbo.fn_CLCDeviationRequestDetails' because it is an incompatible object type.Here is the code below:Can you please suggest whats wrong in this?[code="sql"]Alter function [dbo].fn_CLCDeviationRequestDetails( @DevDateChanged DATETIME)RETURNS @temptable TABLE ( ProjectID INT, ProposalID INT, SolutionID INT, UnitID INT, DeviationNO nvarchar(128), IsRevisedRequest Bit, DeviationStatus INT, RequestDate DATETIME, RequestBy nvarchar(55), RepliedBy nvarchar(55) )ASBEGIN ;WITH CTE AS ( SELECT DISTINCT D.ProjectId, D.ProposalId, D.SolutionId, D.UnitId, D.RequestId, D.DeviationNo, I.IsRevisedRequest, D.DeviationStatus, D.RequestDate, D.RequestedBy, D.RepliedBy FROM DeviationRequestDetails D JOIN CLCProcessUnitDetailsCompany C ON C.ProjectID = D.ProjectId AND D.ProposalId = C.ProposalID AND C.SolutionID = D.SolutionId AND C.UnitID = D.UnitId JOIN DeviationRequestDetailsInstallation I ON C.ProjectID = I.ProjectId AND I.ProposalId = C.ProposalID AND C.SolutionID = I.SolutionId AND C.UnitID = I.UnitId WHERE C.DevDateChanged >= @DevDateChanged ) INSERT INTO @temptable SELECT TOP 1 C.ProjectId, C.ProposalId, C.SolutionId, C.UnitId, C.DeviationNo, C.IsRevisedRequest, C.DeviationStatus, C.RequestDate, C.RequestedBy, C.RepliedBy FROM CTE C ORDER BY C.RequestId DESC RETURN END[/code] |
| trigger is working for manual insert and not working for application isnerts Posted: 26 Aug 2013 01:13 PM PDT Hi , trigger is working for manual inserts .. andnot working as expected for application inserts .. please helpwhen there is a row from application , 3 columns are copying to new table and forth column is showing as null.alter trigger copytableon dbo.maintableafter insertasbeginset nocount ondeclare @id nchar(32)declare @type nchar(10)select @id=(id) from insertedselect @type=(type) from insertedif (@country='A)begininsert into dbo.w_table1 select * from insertedendelse if (@type='B')begininsert into dbo.table2 select * from maintable where id=@idendend |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment