general comment is required

Hi,
I am trying to implement master detail kind of functionality. table1 has
primary key t1 and table2 has primary key t2. I have not placed any
relationship on these tables. these tables are free tables.
what will happen if I place foreign key will search sort become faster ?
assume data is perfect with/without relationship, so main purpose of having
foreign key relationship is almost equall to 0.
your comments are required.
Kishorkishor (kishor@.discussions.microsoft.com) writes:
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of
> having foreign key relationship is almost equall to 0.
You don't add foreign keys fort sorts to make faster (and I find it
difficult a imagine where an FK would affect the speed of a sort). You
add foreign-key constraints to enforce referential integrity.
If you have, say, an Orders and an OrderDetails table, the Order table
would have OrderID as its primary key, and OrderDeatails would have a
two-column of OrderID and some other column (e.g. RowNo or ProductID).
Furthermore OrderDetails would have a FK constraint to say that any
OrderID must be an existing OrderID in Orders. This constraint applies
both when inserting into OrderDetails and when deleting from Orders.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, there will be a difference in performance.
BOL states that a FK relationship b/w 2 tables indicates that the 2 tables
have been optimized to be combined in a query that uses the keys as its
criteria.
Ideally, u shud maintain an index on the FK columns for even better
performance of ur queries. I doubt if the index is maintained in the 2 cases
mentioned (with & without FK), will there be any speed difference or not?
Probably, not.
- R
"kishor" wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
>|||If a FK relationship makes sense there is always a benefit for using it
and keeping therefore referential integrity, but just using a FK does
NOT mean do use an index. FK can be also place on columns which aren=B4t
indexed, so you should be aware not to mix that up. But I hope that you
are in the design phase and not the reengineering phase, because your
thoughts should take place in the designing phase.
HTH, jens Suessmeyer.|||kishor wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
You say you assume the data is perfect but how will you enforce that
rule without a foreign key? If you enforce it only in your application
then you have to duplicate that integrity feature for each application
or data entry screen. A bug in any one place could affect all other
users of the data. When you come to write queries against the data, the
server won't see a referential integrity constraint so it won't be able
to use that constraint to help construct a better query plan.
Always declare the constraints that are required to enforce your
business rules.
David Portas
SQL Server MVP
--|||"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:D8FDB80A-F7E6-4600-8BED-B20A80CD5815@.microsoft.com...
> Hi,
> I am trying to implement master detail kind of functionality.
table1 has
> primary key t1 and table2 has primary key t2. I have not placed
any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become
faster ?
> assume data is perfect with/without relationship, so main purpose
of having
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
kishor,
Establishing a foreign key constraint is done for the purpose of
enforcing referential integrity. These constraints should always
been done where cardinality indicates it. Referential integrity is
one of the key features of an RDBMS, avoiding its use would be
substantially less than ideal (I would almost go so far as to say,
"Why have an RDBMS if you don't bother with referential
integrity?").
The foreign key constraint may be enforced by the use of an index
(in SQL Server, it usually is). Indexes can improve performance on
some types of queries (SELECT), and the query optimizer may or may
not use that index on those queries depending on *many* other
factors. Indexes may degrade performance on some types of queries
(UPDATE, INSERT, DELETE). But these considerations are entirely
beside the consideration of enforcing referential integrity.
RI provides for database consistency. Without it, your database
may, without any warning, enter an "inconsistent state". If you
encounter such an error in your database(quite likely without
enforced RI, IMO), I wish you luck in fixing it.
Sincerely,
Chris O.

Function Return Value

I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @.MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "\" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQL\Data" instead of "D:\MSSQL\Data" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

Any suggestions? Thank you.

HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

BEGIN

--Extract the file path for the database MDF physical file.
SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END

HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON

DECLARE
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.

SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATHAny suggestions?

YEah, rethink what you're doing...

MOO|||If you didn't have any information that would possibly be helpful in my question, please leave future posts to those who would be more intelligent in their responses.

If you see something that I am doing wrong, then why not offer a suggestion instead of either keeping the answer to yourself or acting more intelligent than what you are. After all, this is what this forum is intended for.

Thank you.|||Sorry you feel that way...

If you can supply us with what you're doing, I'm sure the people here can assist...if you don't like what I say, I'm sure someone will step...need more details though...

This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Any "AUTO-ADMIN" stuff is always risky...(my own opinion) MOO

Why do you have to do this? Are you releasing hundreds of databases?

Also, unless there are performance issues involved, why deviate from standard practices...

AND HOW DARE YOU ACCUSE ME OF BEING INTELLIGENT!

The nerve...|||CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

The return value of the function is declared as NVARCHAR, a single character. You need to declare the return value as a character array.

RETURNS NVARCHAR(1000)|||USE Northwind
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS

BEGIN

SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END
GO

SET NOCOUNT ON
DECLARE @.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATH
SET NOCOUNT OFF
GO

DROP FUNCTION fn_sqlmgr_get_mdf_filepath
GO|||Yeah .. but in spite of all the help from BK ... you need to rethink it anyway ;)|||I think that Brett saw you doing something potentially VERY dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP|||I think that Brett saw you doing something potentially [i[VERY[/i] dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP

You know the funny thing about this?

Trying to build a rocket ship, but can't get past retrun nvarchar|||And i dont remember exactly ... but isnt there a registry key we could read to get the default location where a file would be created ...

Hmmm ... what the hell I am thinking .. the files would be created int the default location anyway ... if you do not specify ... so whats with the function ... i am confused (once again ;))|||Thank you Homer37 for your assistance. Your answer worked just fine.|||Improper setting at the time of server installation leads to situations like this, where there is a need to write extra code. However, it also appears that you ARE trying to auto-create databases (I already picture a scene where something in other parts of your code got missed and you are sitting at a non-responsive server because your code ended up creating databases), unless I am misreading the post. To alter the default location you need to use xp_instance_regwrite rather than for every database to be created to reference the same server, following a call to xp_instance_regread.

function on heirarchy nodes

Hi
i have 3 master tables 1)RoleDetails(Roleid(PK),name,masterroleid(fk) ref:RoleDetails roleid)
2) PositionDetails(positionid(PK), name,MasterPositionid(FK) ref:PostionDetails postionid,Roleid(fk) ref:Roledetails roleid)
3) Userdetails(userid(pk), loginid,pwd,roleid(fk) ref:roledetails roleid,positionid(fk)ref:postionDetails positionid,fname,address)
how to Create two functions one return child nodes as per Case 1 and another one is return Parent Nodes
as per case 2
(Manager) a -- r1 (roledetails)
/ \

(ROL)a1 (ROL) a2 -- r2
/ | \ / | | \
(RO)b1 b2 b3 b4 b5 b6 -- r3
Case 1:
On passing the User ID of (a) , should get the Output as User id of ( b1,b2,b3,b4,b5,b6) along with their Role ID.

On passing the User ID of (a1), should get the Output as User ID of (b1,b2,b3)along with their Role ID.

Case 2:
On passing the Role ID of (R3), should get the User ID of all the Parent roles( a1 and a2 (R2), a(R1)long with their Role id
Case 3: on passing role id of child node , should get only all particular parent userid and roleid's.

thanks in adv.,
chakriPost the DDL of the tables.it is lot easier to help u.|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri

read this post first.http://www.dbforums.com/showthread.php?t=1196943|||CREATE TABLE [dbo].[PositionMaster] (
[PositionID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentPositionID] [bigint] NULL ,
[RoleID] [bigint] NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleMaster] (
[RoleID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentRoleID] [bigint] NOT NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserMaster] (
[UserID] [bigint] NOT NULL ,
[LoginID] [varchar] (100) COLLATE Latin1_General_CS_AS NOT NULL ,
[Password] [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
[RoleID] [bigint] NOT NULL ,
[PositionID] [bigint] NOT NULL ,
[Status] [bit] NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[LastName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[Gender] [varchar] (6) COLLATE Latin1_General_CS_AS NULL ,
[ContactNum] [bigint] NULL ,
[Address] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [PK_PositionMaster] PRIMARY KEY CLUSTERED
(
[PositionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [PK_RoleMaster] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [PK_UserMaster] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [FK_PositionMaster_PositionMaster] FOREIGN KEY
(
[ParentPositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_PositionMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [FK_RoleMaster_RoleMaster] FOREIGN KEY
(
[ParentRoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [FK_UserMaster_PositionMaster] FOREIGN KEY
(
[PositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_UserMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO|||if u ask for DML here is it..

Insert into RoleMaster values (1,'Admin','Adminstrator',1,1)
Insert into RoleMaster values (2,'Mgr','Manager',1,2)
Insert into RoleMaster values (3,'RoL','Recovery Operator Lead',2,3)
Insert into RoleMaster values (4,'RO','Recovery Operator',3,4)
select * from RoleMaster
go

Insert into PositionMaster values (1,'Mgr','Manager',Null,1,1)
Insert into PositionMaster values (2,'ROL1','Recovery Operator Lead 1',1,2,1)
Insert into PositionMaster values (3,'ROL2','Recovery Operator Lead 2',1,2,1)
Insert into PositionMaster values (4,'RO1','Recovery Operator 1',2,3,1)
Insert into PositionMaster values (5,'RO2','Recovery Operator 2',2,3,1)
Insert into PositionMaster values (6,'RO3','Recovery Operator 3',3,3,1)
Insert into PositionMaster values (7,'RO4','Recovery Operator 4',3,3,1)
Insert into PositionMaster values (8,'RO5','Recovery Operator 5',3,3,1)
Insert into PositionMaster values (9,'RO6','Recovery Operator 6',3,3,1)

go
select * from PositionMaster

Insert into UserMaster values (1,'Tom','Tom',2,1,1,'Tom','Hanks','m',Null,'Null' ,'Null')
Insert into UserMaster values (2,'Jim','Jim',3,2,1,'Jim','Ward','m',Null,'Null', 'Null')
Insert into UserMaster values (3,'Sandra','Sandra',3,3,1,'Sandra','Bullock','m', Null,'Null','Null')
Insert into UserMaster values (4,'Ross','Ross',4,4,1,'Ross','Magan','m',Null,'Nu ll','Null')
Insert into UserMaster values (5,'Joe','Joe',4,5,1,'Joe','Vester','m',Null,'Null ','Null')
Insert into UserMaster values (6,'Bryan','Bryan',4,6,1,'Byran','Adam','m',Null,' Null','Null')
Insert into UserMaster values (7,'John','John',4,7,1,'Jhon','Abraham','m',Null,' Null','Null')
Insert into UserMaster values (8,'Adam','Adam',4,8,1,'Adam','Core','m',Null,'Nul l','Null')
Insert into UserMaster values (9,'Jobin','Jobin',4,9,1,'Jobin','Thomas','m',Null ,'Null','Null')
select * from UserMaster|||Now give me a sample output of ur result u r looking|||Case 1: (Retrieval of the child nodes)

Example !: On passing the User ID of (Tom) , should get the Output as User id of ( Ross, Joe, Bryan , John, Adam and Jobin) along with their Role ID.

Example 2: On passing the User ID of (Jim) , should get the Output as User ID of (Ross, Joe ) along with their Role ID.

Case 2: (Retrieval of Parent Nodes)

On passing the Role ID of (R3), should get the User ID of all the Parent roles( Jim and Sadra (R2) , Tom (R1)) along with their Role id

case 3: on passing the Role Id of child node, should get the User ID's of all specific parent roles along with Role id.
Note that i may increase the rows and position id's|||case 1,2

--Case Tom--
declare @.UserID int
set @.UserID=1
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID
go
--case Jim---
declare @.UserID int
set @.UserID=2
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

and for jim which ur query is displaying
userid loginid roleid
4 Ross 4
5 Joe 4
6 Bryan 4
7 John 4
8 Adam 4
9 Jobin 4
but it need to disply only for Ross and Joe details..|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

I will explain what i undestand,

1.based on userid I got the roleID
2.select RoleID from RoleMaster by equating roleID from 1 step with ParentRoleID.
3.select userID from PostionMaster based whoever having that roleID I got from 2 step.
---

based on userid Tom's role id is 2
roleid 3 has parentRoleID 2(tom's roleid)
jim and sandra have roleid 3 in usermaster|||i will explain my scenario.
when i gve userid of Tom, condition is all the child nodes should be retrived, unconditionally how many may be the child nodes. all child nodes should be retrived. this is in case 1.
case 2. is when i gve roleid of anychild node, all the parent nodes should be retrived. it means if child node is not in the hand of parent node also all the parents should be output.
case 3: if i gve roleid of anychild node only the corresponding all the parents should be retived. it means it is hand based. if child is said to particular parent and that parent is in hand of another parent.. all related parents should be retrived.
i hope u can understand by this. i was unable to get this solution from last 2 days.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.

I forgot ur post itself.let me try now .|||Here is an article I wrote on a method of returning data from hierarchical structures. You should be able to adapt this to your situation.
-------------------
The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

CREATE TABLE [YourTable]
([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL)

The challenge is to find a way to return all the child records and descendants for any given parent record.

While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

A better algorithm is a method I call the "Accumulator Table".

In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

Here is some skeleton code to show how it works:

--This variable will hold the parent record ID who's children we want to find.
declare @.RecordID int
set @.RecordID = 13

--This table will accumulate our output set.
declare @.RecordList table (RecordID int)

--Seed the table with the @.RecordID value, assuming it exists in the database.
insert into @.RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @.RecordID

--Add new child records until exhausted.
while @.@.RowCount > 0
insert into @.RecordList (RecordID)
select YourTable.RecordID
from YourTable
inner join @.RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @.RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

--Return the result set
select RecordID
from @.RecordList

This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5D|||ya i had a look at that site also, but can u explain how in Personnel table , fo albert and bert... got 12 and 3.. as rgt. as lft=2*(SELECT COUNT(*) FROM TreeTable) ..and sorry for asking u directly .. could u give me direct procedure for my requirement, as i am working for these, last week days . i am trying in different ways and moved to another. plz do if possible. thanks for what u r doing.|||--case 1

create procedure dbo.retrieveUserid (
@.roleid int
)
as
if object_id('tempdb.dbo.##temp','u') is null
begin
create table ##temp(userid int,roleid int)
print('create table')
end
declare @.roleid1 int
select
@.roleid1=r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.RoleID=@.roleid
if (@.@.rowcount>0)
begin
insert into ##temp
select distinct
u2.UserID ,
r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1. RoleID= @.roleid

exec retrieveUserid @.roleid1
end
else
begin
select * from ##temp
drop table ##temp
end

--Call procedure--
declare @.roleid int,@.userid int
set @.userid=1
select @.roleid=RoleID from UserMaster where UserID= @.userid
exec retrieveUserid @.roleid

function

hi..
i have one holiday master where all holiday are defined..now in below function i want to add payoutday to my trade_day.and this date will insert into my funding table.but before that i have to check after adding payoutday to the trade_day, this date not fall in holiday list.if that day will holidaty than i have to take next working day.and if again next day also holiday than i want to skip that day also and take next working day...and again check for holiday so on.
how to do this?

[code]

ALTER FUNCTION dbo.NewPayOutDate(@.id numeric(9))
Returns datetime
as
Begin

DECLARE @.CALDATE int
DECLARE @.ACTDATE DATETIME
Declare @.adddate DATETIME
declare @.moreCALDATE int
Begin
SELECT @.CALDATE=payoutday ,@.ACTDATE=trade_date
FROM pruamc.Tbl_GroupMst GM
INNER JOIN pruamc.Tbl_Redemption_UploadDetails (NOLOCK)
ON
GM.Group_Name = pruamc.Tbl_Redemption_UploadDetails.scheme_group
WHERE pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.id
SET @.adddate = dateadd(dd,@.CALDATE,@.ACTDATE)
SELECT @.moreCALDATE=count(*) from pruamc.Tbl_holidaymst where holiday_date between @.ACTDATE and @.adddate
END
set @.adddate = dateadd(dd,@.moreCALDATE,@.adddate)
RETURN @.adddate
END

[/code]

Moving to the Transact-SQL forum.|||

i m sorry ...thanx for

Moving to the Transact-SQL forum.

but any solution about my problem?

thanx a lot

|||

The following function will be help you to get the next working day...

Code Snippet

Create Function GetWorkingDay(@.Date as DateTime)

Returns DateTime

as

Begin
Declare @.Holiday as DateTime;
Select @.Holiday = holiday_date From Tbl_holidaymst Where holiday_date = @.Date;

If @.Holiday is Null
return @.Date;

return dbo.GetWorkingDay(DateAdd(DD, 1, @.Date));

End

Output:

if 1/1/2007 & 1/2/2007 is holiday

Select dbo.GetWorkingDay('1/1/2007') => 1/3/2007

Select dbo.GetWorkingDay('12/31/2006') => 12/31/2007

|||great thanx a lot..manid