Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

Dynamic table name in sp?

Hi,

I need to create a stored procedure that accepts a string parameter containing a table name and truncates the table if it exists and creates it if it doesn't. Is there a way to do this without creating the whole sp in a string and then executing it?

For example, I can do this:

DECLARE @.ThisQuery VARCHAR(8000)

SET @.ThisQuery = 'Create Table ' + @.TableName <--this is the parameter

SET @.ThisQuery = @.ThisQuery + '...'

EXEC (@.ThisQuery)

Is there a better way that does not involve creating a dynamic query like this?

Thanks

I would convert the varchar(8000) to varchar(max); otherwise I would be converned that you might overflow your variable. Yes, this looks like it will work. As an aside I would personally try to avoid this type of generic operation.|||

Using Dynamic SQL for schema change issues is extremely dangerous. First, there would have to be a high level of permission to accomplish the task, and second, it could leave your server open to SQL Injection attacks.

You would be 'safer' using [ sp_executesql() ], and I recommend using unicode, so [ nvarchar(max) ] would be in order.

You may find this article by Erland worth the time to read.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Thank you for this help. Yes, I understand it's dangerous, that's why I'm looking for another way to do it! The article seems great, but it is very long and verbose and after looking through it for a while now and I'm still not sure if it can be done and how. Is there possibly just some syntax someone can please give me that cuts through all this? I don't really need to understand it, I just need to know how to do it.

Many thanks.

|||

Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin

|||DECLARE @.ThisQuery VARCHAR(8000)
declare @.TableName sysname
set @.TableName = 'MyTab'
SET @.ThisQuery = 'if object_ID('''+@.TableName +''') is null '+ CHAR(13)+
'Create Table ' + @.TableName + '([Dummy] varchar(50))'
EXEC (@.ThisQuery)

|||You realize, I hope, that TRUNCATE TABLE will FAIL if there are PK-FK relationships.|||

>> Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin<<

100% agree with you here. Building tables in a stored procedure is generally a sign of using SQL in a non-preferred manner. We haven't been given any insight into the problem trying to be solved, but the "dummy" column makes it seem probably worse than it might actually be.

|||

I have to say, point well taken! After re-thinking my approach, I came up with a better way to do things that does not require a dynamic table name. Thanks.