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