June 6, 2012 at 12:25 pm
SQLKnowItAll (6/6/2012)
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 😎
I like putting parens there, helps me keep things organized, especially when things start getting nested.
June 6, 2012 at 12:27 pm
Alright got it!!! Thanks everyone for the help
June 6, 2012 at 12:30 pm
Lynn Pettis (6/6/2012)
SQLKnowItAll (6/6/2012)
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 😎
I like putting parens there, helps me keep things organized, especially when things start getting nested.
That's why I indent!!! :w00t: Just kidding, to each his own. See ya in another thread, Lynn. Blampe, glad to help 🙂
Jared
CE - Microsoft
June 6, 2012 at 12:46 pm
SQLKnowItAll (6/6/2012)
Lynn Pettis (6/6/2012)
SQLKnowItAll (6/6/2012)
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 😎
I like putting parens there, helps me keep things organized, especially when things start getting nested.
That's why I indent!!! :w00t: Just kidding, to each his own. See ya in another thread, Lynn. Blampe, glad to help 🙂
Hey, I indent as well. :w00t:
Glad we could help! 🙂
June 6, 2012 at 12:48 pm
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)
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
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply