Transact SQL :: Adding Constraint For Business Logic

May 27, 2015

ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT
     ReqdCovgAmt_constraint33 CHECK     
( case 
  when CovgAmt = 0 and  ReqdCovgAmt = 0 then 1
  when CovgAmt = 0 and  ReqdCovgAmt = 1 then 1
  when CovgAmt = 1 and  ReqdCovgAmt = 0 then 1
  when CovgAmt = 1 and  ReqdCovgAmt = 1 then 0
end =1
)

This is my first attempt to add a constraint for business logic.  The desired behavior is that the two columns together have the same behavior as a radio button.  (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.

The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".

So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?

View 14 Replies


ADVERTISEMENT

Transact SQL :: Adding A Column To A Large (100 Million Rows) Table With Default Constraint?

Apr 24, 2013

IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END

It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.

View 4 Replies View Related

Business Logic File Needs Something...

Feb 26, 2008

Or maybe it can't find something it's looking for to make my additional symetrical with everything else. This is from one of my businesslogic files.  What causes Visual Studio to not allow me to add something here and have it behave like the other parameters/  I'm talking about ShortDesc.  Visual studio inserted () and the Get instead of GET like all the others.  It would not allow me to capitalize the "get". There has to be a reason for this. Currently my program is adding all of the fields except the ShortDesc to the table in my database. Does anyone have any thoughts as to why this is the case?
 
 #Region "Pvt Members"        Private _ArticleID As System.Int32        Private _UserID As System.Int32        Private _WebSiteID As System.Int32        Private _ArticleTitle As System.String        Private _Author As System.String        Private _ShortDesc As System.String        Private _ArticleText As System.String        Private _AnchorText As System.String        Private _ShowInDirectory As System.Boolean        Private _Active As System.Boolean        Private _DateAdded As System.DateTime#End Region#Region "Properties"        Public Property ArticleID As System.Int32            GET                Return _ArticleID             End Get             Set(ByVal Value As System.Int32)                _ArticleID= Value             End Set         End Property         Public Property UserID As System.Int32            GET                Return _UserID             End Get             Set(ByVal Value As System.Int32)                _UserID= Value             End Set         End Property         Public Property WebSiteID As System.Int32            GET                Return _WebSiteID             End Get             Set(ByVal Value As System.Int32)                _WebSiteID= Value             End Set         End Property         Public Property ArticleTitle As System.String            GET                Return _ArticleTitle             End Get             Set(ByVal Value As System.String)                _ArticleTitle= Value             End Set         End Property         Public Property Author As System.String            GET                Return _Author             End Get             Set(ByVal Value As System.String)                _Author= Value             End Set         End Property        Public Property ShortDesc() As System.String            Get                Return _ShortDesc            End Get            Set(ByVal value As System.String)            End Set        End Property        Public Property ArticleText As System.String            GET                Return _ArticleText             End Get             Set(ByVal Value As System.String)                _ArticleText= Value             End Set         End Property         Public Property AnchorText As System.String            GET                Return _AnchorText             End Get             Set(ByVal Value As System.String)                _AnchorText= Value             End Set         End Property         Public Property ShowInDirectory As System.Boolean            GET                Return _ShowInDirectory             End Get             Set(ByVal Value As System.Boolean)                _ShowInDirectory= Value             End Set         End Property         Public Property Active As System.Boolean            GET                Return _Active             End Get             Set(ByVal Value As System.Boolean)                _Active= Value             End Set         End Property         Public Property DateAdded As System.DateTime            GET                Return _DateAdded             End Get             Set(ByVal Value As System.DateTime)                _DateAdded= Value             End Set         End Property #End Region

View 1 Replies View Related

Business Logic In Store Procedure

Apr 5, 2006

Hey guys,I have to import a csv file to my database and then add
some values to other fields based on these values. At present I use a
store procedure that does a bulk insert and then I use an update and
fetch inside the sql to perform these updates. I am not sure if this is
the best way. Is this bad, since some amount of busniess logic is in
the store procedure?If it is, how can I do this better?Thanks for the answer.

View 1 Replies View Related

Business Logic Handler Not Loading

Nov 7, 2006

I'm trying to implement a custom conflict resolver by inheriting from

Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule.  The replication is between a SQL Server 2005 Express subscriber and a SQL Server 2005 publisher/distributer.

The problem is that the resolver class in the DLL will not load; although, it does appear to find the DLL.  (If I rename the DLL I get a "file not found error").  The exact error message is:


Microsoft.SqlServer.Replication.ComErrorException

 "Error loading custom class "MergeConflictHandler" from custom assembly "MergeConflictHandler",

Error : "Could not load type 'MergeConflictHandler' from assembly 'MergeConflictHandler, Version=1.0.2502.22393, Culture=neutral, PublicKeyToken=0403e50cc4dc27fa'."."} 

I placed the DLL in the directory of the program that calls SynchronizationAgent.Synchronize and tried it with and without being registered in the GAC.  There was no change.  Interestingly, when I move the file out of that location, but register it in the GAC, the file is not found.  Thinking it may be a security problem, I gave Everyone Read & Execute and Read privileges.  I believe the class is actually instantiated by replmerge.exe, an apparantly unmanaged app, so I tried marking it ComVisible.  No Luck.
I registered the resolver with the following T-SQL code:


sp_registercustomresolver @article_resolver = 'eClinical Notes Conflict Resolver'

, @is_dotnet_assembly = 'true'

, @dotnet_assembly_name = 'MergeConflictHandler'

, @dotnet_class_name = 'MergeConflictHandler'

, @resolver_clsid = Null
The resolver I've created is not much more than a shell at this point.  It is pasted below, but I tried using the code found on MSDN character-for-character and had the same problem.

using System;


using System.Text;

using System.Data;

using System.Data.Common;

using Microsoft.SqlServer.Replication.BusinessLogicSupport;

namespace MergeConflictHandler

{


public class MergeConflictHandler :

Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule

{


// Variables to hold server names.

private string m_PublisherName;

private string m_SubscriberName;

private string m_Artical_Name;

public MergeConflictHandler()

{

}

// Implement the Initialize method to get publication

// and subscription information.

public override void Initialize(string publisher, string subscriber, string distributor, string publisherDB, string subscriberDB,string articleName)

{


m_PublisherName = publisher;

m_SubscriberName = subscriber;

m_Artical_Name = articleName;

}

// Declare what types of row changes, conflicts, or errors to handle.

public override ChangeStates HandledChangeStates

{


get

{

return ChangeStates.UpdateConflicts;

}

}

public override ActionOnUpdateConflict UpdateConflictsHandler(DataSet publisherDataSet, DataSet subscriberDataSet, ref DataSet customDataSet, ref ConflictLogType conflictLogType, ref string customConflictMessage, ref int historyLogLevel, ref string historyLogMessage)

{


if (m_Artical_Name == "PatientAllergies")

{

// Accept the updated data in the Subscriber's data set and apply it to the Publisher.

}

return ActionOnUpdateConflict.AcceptPublisherData;



}

}

}

 
Does anyone have any thoughts or advice?  (Help!)

View 4 Replies View Related

Cannot Debug 'business Logic Handler'

Jul 10, 2006

hi,

i'm following steps described at: http://msdn2.microsoft.com/en-us/library/ms365150.aspx

all goes fine until step 6. of paragraph "To debug a business logic handler on a Web server using Web synchronization, or for a SQL Server Mobile Subscriber".

in my case when attaching to w3wp.exe i can only debug t-sql or native code. when i forcibly check 'managed' i get: "Unable to attach to the process. Access is denied". error picture:

http://img156.imageshack.us/my.php?image=debuggingerror7bp.jpg

i KNOW that the business logic handler gets loaded and executed because i am logging some info in it. i would be grateful for any suggestions.



TIA, kamil nowicki

View 3 Replies View Related

Stored Procedure Business Logic

May 21, 2008

I am working with stored procedures to provide conditional business logic for a customer based on triggers. Is it possible for a stored procedure to execute an external program ie opening or distributing a crystal report?

View 9 Replies View Related

Business Logic Handler Question

Oct 25, 2007

Hi,

I am performing the Merge Replication of Timesheet table between SQL server 2005 and SQL CE on the Pocket PC. For one of the article, I would like to perform web synch for only those rows which have certain field set to true.

I am trying to use Business Logic Handler to implement this logic. I am using following code out of sample on MSDN web site. However I am not sure if this code gets executed for each row for a table during synch or does this get executed once for each table? The answer would help me write logic on applying synch changes only for few rows.

Thanks

public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
ref string historyLogMessage)
{
if (updateSource == SourceIdentifier.SourceIsPublisher)
{
//check the approved status of the timesheet
if (updatedDataSet.Tables["tbl_timesheets"].Rows[0]["ts_approved"] == true)
{
// Set the reference parameter to write the line to the log file.
historyLogMessage = updatedDataSet.Tables["tbl_timesheets"].Rows[0]["ts_id"].ToString();
// Set the history log level to the default verbose level.
historyLogLevel = 1;

// Accept the updated data in the Subscriber's data set and apply it to the Publisher.
return ActionOnDataChange.AcceptData;


}

else
{
return ActionOnDataChange.RejectData;

}



}
else
{
return base.UpdateHandler(updateSource, updatedDataSet,
ref customDataSet, ref historyLogLevel, ref historyLogMessage);
}
}

