InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 15:35:49 GMT20http://www.teradata.com/teradataforum/Topic11729-9-1.aspxHello,Following is the most simple example of a Stored-Procedure:CREATE PROCEDURE spName(IN P1 INTEGER, OUT P2 INTEGER)BEGIN SELECT P1 + 10 INTO P2;END;Perhaps the task you wish to achieve can be better done using dynamic-sql or offcourse BTEQ script will be great. Following is the example of dynamic-sql in Stored-Procedure (you will have to add ID column against each username in temporary table):CREATE PROCEDURE spName(IN P1 INTEGER, OUT P2 INTEGER)BEGIN DECLARE DSQL VARCHAR(100); DELCARE UserCount INTEGER; SELECT COUNT(*) INTO UserCount FROM VolatileTable1; L1: LOOP SELECT 'CREATE USER ' || UserNameColumn INTO DSQL FROM VolatileTable1 WHERE ID = UserCount; CALL DBC.SysExecSQL(DSQL); SELECT UserCount - 1 INTO UserCount; IF(UserCount < 1) LEAVE L1; END IF; END LOOP L1;END;HTH.Regards,AdeelWed, 04 Jun 2008 10:07:31 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11729-9-1.aspxHi, I need to write a stored procedure for creating a user on the basis of the values in a temp table. The temp table will have only username column and based on the values in the table. My SP shud do the following- Create role for user- Create default database for user- Create user- Moving perm space to user from d_spacereserve- Granting privs for user- Granting role to the user Can anyone help me please as I have never worked on SP. If you can provide me a general structure with a simple example i wud be great.ThanksWed, 04 Jun 2008 07:37:53 GMTterakops