Thoughts on this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Thoughts on this

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2009-08-21 : 10:01:56

I have an ASP.NET 2.0 application that when the user clicks on a save button I need to insert into the table StoreMiscNavLink. The application needs to have the LinkID returned to it, I have that covered using an OUTPUT clause.
I have sql statements that work and do what I want but I would like to know if there is a better way to do it? Any thoughts are welcome.

INSERT INTO StoreMiscNavLink (CCPID,LinkName,LinkAddress,PageCode,NavPosition) OUTPUT inserted.LinkID
VALUES (5886,'Testing','none','<a href="GetFilesInImageDir1.aspx">Get Files in Directory 1</a>', 'S')
UPDATE StoreMiscNavLink SET LinkAddress = ('storefront.aspx?TopicID=' + Cast(@@IDENTITY AS varchar(10))) WHERE LinkID = @@IDENTITY

I have tried

INSERT INTO StoreMiscNavLink (CCPID,LinkName,LinkAddress,PageCode,NavPosition) OUTPUT inserted.LinkID
VALUES (5886,'Testing','storefront.aspx?TopicID=' + Cast(@@IDENTITY AS varchar(10)),'<a href="GetFilesInImageDir1.aspx">Get Files in Directory 1</a>', 'S')

but this inserts with the Identity of LinkID -1, so I thought that I could just add one to the Identity value and use it but that seems like it might be unreliable.

The table:


USE [MYDB]
GO
/****** Object:  Table [dbo].[StoreMiscNavLink]    Script Date: 08/21/2009 08:47:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StoreMiscNavLink](
	[CCPID] [int] NOT NULL,
	[LinkName] [varchar](50) NULL,
	[LinkAddress] [varchar](500) NULL,
	[LinkID] [int] IDENTITY(1,1) NOT NULL,
	[PageCode] [varchar](max) NULL,
	[NavPosition] [char](1) NULL,
 CONSTRAINT [PK_StoreMiscNavLink] PRIMARY KEY CLUSTERED 
(
	[LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'S(side), T(Top), B(Both)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StoreMiscNavLink', @level2type=N'COLUMN',@level2name=N'NavPosition'
GO

ALTER TABLE [dbo].[StoreMiscNavLink]  WITH CHECK ADD  CONSTRAINT [FK_StoreMiscNavLink_Store] FOREIGN KEY([CCPID])
REFERENCES [dbo].[Store] ([CCPID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[StoreMiscNavLink] CHECK CONSTRAINT [FK_StoreMiscNavLink_Store]
GO

ALTER TABLE [dbo].[StoreMiscNavLink] ADD  CONSTRAINT [DF_StoreMiscNavLink_Position]  DEFAULT ('S') FOR [NavPosition]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 11:25:44

Not sure how static your link address is but you could make it a computed column:


CREATE TABLE [dbo].[StoreMiscNavLink](
	[CCPID] [int] NOT NULL,
	[LinkName] [varchar](50) NULL, 
	[LinkAddress] as 'storefront.aspx?TopicID=' + Cast(LinkID AS varchar(10)), 
	[LinkID] [int] IDENTITY(1,1) NOT NULL,
	[PageCode] [varchar](max) NULL,
	[NavPosition] [char](1) NULL,
 CONSTRAINT [PK_StoreMiscNavLink] PRIMARY KEY CLUSTERED 
(
	[LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

EDIT:
I guess for that matter you could just store the "storefront.aspx?" and concatenate the topicID when you SELECT.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources