June 13, 2012 at 10:19 am
I am relatively new to SQL so I apologize if this question is somewhat basic. The problem that I am having is with an if-else statement (code below) within a procedure. The procedure obsfucates table information, but its purpose is not that important to my question. The if block prints the @STR variable to screen, which is the update statement to be ran. EXEC(@str) executes the update statement and the print statement outputs this: "5 rows updated for schema_name.table_name." @aud_flag is passed in as a procedure parameter and will always be either 0 or 1 depending on whether you want to carry out the update or not. When the @aud_flag is initialized to 1 it works fine, the entire if block processes correctly. This part of the if-else statement works fine.
The problem is with the else block. The if-else below functions as a type of audit on whether to go through and update/obsfucate the tables or not. The problem I am having is with the output of the else block, it is just outputting the count when I execute the procedure and not printing either of the two print statements to screen. I was wondering why this is and how I could get it to recognize those two print statements and print them to screen. I want my output to look something like: "5 rows available for update in schema_name.table_name" and then the other print statement "Print @STR" will just print the update statement to screen as it does in the if block.
I was also curious if there is any way so when the exec(@str) exec(@aud_str) statements are executed that there is no output to screen from them? If I'm not being concise enough on anything, just let me know and I will expand. Thank you in advance for the responses and again bear with me as I am new to SQL.
if(@aud_flag = 1)
begin
Print @STR
EXEC (@str)
Print convert(varchar(10),@@rowcount) + ' rows updated for ' + @schema_name + '.' + @table_name
end
else
begin
set @aud_str = 'select count(*) from ' + @schema_name + '.' + @table_name
EXEC (@aud_str)
Print convert(varchar(10),@@rowcount) + ' rows available for update in ' + @schema_name + '.' + @table_name
Print @STR
end
June 13, 2012 at 12:16 pm
I don't see a declaration for @audit_flag. I say that knowing that you are leaving out script, and without all of it we will have a hard time helping you.
Jared
CE - Microsoft
June 13, 2012 at 12:33 pm
Ok here is the entire procedure, but keep in mind that the script runs just fine and I am not interested in any performance related stuff, "Why did you do it that way?" etc. This is strictly an issue with outputting from the else statement. My execute statements are commented out below the script too. Also note that this procedure is fed by 2 tables encrypt_table and encrypt_table_columns. The procedure works fine I am just concerned with the output of my else block and why it is not reading the 2 print statements.
Remember, the problem is with the output of the else block, it is just outputting the count when I execute the procedure and not printing either of the two print statements to screen. I was wondering why this is and how I could get it to recognize those two print statements and print them to screen. I want my output to look something like: "5 rows available for update in schema_name.table_name" and then the other print statement "Print @STR" will just print the update statement to screen as it does in the if block.
Thanks for being patient
if exists (select * from sysobjects where type = 'P' AND name = 'phi_scrub')
drop procedure phi_scrub
GO
create procedure phi_scrub(@audit char(1)) --audit must be passed as 1 for procedure to update the specified table
as
declare @table_id int
declare @schema_name varchar(128)
declare @table_name varchar(128)
declare @STR varchar(4000)
declare @aud_flag char(1)
declare @aud_str varchar(4000)
set @aud_flag = @audit
declare table_cur cursor read_only for
select table_id, sch_name, table_name
from DBA.dbo.encrypt_table
where active = 1
open table_cur
fetch next from table_cur into @table_id, @schema_name, @table_name
if @@fetch_status > -1
while @@fetch_status = 0
begin
--start the update statement
set @STR = 'Update ' + @schema_name + '.' + @table_name + ' SET '
declare @column_name varchar(50)
declare @ctr int
declare col_cur cursor read_only for
select column_name
from DBA.dbo.encrypt_table_columns
where table_id = @table_id and active = 1
--init ctr
set @ctr = 1
open col_cur
fetch next from col_cur into @column_name
if @@fetch_status > -1
while @@fetch_status = 0
begin
set @STR = @STR + @column_name + ' = brett_test.dbo.character_scramble(' + @column_name + '), '
fetch next from col_cur into @column_name
end
else
print 'empty column cursor! active value must be 1'
close col_cur
deallocate col_Cur
--get rid of the last comma to fix syntax issue
set @STR = substring(@str,1,Len(@str)-1)
--include a cr/lf to make print statement readable
if(@aud_flag = 1)
begin
Print @STR
EXEC (@str)
Print convert(varchar(10),@@rowcount) + ' rows updated for ' + @schema_name + '.' + @table_name
end
else
begin
set @aud_str = 'select count(*) from ' + @schema_name + '.' + @table_name
EXEC (@aud_str)
Print convert(varchar(10),@@rowcount) + ' rows available for update in ' + @schema_name + '.' + @table_name
Print @STR
end
fetch next from table_cur into @table_id, @schema_name, @table_name
end
else
print 'empty table cursor! active value must be 1'
close table_cur
deallocate table_Cur
/*
drop procedure phi_scrub;
exec insert_table_column 'Brett_Test', 'dbo', 'test1', 'fname', 1
exec phi_scrub '0'
*/
June 13, 2012 at 12:52 pm
The results of @@rowcount will be one and is out of scope (executed in a different context) so the value of @@rowcount in you print statement is null. You are stringing a null with a string which will return a null.
If your intent is to print the actual number of records in the count query, try this:
begin
select @aud_str = N'select count(*) from ' + @schema_name + '.' + @table_name
declare @value table (RCount int)
insert into @value
execute (@aud_str)
select @rowcount = Rcount from @value
print convert(varchar(10),@rowcount) + ' rows available for update in ' + @schema_name + '.' + @table_name
Print @STR
end
June 13, 2012 at 12:55 pm
Well, I ran this on some test tables I have and got the following results:
declare @table_id int
declare @schema_name varchar(128)
declare @table_name varchar(128)
declare @STR varchar(4000)
declare @aud_flag char(1)
declare @aud_str varchar(4000)
set @aud_flag = 0
set @schema_name = 'dbo'
set @table_name = 'yourTable'
set @STR = 'SELECT * FROM testConstraint'
if(@aud_flag = 1)
begin
Print @STR
EXEC (@str)
Print convert(varchar(10),@@rowcount) + ' rows updated for ' + @schema_name + '.' + @table_name
end
else
begin
set @aud_str = 'select count(*) from ' + @schema_name + '.' + @table_name
EXEC (@aud_str)
Print convert(varchar(10),@@rowcount) + ' rows available for update in ' + @schema_name + '.' + @table_name
Print @STR
end
Results:
(1 row(s) affected)
1 rows available for update in dbo.yourTable
SELECT * FROM testConstraint
So my guess is that you are never actually getting to the else.
Jared
CE - Microsoft
June 13, 2012 at 12:56 pm
2 things:
If you set CONCAT_NULL_YIELDS_NULL = OFF, does that change the behavior?
The @@rowcount in your 'else' block will only ever return 1 because all you are returning is the row with the count(*).
June 13, 2012 at 12:59 pm
Good point Cathy and David, however, it does not solve why the OP is not seeing any printed output. I can see it with that block, so I think the block is not being executed.
Jared
CE - Microsoft
June 13, 2012 at 1:07 pm
@@rowcount returns the number of rows affected by the last statement so this could be an issue, but I'm pretty sure count(*) should return the number of rows in a specified table when used with @@rowcount. I know it is entering the else statement though because it outputs the count value to screen when I run my EXEC(@aud_flag) command. I can't figure out why it is not reading those pesky print statements though.
I just commented out the set and exec statement in the else block and it runs fine then? The @@rowcount just isn't initialized to anything because for it to work there would have to be some sort of sql statement ahead of it to traverse the table and return @@rowcount as the number of rows in the table.
June 13, 2012 at 1:16 pm
I don't know what to say... It works for me (except the @@rowcount because the EXEC is out of scope and only returns 1) and I see both print statements.
Jared
CE - Microsoft
June 13, 2012 at 1:24 pm
Sorry if this seem too simple, but where are you looking for the print statements to appear? Are you running this in SSMS?
June 13, 2012 at 1:30 pm
What happens if you run this?
CREATE TABLE #temp1 (fghf int identity(1,1), something varchar(25))
INSERT INTO #temp1
SELECT 'dfsahjkfdh'
UNION ALL
SELECT 'fdsg'
UNION ALL
SELECT 'jtyhgkd'
UNION ALL
SELECT 'jgddgjdj'
declare @table_id int
declare @schema_name varchar(128)
declare @table_name varchar(128)
declare @STR varchar(4000)
declare @aud_flag char(1)
declare @aud_str varchar(4000)
set @aud_flag = 0
set @schema_name = 'dbo'
set @table_name = 'fghf'
set @STR = 'SELECT * FROM fghf'
SET NOCOUNT ON
if(@aud_flag = 1)
begin
Print @STR
EXEC (@str)
Print convert(varchar(10),@@rowcount) + ' rows updated for ' + @schema_name + '.' + @table_name
end
else
begin
set @aud_str = 'select count(*) from ' + @schema_name + '.' + @table_name
EXEC (@aud_str)
Print convert(varchar(10),@@rowcount) + ' rows available for update in ' + @schema_name + '.' + @table_name
Print @STR
end
You should get no results because of an error and the following in messages:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.fghf'.
1 rows available for update in dbo.fghf
SELECT * FROM fghf
Jared
CE - Microsoft
June 13, 2012 at 1:41 pm
FINALLY!!! ....... I got it to work and it was a problem with the @@rowcount. Also there was some setting in SSMS that was disabled or something that's why I wasn't seeing my print statements. Thank you all for your comments, greatly appreciated!!! 😎
June 13, 2012 at 1:43 pm
Well don't leave us hanging! How did you solve the @@rowcount issue and what setting was disabled?
Jared
CE - Microsoft
June 13, 2012 at 1:59 pm
I used the pseudocode that Cindy posted to solve my @@rowcount issues and was able to achieve the desired output. I started getting various other errors when I added the pseudocode and then had to tweak it a bit and specify which database I was in. As for the setting, your guess is as good as mine! My boss came to look at the procedure and see how it was coming along and he switched something within the SSMS that made the print statements work!!!
June 13, 2012 at 2:00 pm
if(@aud_flag = 1)
begin
Print @STR
EXEC (@str)
Print convert(varchar(10),@@rowcount) + ' rows updated for ' + @database_name + '.' + @schema_name + '.' + @table_name
end
else
begin
set @aud_str = 'select count(*) from ' + @database_name + '.' + @schema_name + '.' + @table_name
declare @value table (rcount int)
insert into @value
execute (@aud_str)
select @row_no = rcount from @value
print convert(varchar(10),@row_no) + ' rows available for update in ' + @database_name + '.' + @schema_name + '.' + @table_name
Print @aud_str
end
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply