Create/Select/Insert/Update/Delete store procedures Automatically
when working with database applications much of time is wasted when creating sps and that too if the table is big we need to write each parametrs passing to the sp..
Creating Select ,Insert,Update,Delete Store Procedures Automatically
when working with database applications much of time is wasted when creating sps and that too if the table is big we need to write each parametrs passing to the store procedures ..
after a good research online i created an sp which will accept the table name and database name and will create insert update delete store procedures in seconds
the modified version of the store procedures is attached
steps
-----
execute the attached scripts
Running the store procedures
----------------
here i used 'aspnet_Users' membership table as table parameter for this store procedures
exec proc_Create_Script @GenerateProcsFor='aspnet_Users',@DatabaseName='dbname'
when executing the above store procedures the out put will be
1.procedure for Listing all
2.Procedure based on where condition
3.Procudre for Insert/Update
4.procudure for delete
List All
---------------------------------------------------
CREATE PROC [dbo].[Proc_aspnet_users_list]
AS
BEGIN
SET nocount ON
SELECT [ApplicationId],
[UserId],
[UserName],
[LoweredUserName],
[MobileAlias],
[IsAnonymous],
[LastActivityDate]
FROM aspnet_users
SET nocount OFF
END
----------------------------------------------------
List based on condition
----------------------------------------------------
CREATE PROC [dbo].[Proc_aspnet_users_list]
@ApplicationId UNIQUEIDENTIFIER
AS
SET nocount ON
SELECT [ApplicationId],
[UserId],
[UserName],
[LoweredUserName],
[MobileAlias],
[IsAnonymous],
[LastActivityDate]
FROM aspnet_users
WHERE [ApplicationId] = @ApplicationId
SET nocount OFF
-----------------------------------------------------
Insert/Update
-----------------------------------------------------
CREATE PROC [dbo].[Proc_aspnet_users_insertupdate]
@ApplicationId UNIQUEIDENTIFIER,
@UserId UNIQUEIDENTIFIER,
@UserName NVARCHAR( 256),
@LoweredUserName NVARCHAR(256),
@MobileAlias NVARCHAR(16),
@IsAnonymous BIT,
@LastActivityDate DATETIME
AS
SET nocount ON
IF @ApplicationId = ''
BEGIN
INSERT INTO aspnet_users
([ApplicationId],
[UserId],
[UserName],
[LoweredUserName],
[MobileAlias],
[IsAnonymous],
[LastActivityDate])
VALUES ( @ApplicationId,
@UserId,
@UserName,
@LoweredUserName,
@MobileAlias,
@IsAnonymous,
@LastActivityDate )
SELECT Scope_identity() AS insertedid
END
ELSE
BEGIN
UPDATE aspnet_users
SET [UserId] = @UserId,
[UserName] = @UserName,
[LoweredUserName] = @LoweredUserName,
[MobileAlias] = @MobileAlias,
[IsAnonymous] = @IsAnonymous,
[LastActivityDate] = @LastActivityDate
WHERE [ApplicationId] = @ApplicationId
END
SET nocount OFF
--------------------------------------------------------
Delete
CREATE PROC [dbo].[Proc_aspnet_users_delete]
@ApplicationId UNIQUEIDENTIFIER
AS
SET nocount ON
DELETE FROM aspnet_users
WHERE [ApplicationId] = @ApplicationId
SET nocount OFF
------------------------------------------------------
jeebu
me thanks this is blown out of proportion. I don't want to be seen be new Twitter users, I'm in the Long Tail, not the Short Head! How about you? Cheers- Chris