REWARD FOR ALL OF YOU GIVERS - 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

Script Library

REWARD FOR ALL OF YOU GIVERS

Author

Topic

cmschick
Starting Member

20 Posts

Posted - 2005-12-21 : 22:42:59

Hey Guys. I just wanted to thank everyone for all their help, especially TG - One with the Optimizer

I found this article on MSDN Mag and wanted to do a special case. This code (with these modificaitons) will write your Insert stored procedures for you if you pass in the table name,secondary key name, and the secondary key ID (if a secondary key is needed), and will return the PrimaryKeyID from the generated stored procedure.

You have the option to execute the SP after generating it.
You have the option to insert 1 value for one field (if the table doesn't require a SecondaryKeyID).
No input parameters are required to generate the new SP.

Read the article for more details.
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/[/url]

Here are the modifications to the Insert Procedure... Happy coding

----Here's how to call this SP. Copy and paste the following commented text into the Query Analyzer and select Edit, Advanced, Remove Comments from the window menu.

----This is the variable that will accept the OUTPUT value (in VB it would be used like this: myVariable = MyCommand.Execute)
--DECLARE @id int

----This calls the SP

---Input parameters explanation:
----@sTableName = [The name of your table, Required]
----@bExecute [Execute the new stored procedure after generating it? Optional, 1=Yes, 0 or omitted = No. Default = 0)
----@FieldName [Optional if SecondaryKeyID is not required, Required if it is. Can be any field name if SecondaryKeyID is not required, Default '']
----@FieldValueInt [Optional ifSecondaryKeyID is not required. Required if it is. Default = 0]
----@FieldValueVar [Optional. Used if SecondaryKeyID is not required and you need to enter text into your @FieldName field. Default = '']
----@intOut = @id OUTPUT [Required. Returns the PrimaryKeyID for the row affected. See instructions below for use in the calling SQL]

--EXEC usp__SYS_MakeInsertRecordProc
--@sTableName = 'YourTableName',
--@bExecute = 1,
--@FieldName = 'YourTableField or the SecondaryID if it is required to make an insert in your table',
--@FieldValueInt = 3, --an integer value if your field requires integers
--@FieldValueVar = '', --a VarChar field if your field accepts text
--@intOut = @id OUTPUT

----Display the output value we captured
--SELECT @id [Enter Your Primary Key Field Name Here]

CREATE PROC usp__SYS_MakeInsertRecordProc
@sTableName varchar(128),
@bExecute bit = 0,
@FieldName varchar(50) = '',
@FieldValueInt int = 0,
@FieldValueVar varchar(128) = '',
@IntOut int OUTPUT

AS

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0

BEGIN
RAISERROR ('Procedure cannot be created on a table without a primary key.', 10, 1)
RETURN
END

DECLARE
@sProcText varchar(8000),
@sKeyFields varchar(2000),
@sAllFields varchar(2000),
@sAllParams varchar(2000),
@sWhereClause varchar(2000),
@sColumnName varchar(128),
@nColumnID smallint,
@bPrimaryKeyColumn bit,
@nAlternateType int,
@nColumnLength int,
@nColumnPrecision int,
@nColumnScale int,
@IsNullable bit,
@IsIdentity int,
@HasIdentity int,
@sTypeName varchar(128),
@sDefaultValue varchar(4000),
@sCRLF char(2),
@sTAB char(1),
@Id int

SET @HasIdentity = 0
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
SET @sProcText = ''
SET @sKeyFields = ''
SET @sAllFields = ''
SET @sWhereClause = ''
SET @sAllParams = ''
SET @Id = 0
SET @intOut = 0

SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''usp_' + @sTableName + '_InsertAG'')' + @sCRLF

SET @sProcText = @sProcText + @sTAB + 'DROP PROC usp_' + @sTableName + '_InsertAG' + @sCRLF

IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
PRINT @sProcText
IF @bExecute = 1
EXEC (@sProcText)
SET @sProcText = ''
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + '-- Insert a single record into ' + @sTableName + @sCRLF
SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF
SET @sProcText = @sProcText + 'CREATE PROC usp_' + @sTableName + '_InsertAG' + @sCRLF

DECLARE crKeyFields cursor for

SELECT *

FROM dbo.fnTableColumnInfo(@sTableName)

ORDER BY 2

OPEN crKeyFields

FETCH NEXT

FROM crKeyFields

INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType,
@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable,
@IsIdentity, @sTypeName, @sDefaultValue

WHILE (@@FETCH_STATUS = 0)

BEGIN
IF (@IsIdentity = 0)
BEGIN
IF (@sKeyFields <> '')
SET @sKeyFields = @sKeyFields + ',' + @sCRLF
SET @sKeyFields = @sKeyFields + @sTAB + '@' + @sColumnName + ' ' + @sTypeName
IF (@sAllFields <> '')
BEGIN
SET @sAllParams = @sAllParams + ', '
SET @sAllFields = @sAllFields + ', '
END

IF (@sTypeName = 'timestamp')
SET @sAllParams = @sAllParams + 'NULL'
ELSE IF (@sDefaultValue IS NOT NULL)
SET @sAllParams = @sAllParams + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')'
ELSE
BEGIN
SET @sAllParams = @sAllParams + '@' + @sColumnName
END
SET @sAllFields = @sAllFields + @sColumnName
END
ELSE
BEGIN
SET @HasIdentity = 1
END
IF (@nAlternateType = 2) --decimal, numeric
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', '
+ CAST(@nColumnScale AS varchar(3)) + ')'

ELSE IF (@nAlternateType = 1) --character and binary
SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')'
IF (@IsIdentity = 0)
BEGIN

--******* ADD SPECIALIZED PROCESSING HERE FOR ANY INPUT PARAMETERS REQUIRED *******--

IF (@sColumnName = @FieldName) AND (@FieldValueInt <> 0)
SET @sKeyFields = @sKeyFields + ' = ' + CAST(@FieldValueInt AS varchar(10))
ELSE IF (@sColumnName = @FieldName) AND (@FieldValueVar <> '')
SET @sKeyFields = @sKeyFields + ' = ' + @FieldValueVar

--******* END ADD SPECIALIZED PROCESSING **********--

ELSE IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp')
SET @sKeyFields = @sKeyFields + ' = NULL'
END

FETCH NEXT
FROM crKeyFields
INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale,
@IsNullable, @IsIdentity, @sTypeName, @sDefaultValue

END

CLOSE crKeyFields

DEALLOCATE crKeyFields

SET @sProcText = @sProcText + @sTAB + '@Id int OUTPUT,' + @sCRLF + @sKeyFields + @sCRLF
SET @sProcText = @sProcText + 'AS' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
SET @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF
SET @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF
SET @sProcText = @sProcText + @sCRLF

IF (@HasIdentity = 1)
BEGIN
SET @sProcText = @sProcText + 'SET @Id = (SELECT SCOPE_IDENTITY())' + @sCRLF
SET @sProcText = @sProcText + 'IF @Id IS NULL' + @sCRLF + @sTAB
SET @sProcText = @sProcText + 'RETURN -1' + @sCRLF + 'ELSE' + @sCRLF + @sTAB
SET @sProcText = @sProcText + 'RETURN @Id' + @sCRLF
SET @sProcText = @sProcText + @sCRLF
END

IF @bExecute = 0
SET @sProcText = @sProcText + 'GO' + @sCRLF

PRINT @sProcText

IF @bExecute = 1
EXEC(@sProcText) --Adds the SP to the database and executes but without return value
SET @sProcText = 'usp_' + @sTableName + '_InsertAG'
EXEC @sProcText @id OUTPUT --Executes the generated SP and returns the PrimaryKey ID
SET @intOut = @id
GO

The Schickster

<edit> Moved to Script Library </edit>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources