June 6, 2012 at 9:33 am
Disclaimer: I am new to sql and new to these forums so bear with me on this one. I am trying to create a procedure to run a check if the schema_name or table_name exist in the encrypt_table. If they do I want it to retrieve the table_id so we can insert the column info into the encrypt_table_column table. Essentially the encrypt_table and encrypt_table_columns are tables that hold information about other tables within the database and are used as part of a larger procedure to determine which table columns need be encrypted and which don't. The active BIT field is used as a sort of flag for the larger procedure to determine whether to run the encryption on that table or not and will be passed to the insert through the procedure call, just as the schema_name, table_name, and column_name are. The procedure call should look something like this: exec insert_table_column(schema_name, table_name, column_name).
You will notice that the procedure checks whether the schema, table, and column exist and makes the appropriate insert whether they do or not. The thing I am having trouble with is it keeps giving me errors for using the get(@tab_id) statement. in reality all I am trying to do is get the value for table_id so we can insert the column information into that table. I have also included the error report at the bottom. Thanks in advance for the help!
create table encrypt_table
(
table_id int identity,
sch_name varchar(50),
table_name varchar(50),
active BIT
)
create table encrypt_table_columns
(
table_id int,
column_name varchar(50),
active BIT
)
create procedure insert_table_column(@schema_name varchar(128), @table_name varchar(128), @column_name varchar(128), @active BIT)
as
declare @id int
declare @sch_name varchar(128) --the first error ('CREATE/ALTER PROCEDURE' must be the first statement in a query batch.) occurs here
declare @tab_name varchar(128)
declare @col_name varchar(128)
declare @flag_bit BIT
declare @STR varchar(max)
set @sch_name = @schema_name
set @tab_name = @table_name
set @col_name = @column_name
set @flag_bit = @active
set @id = @@identity
begin
if exists (select sch_name, table_name from encrypt_table where sch_name = @sch_name and table_name = @tab_name)
get(@id) --If the sch_name and table_name already exist in the encrypt_table, then we don't need to insert into this table.
--We only need to find the existing table_id value so we can insert the column info into the encrypt_table_columns table
--The second error (syntax) occurs here
else
insert into encrypt_table values(@id, @tab_name, @flag_bit)
if exists (select column_name from encrypt_table_columns where table_name = @tab_name and column_name = @col_name)
print 'column already exists'
else
insert into encrypt_table_columns values(@id, @col_name, @flag_bit)
select et.sch_name, et.table_name, et.active, tc.column_name, tc.active from encrypt_table et,
encrypt_table_columns tc where et.table_id = tc.table_id; --the final error (syntax) occurs in this select statement
--drop table encrypt_table
--drop table encrypt_table_columns
GO
Msg 111, Level 15, State 1, Procedure insert_table_column, Line 68
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Procedure insert_table_column, Line 81
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure insert_table_column, Line 91
Incorrect syntax near ';'.
June 6, 2012 at 9:42 am
1. There is no such function in T-SQL as "get(@id)", If you want to get table id you can use SQL function OBJECT_ID('TableName')
2. The first errors you have is due to the fact that you've created the tables in the same batch as creating the procedure. Just use GO before CREATE PROC statement.
June 6, 2012 at 9:43 am
Look over the following. One thing you needed was to separate the table creation from the creation of the procedure by inserting GO before the CREATE PROCEDURE. I also made a few changes to your code.
create table encrypt_table
(
table_id int identity,
sch_name varchar(50),
table_name varchar(50),
active BIT
)
create table encrypt_table_columns
(
table_id int,
column_name varchar(50),
active BIT
)
GO
create procedure insert_table_column(@schema_name varchar(128), @table_name varchar(128), @column_name varchar(128), @active BIT)
as
declare @id int
declare @sch_name varchar(128) --the first error ('CREATE/ALTER PROCEDURE' must be the first statement in a query batch.) occurs here
declare @tab_name varchar(128)
declare @col_name varchar(128)
declare @flag_bit BIT
declare @STR varchar(max)
set @sch_name = @schema_name
set @tab_name = @table_name
set @col_name = @column_name
set @flag_bit = @active
set @id = @@identity
begin
if exists (select sch_name, table_name from encrypt_table where sch_name = @sch_name and table_name = @tab_name)
SELECT @id = table_id FROM encrypt_table where sch_name = @sch_name and table_name = @tab_name
else
insert into encrypt_table values(@id, @tab_name, @flag_bit)
if exists (select column_name from encrypt_table_columns where table_name = @tab_name and column_name = @col_name)
print 'column already exists'
else
insert into encrypt_table_columns values(@id, @col_name, @flag_bit)
select
et.sch_name,
et.table_name,
et.active,
tc.column_name,
tc.active
from
encrypt_table et
INNER JOIN encrypt_table_columns tc
ON (et.table_id = tc.table_id);
END
GO
June 6, 2012 at 9:59 am
Thank you for the help, I really appreciate it. I have updated my code, but I am still receiving the error regarding the select statement towards the end of my code. All it will tell me is that there is a syntax error near ';'. I was wondering if you had any other ideas on this? Thanks!
June 6, 2012 at 10:05 am
Try highlighting just that select statement and running it. Does it run or do you get the syntax error? If it gives the error, copy and paste only that snippet of the script here.
Jared
CE - Microsoft
June 6, 2012 at 10:09 am
blampe (6/6/2012)
Thank you for the help, I really appreciate it. I have updated my code, but I am still receiving the error regarding the select statement towards the end of my code. All it will tell me is that there is a syntax error near ';'. I was wondering if you had any other ideas on this? Thanks!
Only error I get when I run the code I posted is here:
if exists (select column_name from encrypt_table_columns where table_name = @tab_name and column_name = @col_name)
Looks like you don't have a column named table_name in the table encrypt_table_columns.
Please post the code you are running when you get your error.
June 6, 2012 at 10:10 am
Also, looks like your insert into the first table won't really work either.
June 6, 2012 at 10:17 am
The following code runs successfully on my system:
create table encrypt_table
(
table_id int identity,
sch_name varchar(50),
table_name varchar(50),
active BIT
)
create table encrypt_table_columns
(
table_id int,
column_name varchar(50),
active BIT
)
GO
create procedure insert_table_column(@schema_name varchar(128), @table_name varchar(128), @column_name varchar(128), @active BIT)
as
declare @id int
declare @sch_name varchar(128) --the first error ('CREATE/ALTER PROCEDURE' must be the first statement in a query batch.) occurs here
declare @tab_name varchar(128)
declare @col_name varchar(128)
declare @flag_bit BIT
declare @STR varchar(max)
set @sch_name = @schema_name
set @tab_name = @table_name
set @col_name = @column_name
set @flag_bit = @active
set @id = @@identity
begin
if exists (select sch_name, table_name from encrypt_table where sch_name = @sch_name and table_name = @tab_name)
SELECT @id = table_id FROM encrypt_table where sch_name = @sch_name and table_name = @tab_name;
ELSE
BEGIN
insert into encrypt_table values(@schema_name, @tab_name, @flag_bit);
SET @id = SCOPE_IDENTITY();
END
if exists (select column_name from encrypt_table_columns where table_id = @id and column_name = @col_name)
print 'column already exists';
else
insert into encrypt_table_columns values(@id, @col_name, @flag_bit);
select
et.sch_name,
et.table_name,
et.active,
tc.column_name,
tc.active
from
encrypt_table et
INNER JOIN encrypt_table_columns tc
ON (et.table_id = tc.table_id);
END
GO
June 6, 2012 at 10:44 am
Thank you all for your responses ..... I was able to get the procedure to run, but now when I call the procedure it gives me an error. It's just another of those pesky syntax errors but I'll post the procedure call below and the error if you guys have any ideas. thanks
exec insert_table_column(dbo, test1, ssn, 1) --error occurs in the procedure call
--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near 'dbo'.
June 6, 2012 at 10:47 am
blampe (6/6/2012)
Thank you all for your responses ..... I was able to get the procedure to run, but now when I call the procedure it gives me an error. It's just another of those pesky syntax errors but I'll post the procedure call below and the error if you guys have any ideas. thanksexec insert_table_column(dbo, test1, ssn, 1) --error occurs in the procedure call
--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near 'dbo'.
I'm sorry, but this one is easy. What are you trying to pass into the procedure? Character strings. How do you do that? They have to be surrounded by single quotes.
June 6, 2012 at 12:04 pm
In my code I had quotes around it (typo when I posted). Putting quotes around it does not help. Same error ... same place.
June 6, 2012 at 12:06 pm
Well then, post the actual code.
Jared
CE - Microsoft
June 6, 2012 at 12:10 pm
Sorry I posted it ^^ but it went to a new page. The procedure itself executes successfully, but when I try to execute it using the statement exec insert_table_column('dbo', 'test1', 'fname', '1') that is when the error occurs.
create procedure insert_table_column(@schema_name varchar(128), @table_name varchar(128), @column_name varchar(128), @active BIT)
as
declare @id int
declare @sch_name varchar(128)
declare @tab_name varchar(128)
declare @col_name varchar(128)
declare @flag_bit BIT
set @sch_name = @schema_name
set @tab_name = @table_name
set @col_name = @column_name
set @flag_bit = @active
set @id = @@identity
begin
if exists (select sch_name, table_name from encrypt_table where sch_name = @sch_name and table_name = @tab_name)
select @id = table_id FROM encrypt_table where sch_name = @sch_name and table_name = @tab_name;
else
begin
insert into encrypt_table values(@schema_name, @tab_name, @flag_bit);
set @id = SCOPE_IDENTITY();
end
if exists (select column_name from encrypt_table_columns where table_id = @id and column_name = @col_name)
print 'column already exists';
else
insert into encrypt_table_columns values(@id, @col_name, @flag_bit);
select
et.sch_name,
et.table_name,
et.active,
tc.column_name,
tc.active
from
encrypt_table et
INNER JOIN encrypt_table_columns tc
ON (et.table_id = tc.table_id);
end
GO
--drop table encrypt_table;
--drop table encrypt_table_columns;
--drop procedure insert_table_column;
GO
exec insert_table_column('dbo', 'test1', 'fname', '1') --error occurs in the procedure call
--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near 'dbo'.
June 6, 2012 at 12:14 pm
I missed this one as well, no parens:
exec insert_table_column 'dbo', 'test1', 'ssn', 1
June 6, 2012 at 12:21 pm
Lynn Pettis (6/6/2012)
I missed this one as well, no parens:exec insert_table_column 'dbo', 'test1', 'ssn', 1
hehehe... I would take then off of the join condition as well, it confuses me to have parentheses where they are not needed 😎
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply