November 4th, 2008 
admin When you want to create a stored procedure dynamically,we can create a stored procedure within a stored procedure.
(
@spname NVARCHAR(100)
)
AS
BEGIN
DECLARE @ProcedureName NVARCHAR(100)
DECLARE @Exe VARCHAR(8000)
/*setting the stored procedure name with spname passed*/
SET @ProcedureName = @spname
/*setting the stored procedure name with spname passed*/
/*Nothing will be done if procedure name is empty*/
IF LTRIM(RTRIM(@ProcedureName)) = ”
BEGIN
RETURN
END
/*Nothing will be done if procedure name is empty*/
/*Creating the stored procedure*/
SET @Exe = ‘CREATE PROC ‘ + @ProcedureName + CHAR(13) +
‘AS’ + CHAR(13) + CHAR(13) +
‘BEGIN’ + CHAR(13) +
‘SELECT * FROM EMPLOYEE’+ CHAR(13) +
‘END’
/*Creating the stored procedure*/
/*Executing the created stored procedure*/
EXEC(@Exe)
/*Executing the created stored procedure*/
END
/*Executing procCreateProcedure to creat new stored procedure*/
EXEC procCreateProcedure ‘procTest’
/*Executing procCreateProcedure to creat new stored procedure*/
now execute and see the stored procedure by name you passed.
EXEC procTest
Here in this example a new stored procedure with name ‘procTest’ which is passed in ‘procCreateProcedure’ is created and employee table is fectched when ‘procTest’ stored procedure is executed.
Popularity: 1% [?]
Posted in 
Tags: 
