Subroutines in TSQL

Question

Subroutines in TSQL

Viewing 15 posts - 76 through 90 (of 96 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dmeissner - Monday, August 14, 2017 12:19 PM
I already did.
"I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution. "

Funny, I don't see any code.

dmeissner Old Hand Points: 318 More actions · Answer 2

Code as requested. A little crude maybe, but it's not a Mona Lisa.

============================

/*================================================== Stored procedure from procedure database ==================================================*/

CREATE PROCEDURE dbo.sp_column_exists( @db_name VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100) ) AS

DECLARE @sql VARCHAR(5000)

SET @sql = ' SELECT 1 FROM ' + @db_name + '.dbo.syscolumns AS c INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id WHERE o.name = ''' + @table_name + ''' AND c.name = ''' + @column_name + ''' '

IF @sql <> '' EXEC(@sql) ELSE SELECT 'Invalid Object' GO

/*================================================== Call to stored procedure from another database. ==================================================*/ -- drop temporary table if exists -- IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- declare variables -- DECLARE @db_name VARCHAR(100) DECLARE @table_name VARCHAR(100) DECLARE @column_name VARCHAR(100) DECLARE @object_exists VARCHAR(20)

-- set variables for lookup -- SET @db_name = 'my_database' SET @table_name = 'my_table' SET @column_name = 'my_column' SET @object_exists = 0

-- create temporary table -- CREATE TABLE #tmp(object_exists varchar(20))

-- insert result of procedure call into temporary table -- INSERT INTO #tmp(object_exists) EXEC procedure_database.dbo.sp_column_exists @db_name, @table_name, @column_name

-- set object_exists variable to result of procedure call -- SELECT @object_exists = object_exists FROM #tmp

-- return object exists value (0=false, 1=true) -- SELECT @object_exists

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

dmeissner - Monday, August 14, 2017 3:19 PM
Code as requested. A little crude maybe, but it's not a Mona Lisa.
============================
/*================================================== Stored procedure from procedure database ==================================================*/
CREATE PROCEDURE dbo.sp_column_exists( @db_name VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100) ) AS
DECLARE @sql VARCHAR(5000)
SET @sql = ' SELECT 1 FROM ' + @db_name + '.dbo.syscolumns AS c INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id WHERE o.name = ''' + @table_name + ''' AND c.name = ''' + @column_name + ''' '
IF @sql <> '' EXEC(@sql) ELSE SELECT 'Invalid Object' GO
/*================================================== Call to stored procedure from another database. ==================================================*/ -- drop temporary table if exists -- IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
-- declare variables -- DECLARE @db_name VARCHAR(100) DECLARE @table_name VARCHAR(100) DECLARE @column_name VARCHAR(100) DECLARE @object_exists VARCHAR(20)
-- set variables for lookup -- SET @db_name = 'my_database' SET @table_name = 'my_table' SET @column_name = 'my_column' SET @object_exists = 0
-- create temporary table -- CREATE TABLE #tmp(object_exists varchar(20))
-- insert result of procedure call into temporary table -- INSERT INTO #tmp(object_exists) EXEC procedure_database.dbo.sp_column_exists @db_name, @table_name, @column_name
-- set object_exists variable to result of procedure call -- SELECT @object_exists = object_exists FROM #tmp
-- return object exists value (0=false, 1=true) -- SELECT @object_exists

I already see one issue with this code. No schema. It is possible to have the same table name in a database with different column names under different schemas.

dmeissner Old Hand Points: 318 More actions · Answer 4

Lynn Pettis - Monday, August 14, 2017 3:38 PM
dmeissner - Monday, August 14, 2017 3:19 PM
Code as requested. A little crude maybe, but it's not a Mona Lisa.
============================
/*================================================== Stored procedure from procedure database ==================================================*/
CREATE PROCEDURE dbo.sp_column_exists( @db_name VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100) ) AS
DECLARE @sql VARCHAR(5000)
SET @sql = ' SELECT 1 FROM ' + @db_name + '.dbo.syscolumns AS c INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id WHERE o.name = ''' + @table_name + ''' AND c.name = ''' + @column_name + ''' '
IF @sql <> '' EXEC(@sql) ELSE SELECT 'Invalid Object' GO
/*================================================== Call to stored procedure from another database. ==================================================*/ -- drop temporary table if exists -- IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
-- declare variables -- DECLARE @db_name VARCHAR(100) DECLARE @table_name VARCHAR(100) DECLARE @column_name VARCHAR(100) DECLARE @object_exists VARCHAR(20)
-- set variables for lookup -- SET @db_name = 'my_database' SET @table_name = 'my_table' SET @column_name = 'my_column' SET @object_exists = 0
-- create temporary table -- CREATE TABLE #tmp(object_exists varchar(20))
-- insert result of procedure call into temporary table -- INSERT INTO #tmp(object_exists) EXEC procedure_database.dbo.sp_column_exists @db_name, @table_name, @column_name
-- set object_exists variable to result of procedure call -- SELECT @object_exists = object_exists FROM #tmp
-- return object exists value (0=false, 1=true) -- SELECT @object_exists
I already see one issue with this code. No schema. It is possible to have the same table name in a database with different column names under different schemas.

As I mentioned in one of my original posts regarding the desire to be able to call a sub-routine inside of a t-sql statement, that might check for the existence of a column in a specified table in a specified database, and without creating a function in each of many databases, this process does exactly that.

This of course assumes that the user knows the database, table, and column that he is checking for. Adjust as needed.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

dmeissner - Monday, August 14, 2017 4:07 PM
Lynn Pettis - Monday, August 14, 2017 3:38 PM
dmeissner - Monday, August 14, 2017 3:19 PM
Code as requested. A little crude maybe, but it's not a Mona Lisa.
============================
/*================================================== Stored procedure from procedure database ==================================================*/
CREATE PROCEDURE dbo.sp_column_exists( @db_name VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100) ) AS
DECLARE @sql VARCHAR(5000)
SET @sql = ' SELECT 1 FROM ' + @db_name + '.dbo.syscolumns AS c INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id WHERE o.name = ''' + @table_name + ''' AND c.name = ''' + @column_name + ''' '
IF @sql <> '' EXEC(@sql) ELSE SELECT 'Invalid Object' GO
/*================================================== Call to stored procedure from another database. ==================================================*/ -- drop temporary table if exists -- IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
-- declare variables -- DECLARE @db_name VARCHAR(100) DECLARE @table_name VARCHAR(100) DECLARE @column_name VARCHAR(100) DECLARE @object_exists VARCHAR(20)
-- set variables for lookup -- SET @db_name = 'my_database' SET @table_name = 'my_table' SET @column_name = 'my_column' SET @object_exists = 0
-- create temporary table -- CREATE TABLE #tmp(object_exists varchar(20))
-- insert result of procedure call into temporary table -- INSERT INTO #tmp(object_exists) EXEC procedure_database.dbo.sp_column_exists @db_name, @table_name, @column_name
-- set object_exists variable to result of procedure call -- SELECT @object_exists = object_exists FROM #tmp
-- return object exists value (0=false, 1=true) -- SELECT @object_exists
I already see one issue with this code. No schema. It is possible to have the same table name in a database with different column names under different schemas.
As I mentioned in one of my original posts regarding the desire to be able to call a sub-routine inside of a t-sql statement, that might check for the existence of a column in a specified table in a specified database, and without creating a function in each of many databases, this process does exactly that.
This of course assumes that the user knows the database, table, and column that he is checking for. Adjust as needed.

Once more with feeling, You are not taking into account schema in your code.

dmeissner Old Hand Points: 318 More actions · Answer 6

Not sure exactly what you are getting at. This is an ad-hoc process that can be used for any schema with minimal tweaking. Schema is relative. The user has asked for an example of how the functionality can be performed. It is only an example.

Are you implying that there is no value to the process? If there is value, but you have an improvement to propose, feel free to add to it.

This is what the forum is about is it not? Or is it just for criticizing. If so what's the use.

J Livingston, I hope it can help you with your issue.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

dmeissner - Monday, August 14, 2017 5:48 PM
Not sure exactly what you are getting at. This is an ad-hoc process that can be used for any schema with minimal tweaking. Schema is relative. The user has asked for an example of how the functionality can be performed. It is only an example.
Are you implying that there is no value to the process? If there is value, but you have an improvement to propose, feel free to add to it.
This is what the forum is about is it not? Or is it just for criticizing. If so what's the use.
J Livingston, I hope it can help you with your issue.

There's definitely value in what you posted for code and, yes, it does demonstrate what the OP requested so very cool thing there. Thanks for taking the time.

Shifting gears to a more friendly dialog myself, remember that you came into this conversation swinging a bat so some people are going to respond a bit tersely to what you posted instead of in the helpful manner and tone that they normally use. The whole point of this type of forum is to not only come up with a solution or example, but people also tend to examine the posted solutions and examples and point out flaws that may appear in the code or the dialog. Such discusses sometime go well beyond the original scope of the question asked and not only "Teach a man to fish", but also tell them what size line and hook to use, which bait to use and why, how to avoid snarling the reel, and what to do if the reel does snarl. It's part of the reason why so many folks originally seemed to ask useless questions and make rhetorical observations... they wanted to find out more about what the OP was really trying to do with "subroutines" so that they could provide the best, safest, fastest, most useful answer possible.

I'll post an example of what I mean after I get home tonight.

p.s. Thanks for the comments in the code. A lot of people don't do that. It really helps newbies and others that may be unfamiliar with what the code is doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

dmeissner - Monday, August 14, 2017 5:48 PM
Not sure exactly what you are getting at. This is an ad-hoc process that can be used for any schema with minimal tweaking. Schema is relative. The user has asked for an example of how the functionality can be performed. It is only an example.
Are you implying that there is no value to the process? If there is value, but you have an improvement to propose, feel free to add to it.
This is what the forum is about is it not? Or is it just for criticizing. If so what's the use.
J Livingston, I hope it can help you with your issue.

Well, I am sorry if you can't figure out what I am telling you without having it handed to you on a silver platter. Here is the gist of it. At no time does your code consider the fact that you could have a table named MyTable in the dbo schema and a schema named test, i.e. dbo.MyTable and test.MyTable. Your check for the table name MyTable will return two possibilities as you fail to account for schema in your code. In addition, even though both tables are named MyTable it is totally possible that they could have totally different columns defined. They could have the same names yet defined differently as well.

Does this help?

And yes, Jeff hit the nail on the head. I was being terse in my responses. I was hoping that my simple hint about the issue with your code would have made you look more closely at what you were trying to accomplish. I even told you that the problem was the fact you ignored schema in your code.

dmeissner Old Hand Points: 318 More actions · Answer 9

Lyle,

I'm sorry you don't understand that this is a hypothetical example. There is no schema except that which the viewer chooses. Variables such as my_database, my_table, and my_column are clearly placeholders where one can simply swap out to match their schema. Nothing more.

I didn't feel it was necessary to include code for creating tables and inserting records, as that was not the object of discussion. I think most people would recognize it for what it is. If not, they merely need to ask.

If you would like to add some sql for that, be my guest. What would really be helpful is if people tried out the solution and commented on how it worked or didn't work for them for the sake of others looking for a solution. That would be extremely productive.

Again, I would like to say that my initial post after this thread was dormant for so long was plain and simple sarcasm. It was not directed at anybody. If fact, I never imagined that anyone would still be subscribed to the thread. I thought it would most likely be seen only by people searching for the same topic as I was.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

dmeissner - Tuesday, August 15, 2017 10:54 PM
Lyle,
I'm sorry you don't understand that this is a hypothetical example. There is no schema except that which the viewer chooses. Variables such as my_database, my_table, and my_column are clearly placeholders where one can simply swap out to match their schema. Nothing more.
I didn't feel it was necessary to include code for creating tables and inserting records, as that was not the object of discussion. I think most people would recognize it for what it is. If not, they merely need to ask.
If you would like to add some sql for that, be my guest. What would really be helpful is if people tried out the solution and commented on how it worked or didn't work for them for the sake of others looking for a solution. That would be extremely productive.
Again, I would like to say that my initial post after this thread was dormant for so long was plain and simple sarcasm. It was not directed at anybody. If fact, I never imagined that anyone would still be subscribed to the thread. I thought it would most likely be seen only by people searching for the same topic as I was.

Whoa. In your code no schema can be selected because you totally ignore the schema, period. You have placeholders for the database, table, and column, nothing else. Using a three part name convention (database.schema.table) you ignore things like MyDatabase.dbo.MyTable and MyDatabase.MySchema.MyTable for tables.

So, how would you modify your code to account for different schema in a database? I already know how I would do it, I want to see if you know or can figure it out.

dmeissner Old Hand Points: 318 More actions · Answer 11

My code clearly illustrates...
@db_name + '.dbo.syscolumns
Anyone with eyes can see it and any sql developer can figure out how to apply it to their setup.

How is it that you know how you would do it when no one else in the whole sql world can figure it out without me including some schema definition.

Lynn you have a problem and rather than asking legitimate questions you choose to try and "one up" me as though I know nothing for some reason.

You have issues for sure. You seem to thrive off of criticism. Come down from your pedestal and get over yourself. Make yourself useful to someone for God's sake.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

dmeissner - Wednesday, August 16, 2017 4:48 PM
My code clearly illustrates...
@db_name + '.dbo.syscolumns
Anyone with eyes can see it and any sql developer can figure out how to apply it to their setup.
How is it that you know how you would do it when no one else in the whole sql world can figure it out without me including some schema definition.
Lynn you have a problem and rather than asking legitimate questions you choose to try and "one up" me as though I know nothing for some reason.
You have issues for sure. You seem to thrive off of criticism. Come down from your pedestal and get over yourself. Make yourself useful to someone for God's sake.

You are completely missing what I am talking about. I have simply pointed out a problem with your code. I know how I would fix it and I am trying to encourage you to see the problem, but you seem blind to what I am trying to accomplish. I am not on a pedestal and definitely have nothing get over. I have been extremely useful to many others on this site.

Again, take a look at what I have been saying and do some research. And just so you know, I am NOT talking about @db_name + '.dbo.syscolumns', I am talking about the schema of the table you are looking for in the procedure.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

dmeissner - Tuesday, August 15, 2017 10:54 PM
Lyle,
I'm sorry you don't understand that this is a hypothetical example. There is no schema except that which the viewer chooses. Variables such as my_database, my_table, and my_column are clearly placeholders where one can simply swap out to match their schema. Nothing more.
I didn't feel it was necessary to include code for creating tables and inserting records, as that was not the object of discussion. I think most people would recognize it for what it is. If not, they merely need to ask.
If you would like to add some sql for that, be my guest. What would really be helpful is if people tried out the solution and commented on how it worked or didn't work for them for the sake of others looking for a solution. That would be extremely productive.
Again, I would like to say that my initial post after this thread was dormant for so long was plain and simple sarcasm. It was not directed at anybody. If fact, I never imagined that anyone would still be subscribed to the thread. I thought it would most likely be seen only by people searching for the same topic as I was.

"Hypothetical code"? You did claim to have created it for us on an old SQL Server 2000 box and the presence of "dbo.sysobjects" does seem to confirm that.

dmeissner - Monday, August 14, 2017 12:19 PM
I already did.
"I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution. "

That, notwithstanding and, like I said previously, it does demonstrate a possible solution for the problem that the OP stated.

Now, let's see what else it does... I'll explain why all of this is important in a minute. Here's the stored procedure you posted, just so we don't have to scroll up to get back to it.

/*================================================== Stored procedure from procedure database ==================================================*/CREATE PROCEDURE dbo.sp_column_exists(
@db_name VARCHAR(100),
@table_name VARCHAR(100),
@column_name VARCHAR(100)
) AS

DECLARE @sql VARCHAR(5000)

SET @sql = '
SELECT 1
FROM ' + @db_name + '.dbo.syscolumns AS c
INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id
WHERE o.name = ''' + @table_name + '''
AND c.name = ''' + @column_name + '''
'

IF @sql <> ''
EXEC(@sql)
ELSE
SELECT 'Invalid Object'
GO

That code has some unintended consequences for the owner even if they do make it work with the legacy reference of dbo.sysobjects. Some of the consequences can be quite troublesome not only to the OP but also for anyone reading the code that might not know better.

1. Going first with Lynn's objection to your code, you have, indeed, included the schema name (owner name, back in 2000) for dbo.sysobjects. That's not what Lynn is referring to. Even back in 2000, you did have the capability of identically named tables with different owners.

Now, let's setup a not-so-uncommon scenario. The "dbo.my_table" table is the "official" table that all the other developers are using to develop code with and it does NOT have "my_column" in it. I'm also a developer and I'm getting ready to test a change to the table. Being the courteous developer, I don't want to interfere with what the other developers are doing so I name my table as "jbm.my_table" and add the "my_column" column to it. Another developer is working on the same project and uses your code to determine if the "official" "my_table" table already has the "my_column" column in it or not. He runs your code to find out. Rather than it correctly returning an "Invalid Object" (as you've designed it) because the official table, "dbo.my_table" doesn't already contain the column, it will return a 1 because it found my "jbm.my_table" table and the "my_column" column is present there.

A worse scenario would be if a new owner/schema name and similarly named table were added to production.

The point is that it is a worst practice, for the reason stated and more, to not use the 2 part naming convention as Lynn has been trying to tell you. Newbies that read your code example may not know all of that and write code that gets deployed to production that could cause such a problem as stated. In other words, you haven't taken any responsibility for the possibility of unintended consequences and have failed to set a best-practices example for those that may be reading the post for reasons other than the original purpose.

2. Speaking of unintended consequences... while it's not likely that the code you wrote would ever be used in a public facing application, it does provide an example of Dynamic SQL. Someone needing such an example doesn't know the consequences of such code in a public facing situation and so they would use your example to write code to meet their own needs in a public facing application... without ever realizing that it's a classic example of how and why SQL Injection is still the leading cause for security breaches.

3. Another problem with the code is that data-type matching was not observed. The underlying values of the database and column names in their respective tables are of the NVARCHAR(128) (or SYSNAME data type alias) and you used VARCHAR(100). While that won't necessarily cause a problem in your code, someone using your code as a model might and, for them, it could create a major performance problem.

4. Your code will recompile for every table and column not already looked up since the last time cache was cleared because you also didn't follow the best practice of using code that can use what's available in cache. Again, this may not be a problem for your code but there may be serious unintended consequences for someone that uses your code as a model for how to use Dynamic SQL. I will admit that this code will necessarily need to cache at least one copy for every database it's played against but it unnecessarily will need to create multiple copies for each database it's played against because of the way the table name and column name are rendered in the code construction.

5. You've mixed the data type of the returned value. If the column is found for the given database and table, you return a numeric 1. If it is not found, you return a VARCHAR-based character string.

6. You've named the stored procedure using Hungarian Notation, which most heavy hitters consider to be a totally unnecessary equivalent to "tag bloat". Worse than that, you've used the Microsoft Prefix Naming Convention of "sp_", which doesn't actually stand for "stored procedure" but means "SPecial" in Microsoft terms. It's what MS uses for "special" items in the Master database. Unfortunately, the prefix is general used for "stored procedures" in the Master database, which is where the confusion comes from. It can also (reportedly, I've not tested it because I don't use it to name stored procedures in user databases) have a significant impact on heavy-hit code because of some alleged interplay (again, I've not personally tested it but I'll take Microsoft's word for it) with the Master database.

I won't gig you on one of Lynn's pet peeves about not using semi-colons because it is code for SQL Server 2000 and not using semi-colons back then wasn't deprecated like it is now.

You came into this thread swinging the "lame" bat at all sorts of people but fail to understand how "lame" your code is and the unintended consequences it can have for someone that doesn't know better. Lynn isn't really trying to bust your chops (well, maybe a little bit 😛 ). He's trying to get you to understand the consequences on just one of many points. You're still carrying the chip on your shoulder because you didn't understand when you came into the thread and you still don't understand what's going on. Yes, you demonstrated a method to satisfy the OP but you've created serious unintended consequences for anyone that uses your code as a model for Dynamic SQL and doesn't know enough to recognize the dangers. Saying that the code "isn't the Mona Lisa" is a good warning for those that understand all the things wrong with the code but not for those that don't.

So now it's time to take the chip off your shoulder, humble down a bit, and tell Lynn, "Ah! Now I understand what you're trying to get at". 😉 Humble, approachable, and teachable is good and it'll go a long way for you when a potential employee happens to stumble across one of your posts. 😀

I'm going to take a break and then come back with some code. It won't be the "Mona Lisa" but it won't be subject to SQL Injection or coming up with the wrong answer if another identically named table exists in a different schema either. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden - Wednesday, August 16, 2017 10:29 PM
dmeissner - Tuesday, August 15, 2017 10:54 PM
Lyle,
I'm sorry you don't understand that this is a hypothetical example. There is no schema except that which the viewer chooses. Variables such as my_database, my_table, and my_column are clearly placeholders where one can simply swap out to match their schema. Nothing more.
I didn't feel it was necessary to include code for creating tables and inserting records, as that was not the object of discussion.  I think most people would recognize it for what it is. If not, they merely need to ask.
If you would like to add some sql for that, be my guest. What would really be helpful is if people tried out the solution and commented on how it worked or didn't work for them for the sake of others looking for a solution. That would be extremely productive.
Again, I would like to say that my initial post after this thread was dormant for so long was plain and simple sarcasm. It was not directed at anybody. If fact, I never imagined that anyone would still be subscribed to the thread. I thought it would most likely be seen only by people searching for the same topic as I was.
"Hypothetical code"? You did claim to have created it for us on an old SQL Server 2000 box and the presence of "dbo.sysobjects" does seem to confirm that.
dmeissner - Monday, August 14, 2017 12:19 PM
I already did.
"I did run across the thread searching for the same thing for an old mssql 2000 box. I ultimately created a single procedure on one db that could be referenced from another, passing the requesting db name to the procedure so it then could perform the task (like checking if an object exists) against the requesting db. Sort of cumbersome, but much better than installing a solution in each db and for sql2000, a pretty decent solution. "
That, notwithstanding and, like I said previously, it does demonstrate a possible solution for the problem that the OP stated.
Now, let's see what else it does... I'll explain why all of this is important in a minute. Here's the stored procedure you posted, just so we don't have to scroll up to get back to it.
/*================================================== Stored procedure from procedure database ==================================================*/CREATE PROCEDURE dbo.sp_column_exists(
@db_name VARCHAR(100),
@table_name VARCHAR(100),
@column_name VARCHAR(100)
) AS
DECLARE @sql VARCHAR(5000)
SET @sql = '
SELECT 1
FROM ' + @db_name + '.dbo.syscolumns AS c
INNER JOIN ' + @db_name + '.dbo.sysobjects AS o ON c.id = o.id
WHERE o.name = ''' + @table_name + '''
AND c.name = ''' + @column_name + '''
'
IF @sql <> ''
    EXEC(@sql)
ELSE
    SELECT 'Invalid Object'
GO
That code has some unintended consequences for the owner even if they do make it work with the legacy reference of dbo.sysobjects. Some of the consequences can be quite troublesome not only to the OP but also for anyone reading the code that might not know better.
1. Going first with Lynn's objection to your code, you have, indeed, included the schema name (owner name, back in 2000) for dbo.sysobjects. That's not what Lynn is referring to. Even back in 2000, you did have the capability of identically named tables with different owners.
Now, let's setup a not-so-uncommon scenario. The "dbo.my_table" table is the "official" table that all the other developers are using to develop code with and it does NOT have "my_column" in it. I'm also a developer and I'm getting ready to test a change to the table. Being the courteous developer, I don't want to interfere with what the other developers are doing so I name my table as "jbm.my_table" and add the "my_column" column to it. Another developer is working on the same project and uses your code to determine if the "official" "my_table" table already has the "my_column" column in it or not. He runs your code to find out. Rather than it correctly returning an "Invalid Object" (as you've designed it) because the official table, "dbo.my_table" doesn't already contain the column, it will return a 1 because it found my "jbm.my_table" table and the "my_column" column is present there.
A worse scenario would be if a new owner/schema name and similarly named table were added to production.
The point is that it is a worst practice, for the reason stated and more, to not use the 2 part naming convention as Lynn has been trying to tell you. Newbies that read your code example may not know all of that and write code that gets deployed to production that could cause such a problem as stated. In other words, you haven't taken any responsibility for the possibility of unintended consequences and have failed to set a best-practices example for those that may be reading the post for reasons other than the original purpose.
2. Speaking of unintended consequences... while it's not likely that the code you wrote would ever be used in a public facing application, it does provide an example of Dynamic SQL. Someone needing such an example doesn't know the consequences of such code in a public facing situation and so they would use your example to write code to meet their own needs in a public facing application... without ever realizing that it's a classic example of how and why SQL Injection is still the leading cause for security breaches.
3. Another problem with the code is that data-type matching was not observed. The underlying values of the database and column names in their respective tables are of the NVARCHAR(128) (or SYSNAME data type alias) and you used VARCHAR(100). While that won't necessarily cause a problem in your code, someone using your code as a model might and, for them, it could create a major performance problem.
4. Your code will recompile for every table and column not already looked up since the last time cache was cleared because you also didn't follow the best practice of using code that can use what's available in cache. Again, this may not be a problem for your code but there may be serious unintended consequences for someone that uses your code as a model for how to use Dynamic SQL. I will admit that this code will necessarily need to cache at least one copy for every database it's played against but it unnecessarily will need to create multiple copies for each database it's played against because of the way the table name and column name are rendered in the code construction.
5. You've mixed the data type of the returned value. If the column is found for the given database and table, you return a numeric 1. If it is not found, you return a VARCHAR-based character string.
6. You've named the stored procedure using Hungarian Notation, which most heavy hitters consider to be a totally unnecessary equivalent to "tag bloat". Worse than that, you've used the Microsoft Prefix Naming Convention of "sp_", which doesn't actually stand for "stored procedure" but means "SPecial" in Microsoft terms. It's what MS uses for "special" items in the Master database. Unfortunately, the prefix is general used for "stored procedures" in the Master database, which is where the confusion comes from. It can also (reportedly, I've not tested it because I don't use it to name stored procedures in user databases) have a significant impact on heavy-hit code because of some alleged interplay (again, I've not personally tested it but I'll take Microsoft's word for it) with the Master database.
I won't gig you on one of Lynn's pet peeves about not using semi-colons because it is code for SQL Server 2000 and not using semi-colons back then wasn't deprecated like it is now.
You came into this thread swinging the "lame" bat at all sorts of people but fail to understand how "lame" your code is and the unintended consequences it can have for someone that doesn't know better. Lynn isn't really trying to bust your chops (well, maybe a little bit 😛 ). He's trying to get you to understand the consequences on just one of many points. You're still carrying the chip on your shoulder because you didn't understand when you came into the thread and you still don't understand what's going on. Yes, you demonstrated a method to satisfy the OP but you've created serious unintended consequences for anyone that uses your code as a model for Dynamic SQL and doesn't know enough to recognize the dangers. Saying that the code "isn't the Mona Lisa" is a good warning for those that understand all the things wrong with the code but not for those that don't.
So now it's time to take the chip off your shoulder, humble down a bit, and tell Lynn, "Ah! Now I understand what you're trying to get at". 😉 Humble, approachable, and teachable is good and it'll go a long way for you when a potential employee happens to stumble across one of your posts. 😀
I'm going to take a break and then come back with some code. It won't be the "Mona Lisa" but it won't be "finger painting" either. 😉

Very well said, Jeff. Now, should I point out another potential issue? There is no restriction on sysobjects regarding the type of object. This means that if a developer using this procedure is looking for a table named CurrentEmployees and this table does not exist, but a view or procedure with this name does it could also return an invalid result. Just thought I'd add another point to list.

dmeissner Old Hand Points: 318 More actions · Answer 15

Listen guys, I apologize for getting uptight. Maybe you guys do things different here by laying out the whole playing field when you post code, but the snippet I provided is only a snippet and is not intended to define a complete data schema with which a user can test. It is designed to simply show how a procedure in one database can search for an object in another. There is no specific schema to validate or verify or otherwise check as anything that relates to the database being searched comes in the form of parameters. The procedure knows nothing else except that which is passed to it. I do not even know if this works in any recent versions of MS SQL, but I am sure that any developer can take it and run with it to find out the exact syntax that will work for the version they are using.

The procedure can be dressed up however anyone likes, but that is not the point of the snippet. No one needs a tutorial on how to create tables and load data into them. No tutorial on how to validate the incoming parameters or make sure that the database even exists. Anyone can add that however they like. This is designed to check for something and return a message if it does not exist. And I believe that I mentioned that it was a crude example. That would mean that it has a lot of room for improvement. It is not, as I said, a Mona Lisa.

This is designed for a seasoned programmer that does not need the whole landscape included to obfuscate the point being made. It should be a foregone conclusion that if any developer is using this, they are already extremely familiar with the databases they are working with and can interpret the snippet well enough to apply it to their schema. Any schema verification should have already been performed. This is not for a newbie. If a newbie uses it, (s)he will most likely run into issues that have to be troubleshooted, but that too is not the point being made.

The procedure is in no way altering any schema or data on the referring database either. It is a simple check for an object and I purposely did not include a bunch of other material that will confuse the matter. I simply wanted to show exactly the parts that perform exactly the need. That is it. I already said that if anyone has anything that they would like to contribute, they should feel free to post it. Lynn, that means if you think a schema should be provided, you have the right to post that. If someone can use it...great.

The biggest issue I have here is that up to now, neither of you have posted anything but criticism, because you were both offended by sarcasm that was not even directed at you. Read the whole thread again from my first post and consider my sarcasm and look at how you responded. And then to have Lynn tell me that he knows how he would fix it but wanted to see if I knew. Really? Honestly, I gave up that BS in 2nd grade. I am sorry, but read it again. How productive is that.

We have gone back and forth for days about semantics. Has there been any fruit produced from it? None whatsoever. This whole discussion has contributed absolutely nothing to the reader looking for a solution. If this were a work environment we would have been fired for wasting time.

Listen, I hope you can understand where I am coming from. I am not attacking you. I am simply trying to keep it simple. I wish you both the best. If you have anything (schema included) to contribute I will check it out. Other than that, I have spent too much time on this thread already. Life goes on and I prefer to spend my free time with my son who is having a difficult time with his.

Take care.