Any Help is appreciated.

Thanks

View 1 Replies View Related

SqlDataSource? What Happened To The Separation Of UI And Business Logic?

Mar 16, 2007

I guess I just don't get the reasoning behind the new SqlDataSource control.  Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET?  In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display?  It doesn't make sense.  Why would anyone do this?  And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?

View 4 Replies View Related

Business Logic Handler Called In Which Direction?

May 17, 2007

When running a business logic handler at the subscriber, you can override SubscriberInserts, SubscriberUpdates and SubscriberDeletes. Are these methods called when data is changed coming from the server to client, from the client to the server, or in both directions?



Thanks,

Bryan

View 1 Replies View Related

Running Custom Business Logic During Synchronization

Oct 30, 2006

I have a merge replication topology that allows some subscribers (clients) to retreive a number for certain columns (ie invoice number, order number, etc).

I have implemented a custom business logic handler to check for inserts from the subscriber and before updating the server, I call a stored proc on the server to generate a new number. I update the data set and call ActionOnDataChange.AcceptCustomData. This works great for the publication db but I am trying to get this new number back into the subscription db. I am using web synchronization so I really don't have a connection back to the subscriber database.

Any Ideas? I was thinking about keeping track of the updates myself and build a sql string and somehow get that back to the subscriber to run updates for those columns... I am wondering if I am pushing the limits of the business logic handler? Was it designed to do this? Of course the easy answer is to modify my client application but I am trying to avoid that at this point.

Any help/suggestions would be greatly appreciated!



View 6 Replies View Related

Multiple Tables In Business Logic Handler

Oct 25, 2007

Hi,

I have 2 related tables in SQL 2005. I am using Business Logic Handler on one of those tables during Merge Replication to reject data for few rows during web synchronization. Once a row is rejected, I would like to run the Business Logic Handler for the related table to reject changes for the row with same foreign key as the rejected row in first table.

Not sure if I need to create separate Business Logic Handlers for each table or can it be achieved in a single Handler.

Also is it possible to get a list of rejected rows back from the handler?

Thanks for help.

View 1 Replies View Related

Custom Business Logic Handler Question

Mar 1, 2007

I am currenly have a simple merge replication topology. The publisher-distibutor is SQL Server 2005 and the subscriber is a SQL Server Express. The type of subscription is non anonymous pull.

I made a custom business logic handler class that is trying the following:

When a new record is inserted in a published table on the pubsliher, some additional records are added in a differenct table in subscriber. Then the "InsertHandler" method returns "AcceptData" in order to allow the agent to add the new record in current table also.

So I am establishing a new connection to the subscriber and I am trying to add the additional recs in the different table. The problem is that this different table is also published as read-only on the subscriber. So my insert fails saying that table is not updatable.

Is there any way to bypass this problem?

In fact I realised in general that when my custom logic handler performs some DML operations on the subscriber, these are NOT considered as part of the replication e.g. the NOT FOR REPLICATION constraints and triggers are active for these operations.

Is this normal?

View 1 Replies View Related

Business Logic In Stored Proc VS Aspx.vb Page

Jul 20, 2005

Hello,I stuck in a delimma.Where to put the business logic that involves only one updatebut N number of selects from N tables........with N where conditions

View 5 Replies View Related

Merge Replication – Bug In Business Logic Update Handler

Nov 17, 2006

I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.

Please can someone confirm if this is indeed a bug €“ and if it is a known bug?

My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.

I can quite happily generate a custom dataset which contains the winning row that I want €“ I can see that because I can step through the conflict handler with debug when a conflict occurs.

However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.

I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update €“ updating all the modified columns. This now works reliably in my testing.

Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the €˜unnecessary€™ update.

Notes:

I am using column level tracking €“ but I have seen the problem with row level tracking too
I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP €“ and the problem occurs there too
The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber

Any thoughts would be much appreciated

View 5 Replies View Related

Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?

Jul 20, 2005

What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg

View 1 Replies View Related

Issues About Deploy Business Logic Handler To A Web Server For Web Replication.

Jul 23, 2007

Pocket PC 2003, SQL Compact Edition, SQL2005, IIS6.0



I implemented a business logic handler to deal with conflicts. When I deploy it on the SQL server which is also the web replication server, the logic handler seems working fine. However, if I deploy this handler to another web server. The logic handler failed to be loaded.

My enviroment settings are desribed as below.



Machine A, distributor, with database and publication. The business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll. It's registered by using sp_registercustomresolver. The @assembly is specified as @assembly=C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll';



Machine B, IIS server. The same business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll on the Machine B itself.



When I ran the web replication, the Merge Agent reported the error as below.

Error loading custom assembly "C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll", Error: "Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll' or one of its dependencies. The given assembly name or codebase was invalid.



It seemed that the Merge Agent had trouble to find my logic handler because the path reported in the error log has two backward slashes. I have no idea where did that came from. I am not sure if that's the cause of the error. Without business logic handler. I had successfully finished web replication of Machine B to sync with Machine A. If I setup web replication directly on Machine A with business logic handler, I can successfully sync as well.



Does anyone has any idea about how to correctly deploy business logic handler on a web server?



Thanks,

Nigel

View 1 Replies View Related

Data Access :: Bulk Fetch Records And Insert / Update Same In Other Table With Some Business Logic

Apr 21, 2015

I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some  business logic? How do i do this?

View 14 Replies View Related

Enforcing Precedence Constraint While NOT Failing Package(On Failure Logic Ignored)

Aug 1, 2007

I have designed an SSIS package that :

(1) contains a for loop task (in which all the logic is contained) that loops through a particular folder for excel files
WITHIN THE FOR LOOP:
(2) pulls data from an excel file into SQL Tables(Data Transformation Task)
(3) run stored proc to validate data (Execute SQL TASK)
(4)ON SUCCESS of executing the SQL Task (Script Task - move file to success or reject folder based on value returned from sproc)
(5)ON FAILURE of executing the stored proc (Script Task - move file to bad format failure)

NOTE:
I have modified the MaximumErrorcount property of (1 FOR LOOP) and (3 EXECUTE SQL TASK) and the package itself to 0.
In order to deal with badly formatted excel files...I do not want the package to stop for every missing tab in excel file or data entry error. I simply want the badly formatted file to be moved to a special folder

PROBLEM:
on failure logic is never executed (I have 2 options after step (3)) on success do step (4) on failure do step (5)
step (3) fails...then it simply iterates to the next file
step 5 is never executed

Is this because I changed the maximumerrorcount property?
What am I doing wrong witht he Precendence Logic?

View 4 Replies View Related

Logic For Adding Dates

May 23, 2004

If I use dateadd(dd,n,@fromdate), it gives me the result including the weekends, is there a way to find the target date without including weekends. Please help.

View 6 Replies View Related

Adding Secondary Axis To Charts In Business Intelligence

May 15, 2008



Hi Guys

Can I use secondary axis in chart item ? I need to plot two different items in a single chart. They have a common x axis but they differ hugely in terms of their values. For ex one item is the Amount of time in minutes and the other item is the total no. of people .But they need to be plotted on a single graph for business needs.
Any ideas??


Cheers

View 3 Replies View Related

Adding A Constraint To A View

Apr 2, 2008

I have created a SQL view of several tables, and need to add either a primary key or a constraint to this view. I need it for some full text indexing that will be performed on the view. How do I do it?

Thanks in advance :)

View 4 Replies View Related

Need Help Adding Foreign Key Constraint

Dec 13, 2007

Here is my sqlce:




Code Block

alter table tblV
add constraint
foreign key (T_ID)
references tblT(T_ID)
on delete set null on update cascade
Error is:
Major Error 0x80040E14, Minor Error 25501
There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = foreign ]

I'm new with SQL Mobile, so sorry for the novice question.

Thanks

View 5 Replies View Related

