Insert - stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

Insert - stored procedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-06-30 : 10:50:15

I have an sp which inserts a record. It has about 60 parameters. I want my user to be able to click a button on my web oage which will insert null values for all the parameters (except where a default value is specified in the sql table) and create a record in the table with an id (identity autonumber). The user can then go back to this record and update fields etc using an update query to update the record in the table. Can i do this without having to code every null value as a parameter in my asp.net Also, how do i return the id to my webpage ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 10:54:18

Your best choice is the default all input parameters to the stored procedure as NULL.

To return the IDENTITY value you have a number of options

1) Make use of OUTPUT parameter (preferrably the first if you default all other parameters as null)
2) Make the SP return a resultset if a new record is inserted
3) Set the return value (ie the error code) for the stored procedure to the new identity (not recommended)

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 11:02:30

Post the DDL, but Let's say the "Key" is the only NOT NULL Value...

...and you want a row per user


CREATE PROCEDURE [dbo].[usp_Create_Template]
	@APPUSER char(30), @scope_identity int OUTPUT
AS 

	IF NOT EXISTS (SELECT* FROM <table> WHERE UserId = @APPUSER)
	  BEGIN
		INSERT INTO <table> (appuser) SELECT @APPUSER
		SET @scope_identity = @@SCOPE_IDENTITY()
	  END
	  ELSE
		SELECT 'UserID: ' + @APPUSER + ' Already Exists'
RETURN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-06-30 : 11:20:45

I don't want a row per user, just one blank record with an id - this errors though...

CREATE PROCEDURE [dbo].[spCreateNewReport]

@NextReportNo int OUTPUT
AS

IF NOT EXISTS (SELECT* FROM tblReports)
BEGIN
INSERT INTO tblReports

@NextReportNo = @@IDENTITY

RETURN @NextReportNo

GO

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 12:17:30

Try This...

Disclaimer: I am totally against this, unless you are actually try to record something


CREATE TABLE myTable99(
	Col1 int IDENTITY(1,1), NewRecInd char(1), col3 int DEFAULT(0), col4 datetime DEFAULT(GetDate()))
GO

CREATE PROCEDURE [dbo].[usp_Create_Template]
	@scope_identity int OUTPUT
AS 
	INSERT INTO myTable99(NewRecInd) SELECT 'Y'
	SET @scope_identity = SCOPE_IDENTITY()
RETURN
GO

DECLARE @scope_identity int
EXEC usp_Create_Template @scope_identity OUTPUT
SELECT @scope_identity AS NEWLY_INSERTED_KEY
SELECT * FROM myTable99 WHERE Col1 = @scope_identity
-- Do it again
EXEC usp_Create_Template @scope_identity OUTPUT
SELECT @scope_identity AS NEWLY_INSERTED_KEY
SELECT * FROM myTable99 WHERE Col1 = @scope_identity

SELECT * FROM myTable99
GO

DROP PROC [usp_Create_Template]
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Pinto
Aged Yak Warrior

590 Posts

Posted - 2010-07-01 : 03:40:09

Why create a table ? I already have a table. I can't believe adding one record to a table is so difficult.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-01 : 03:55:41

Pinto, I don't think Brett has direct access to your server, so he has to mimic an environment that might look like yours.
Run the complete sample code above, and you might be able to understand what the code does.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources