September 20, 2010 at 7:38 am
HI I HAVE A TABLE
CREATE TABLE [dbo].[Url](
[UrlID] [int] NOT NULL primary key,
[UrlName] [varchar](100) NOT NULL,
[Url] [varchar](2000) NOT NULL,
[ParentID] [int] NULL,
[Level] [smallint] NOT NULL,
[IsActive] [bit] NOT NULL,
[SortField] [smallint] NOT NULL,
)
GO
_______ ________ ________ ______
Main MEnu--->PATRONS (SortField=1) SUPPLIERS(SF=2) BIND(SF=3) LOGS(SF=4)
SubMenu(1)---> CREATE(SF=1) ADD(SF=1)
Submenu(1a) --> CONSUMER(SF=1) VERTICAL(SF=1)
Submenu(1b) --> ROLE(SF=2) FLAG(SF=2)
Submenu(1c) --> USER(SF=3) CREDENTIAL(SF=3)
SubMenu(2)---> PRIVILEGE(SF=2)
Submenu(2a) --> SET URLS(SF=1)
Submenu(2b) --> SETTINGS(SF=2)
SubMenu(3)---> LIST(SF=3)
Submenu(3a) --> CONSUMER(SF=1)
Submenu(3b) --> USER(SF=2)
Submenu(3c) --> PRIVILEGE(SF=3)
Submenu(3d) --> URLS(SF=4)
This is the SP i written it manually enters the Sortfield..I want to generate
the sortfield automatically according to the above shown menu.Each menu is followed
by sortfield.This is the result..executing the procedure
_/*_________________________________________________________________________________
UrlID UrlName Url ParentIDLevelIsActiveSortField
1 Patrons http:///UM/Consumer%20List.html 0 1 1 1
2 Supplier http:/Credentials%20Settings.html 0 1 1 2
3 Create http:/UM/Consumer%20List.html 1 2 1 1
4 Consumer http://UM/Consumer%20List.html 3 3 1 1
5 Role http:///UM/Consumer%20List.html 3 3 0 2
6 User http:///UM/Consumer%20List.html 3 3 1 3
7 Add http://SM/List%20%20Settings.html 2 2 1 1
8 Vertical http:///SM/List%20Credentials.html 7 3 1 1
9 Flag http://SM/List%20Credentials.html 7 3 1 2
10 Supplier http:///SM/List%20Credengs.html 7 3 0 3
11 Bind http:///CM/Configuratiogs.html 0 1 1 3
*/
CREATE PROCEDURE UrlInsert
@UrlName varchar(100)
,@Url varchar(200)
,@ParentID int
,@Level smallint
,@IsActive bit
,@SortField smallint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UrlID int
SELECT @UrlID = ISNULL(MAX(UrlID),0) + 1--Auto generated Primary key
FROM _Url
INSERT INTO Url
(
UrlID
,UrlName
,Url
,ParentID
,[Level]
,IsActive
,SortField
)
VALUES
(
@UrlID
,@UrlName
,@Url
,@ParentID
,@Level
,@IsActive
,@SortField
)
END
IS there any way to do insert Sortfield automatically?
thanks
DD
September 20, 2010 at 8:03 am
Yes.
Defining an expression to appear as a column called a computed column in Books Online. You can find information about computed columns under the CREATE TABLE statement. The example below is taken from there. It shows a column called [myavg] that is computed from the values in the [low] and [high] columns.
CREATE TABLE dbo.mytable
( low int, high int, myavg AS (low + high)/2 ) ;
In your situation, consider writing a simple user-defined function to return the proper value for the [Sortfield] column, and use that function as the expression defining [Sortfield] as a computed column.
Basic computed columns are not stored as actual data in the table, rather they are computed whenever data is retrieved from the table, much like columns calculated in a view or CTE or subquery. Computed columns can be made persisted, in which case the actual result is stored as data in the table, rather than being computed on-the-fly by select statements. This is important if you should ever want to build an index over a computed column.
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply