September 29, 2010 at 10:30 am
I've been a casual sql user for sometime, but this is my first stored procedure. 😀
I frequently set up new users with the same identical permissions. The process of setting up new users is a real pain b/c the application doesn't allow me to copy from another user. I set up some header info such as user name, full name, default locations, etc... all stored in one table and then I must go to a tab and grant the appropriate table permissions (inquire, update, add, delete) for just over 100 tables, which is all contained in a second table.
So here's what I've done:
Created one Stored Procedure to do the following...
1. INSERT INTO Table 1. For those values that don't change, I set the defaults. Here's a snippet:
CREATE PROCEDURE usp_addusertest4
@shusername varchar(35),
@shdescription varchar(50),
@shopid varchar(3),
@shadmin varchar (1)= '0',
@shehshop varchar(2) = 'AU',
@shstoreroom varchar(1) = 'A',
AS
BEGIN
INSERT INTO FASTER.sheader (shusername,shdescription,shopid,
shadmin,shehshop,shstoreroom,
VALUES (@shusername,@shdescription,@shopid,
@shadmin,@shehshop,@shstoreroom,
2. INSERT INTO Table 2
INSERT INTO faster.SAttribute (sashusername)
SELECT @shusername
So far so good... the above works fine, when I execute my full SP.
3. UPDATE Table 2 (b/c at this point, table 2 only has my new userid and this is where I need help)
UPDATE faster.SAttribute
SET SATablePerm = '5'
WHERE SASHusername = @shusername
AND SATable in ('WC','WD','WH','WI','WL','WM','WR');
When I execute my sp, steps 1 and 2 above work fine. Step 3 does not and I get no errors, I'm sure b/c it finds no sashusername that = @shusername.
So my problem seems to be:
WHERE SASHusername = @shusername
How the heck do I tell it to update the table just for my new user, which was specfied when I executed my sp? Any help would be appreciated.
September 29, 2010 at 10:48 am
When you do the insert:
INSERT INTO faster.SAttribute (sashusername)
SELECT @shusername
you don't insert a value for SATable, yet you are looking for a value in the second update. It won't find the row because the row doesn't contain any of the values in the list.
UPDATE faster.SAttribute
SET SATablePerm = '5'
WHERE SASHusername = @shusername
AND SATable in ('WC','WD','WH','WI','WL','WM','WR');
September 29, 2010 at 1:01 pm
tshelly (9/29/2010)
CREATE PROCEDURE usp_addusertest4
@shusername varchar(35),
@shdescription varchar(50),
@shopid varchar(3),
@shadmin varchar (1)= '0',
@shehshop varchar(2) = 'AU',
@shstoreroom varchar(1) = 'A',
AS
BEGIN
INSERT INTO FASTER.sheader (shusername,shdescription,shopid,
shadmin,shehshop,shstoreroom,
VALUES (@shusername,@shdescription,@shopid,
@shadmin,@shehshop,@shstoreroom,
2. INSERT INTO Table 2
INSERT INTO faster.SAttribute (sashusername)
SELECT @shusername
So far so good... the above works fine, when I execute my full SP.
3. UPDATE Table 2 (b/c at this point, table 2 only has my new userid and this is where I need help)
UPDATE faster.SAttribute
SET SATablePerm = '5'
WHERE SASHusername = @shusername
AND SATable in ('WC','WD','WH','WI','WL','WM','WR');
When I execute my sp, steps 1 and 2 above work fine. Step 3 does not and I get no errors, I'm sure b/c it finds no sashusername that = @shusername. So my problem seems to be:
WHERE SASHusername = @shusername
Your problem is not with WHERE SASHusername = @shusername
it is with SATable in ('WC','WD','WH','WI','WL','WM','WR'); since u r just inserting only
INSERT INTO faster.SAttribute (sashusername)
SELECT @shusername
Remaining fields are missing Null value will be there and you are trying to achive it with out inserting data to field name SATable
Thanks
Parthi
Thanks
Parthi
September 29, 2010 at 2:25 pm
Yep, thanks to both of you. I had it in my head that the table names were already established.... I see clearly now, thanks to y'alls input. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply