|
shawn19801980
Starting Member
1 Post |
 Posted - 2013-05-16 : 15:19:35
|
| I want to write an generic sql script. I am totally new to SQLI have the following parameters.We need to create a generic sql script that will accept the following parameters:1. @v_Username2. @v_Encryptedpassword3. @v_Firstname4. @v_Lastname5. @v_Branch6. @i_RolekeyStep 1: Add the users rowCheck the users table to see if the username already exists-if yes then get the users.id and store it in @i_userid-if no, then -set @i_userid = max users.id and add +1 to it to get the new users.id-insert the following INSERT INTO users(id,username,password,first_name,last_name,active,deleted,created_by,dev_access)values(@i_userid, @v_Username, @v_Encryptedpassword,@v_firstname,@v_lastname,1,0,1,0)Step 2: Set the branch information-Set the @i_branchid = bu_entries.id where bu_entries description = @v_Branch-Set the @v_branchcode = bu_entries.code where bu_entries description = @v_BranchCheck the users_entries table to see if a row already exists for users_entries.user_id = @i_userid and users_entries.entry_id = @i_branchid IF no then Insert the following INSERT INTO users_entries (user_id,entry_id,role_id,sort_order,created_Date)values (@i_userid,@i_branchid,@i_rolekey,1,curdate()) Now do the dar_branch table- Set the @i_maxdarbranchkey = max dar_branch.id +1Check to see if the row already exists for dar_branch.userid = @i_userid and dar_branch.branchid = @v_branchcodeIf no then insert the rowinsert into dar_branch (id,userid,branchid) values (@i_maxdarbranchkey, @i_userid, @v_branchcode,3)Step 3: Insert the acl_priviliges rows to allow access to the dar pages by cloning from an existing user. We will need to do a loop to increment the acl_priviliges.id for each insert. Check first to see if the user exists where acl_privliges.user_id = @i_userid If no, then we need to insert all the rows Insert into acl_priviliges (id, resource_id,role_id,user_id,template_id, rule) Select @i_maxid,resource_id,role_id,@i_userid,template_id,rule from acl_privilges where user_id= 32052 |
|
Generic sql script
Here is a link that describes how to post a question so that anyone here can reply with exactly what you need: