Table name from variable in select statement

Question

Table name from variable in select statement

Viewing 10 posts - 16 through 24 (of 24 total)

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

B Hilderman SSC-Addicted Points: 435 More actions · Answer 1

Thanks Jeff. We have a database from a vendor with about 900 tables. We are changing our account structure. The account number is imbedded in over 300 tables. I was able to write a query to find the tables with reference to "account". But, is it the account we are changing the structure of or just, for example, a field with "account" in the title and unrelated to the account number we are restructuring... So, I have this working now to show me several rows of each table for those tables with "account" and can visually determine whether the output looks like our account number and then make sure we restructure every single table. Light years faster than opening each table and pondering over each opened table and after running updates against those tables making sure what I end up with is what I need.

Bottom line, sometimes it's just like magic to make the code work 😀 Not to mention the ohs and ahs from the staff who were just going to open the tables one by one. :crazy:

grtn316 Ten Centuries Points: 1239 More actions · Answer 2

Thanks for all the great help guys! My very last issue is trying to get it to accept a smalldatetime type.

I was able to Cast all the variables directly in the sql statement but when I try to cast a smalldatetime type it produces an error. For Example:

@DateFilter smalldatetime = '12/22/2004'

set @sql = 'query....' + CAST(@DateFilter AS varchar(20))

Error: incorrect syntax near '22'

Any ideas?

B Hilderman SSC-Addicted Points: 435 More actions · Answer 3

My guess would be;

CAST('2007-12-20 12:15:01.111' AS smalldatetime)

You don't have the 'time' portion.

using your date of course...

grtn316 Ten Centuries Points: 1239 More actions · Answer 4

Well, I've tried it directly from my asp.net app and directly from sql management studio and it doesn't work.

When I changed it to that format, it says:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '20'.

HOWEVER! I left it as the varchar format as it is already in smalldatetime format.

grtn316 Ten Centuries Points: 1239 More actions · Answer 5

Bingo!

CONVERT(varchar(20), @DateFilter, 1)

Spoke to soon...The query runs but it appears that it is ignoring the date filter now. So, instead of returning records that are newer than the date specified, it returns all.

This is the format being used in the database: 10/5/2007 12:00:00 AM

grtn316 Ten Centuries Points: 1239 More actions · Answer 6

This worked!

Date_Submitted > ''' + CONVERT(varchar(23), @DateFilter, 101) + '''

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

Bottom line, sometimes it's just like magic to make the code work Not to mention the ohs and ahs from the staff who were just going to open the tables one by one.

I absolutely agree... ohs and ahs sometimes make it all worth while. Thanks for the feedback and "well done" on your part all the way around!

--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)
Intro to Tally Tables and Functions

Mauve SSChampion Points: 11316 More actions · Answer 8

Rather than casting/converting all of the WHERE clause values into the dynamic SQL query string you should be using the system stored procedure sp_executesql vs. just plain EXECUTE. The EXECUTE statement does not support parameter substitution in the executed string.

It offers numerous benefits:

1. Helps prevent SQL Injection

2. Eliminates the need to CAST, CONVERT, or "escape" character strings that may contain quotes.

3. Easier to construct the dynamic SQL statement

4. Better performance

5. and more!

I use it, where needed, with parameters and it works great.

See the BOL for complete documentation.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

B Hilderman SSC-Addicted Points: 435 More actions · Answer 9

Which really brings up the whole subject of stored procedures. I'd rather use a stored procedure in my .NET code with variables than embed the code in a string. I find I can use the SP over and over from other code pages. My only one and only contribution to the future of SOA 😉

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

JohnG (12/21/2007)
Rather than casting/converting all of the WHERE clause values into the dynamic SQL query string you should be using the system stored procedure sp_executesql vs. just plain EXECUTE. The EXECUTE statement does not support parameter substitution in the executed string.
It offers numerous benefits:
1. Helps prevent SQL Injection
2. Eliminates the need to CAST, CONVERT, or "escape" character strings that may contain quotes.
3. Easier to construct the dynamic SQL statement
4. Better performance
5. and more!
I use it, where needed, with parameters and it works great.
See the BOL for complete documentation.

Yep... I agree... just be sure that your dynamic SQL doesn't exceed 4000 characters...

--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)
Intro to Tally Tables and Functions