How To Tell SQL Server To Use The Default Constraint Name When Adding One?

Oct 26, 2005

Hi guys,

I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.


ALTER TABLE TableTest
ADD CONSTRAINT PRIMARY KEY (C1)


Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".

If I add a name to the constraint, it works fine.
I'm using SQL Server 2000

Thanks
Darkneon

View 7 Replies View Related

SQL 2012 :: Adding A Constraint To A Table

Sep 16, 2015

Select MemberNbr, EligYear,EligMonth, count(*) FROM [MemberMonth]
GROUP BY MemberNbr, EligYear,EligMonth HAVING COUNT(*) > 1

THIS WILL GIVE ME ZERO ROWS,,, That is GOOD which means NO DUPLICATES..I need to add a constraint to the table MEMBERMONTH to make sure no one can insert duplicate records.

MemberNbr VARCHAR,
EligYear INT ,
EligMonth INT

View 1 Replies View Related

Adding A Column With Foreign Key Constraint

Oct 1, 2007



Hello!


I am trying to alter a table adding a column with foreignkey constraint. The table that holds the primary key in that relation has data, so i am not being able to execute the alter statement.

This is the error:


Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_MOPAeroportoICAO_IDMOPAlimentacaoFerroviaTipo'. The conflict occurred in database 'MOP', table 'MOPAlimentacaoFerroviaTipo', column 'IDMOPAlimentacaoFerroviaTipo'.

This is the script:

ALTER TABLE MOPAeroportoICAO
ADD IDMOPAlimentacaoFerroviaTipo [int] DEFAULT 0 NOT NULL
CONSTRAINT [FK_MOPAeroportoICAO_IDMOPAlimentacaoFerroviaTipo] FOREIGN KEY
REFERENCES MOPAlimentacaoFerroviaTipo (IDMOPAlimentacaoFerroviaTipo)

How can i turn of check when i add the foreign key ?

Thanks in advance

View 1 Replies View Related

Transact SQL :: Deleting Records Based On Certain Logic

May 7, 2015

There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same

PID,EDate,CP,RelativePNum,XrefCode,CPD 
PID,EDate,CP,RelativePNum,XrefCode,CPD 

In the above screenshot, row 1 and row 2 have same

So i want to write a query which deletes row 2 records, and keep row 1,row 3 and row 4 records. This i want to do in the whole table.

create
table AB
(
PID
int,
tTimeStamp
datetime,
EDate 
varchar(40),

[Code] ....

View 12 Replies View Related

Adding A Default Constraint To An Existing Column

Mar 9, 2000

I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :

ALTER TABLE table_name WITH NOCHECK
ADD CONSTRAINT column_name DEFAULT (0)

This gives me a syntax error.

The column was originally added with a default constraint of 1 to a 2.6 million row table.
I dropped the existing constraint and need to add the new default constraint of 0 for that column.

Anyone have any ideas? Thanks in advance.

View 2 Replies View Related

Transact SQL :: Managing First Application Of MERGE Statement In ETL Logic

Apr 20, 2015

In order to feed a fact table of a dwh from a staging table I'm using the MERGE statement in order to control insert and update operations. It is possible that the staging table has duplicate rows respect to the fields controlled in the merge condition:

When I run the first time the MERGE statement unwanted rows could be inserted in the fact table.

Does the MERGE statement allow to manage this case or do I need to filter data from the staging table before to write them into the fact table?

View 4 Replies View Related

Transact SQL :: Create Search With Boolean Logic And Wildcard Characters

Jun 15, 2015

I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented? 

View 3 Replies View Related

Transact SQL :: Creating Comma Separated Value Logic Not Working Inside Function?

Jul 16, 2015

I am need to create comma separated list in sql and I am using below query.

declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString

When I use the above code inside a function, i am not getting desired output.

create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)

View 3 Replies View Related

Transact SQL :: Triggers - Pass INSERTED / DELETED Logical Tables To Function To Encapsulate Logic?

Jun 13, 2015

I would like to wrap the following code in a function and reuse it.  I use this code in many triggers.

DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U'  -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I'  -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D'  -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".   
END)

Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?

View 5 Replies View Related

Transact SQL :: Calendar Table - Get The Date Post 3 Business Days

Sep 10, 2015

I have a calendar table against entire year 2015 with each day with 2 flag,

1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"

DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')

We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.

I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved