Querying a Table Using Dynamic SQL

Question

Post reply

Querying a Table Using Dynamic SQL

Jeff K

SSC-Addicted

Points: 417
More actions
March 3, 2010 at 11:32 am

#221235

I have run into an interesting dilemma. I am working on a stored procedure used by one of our applications. Table Name is being passed in as a variable and I need to query that table. As part of the result set, I need to return rownumber. I handled this by using SELECT INTO and IDENTITY (INT, 1, 1).
DECLARE @V_QUERY_STR VARCHAR(MAX),
@V_TABLE_NAME VARCHAR(50),
@V_START_ROW INT,
@V_QUERY_CNT INT
SELECT @V_TABLE_NAME = 'AHL_Deed', @V_START_ROW = 1
SELECT @V_QUERY_STR = 'SELECT IDENTITY(INT, 1,1) as ROWINT, TN.* INTO #temp4 '
+ 'FROM ' + @V_TABLE_NAME + ' TN '
+ @V_ORDER_BY_QUERY
+ 'DECLARE @V_QUERY_CNT INT;'
+ 'SELECT @V_QUERY_CNT = COUNT(*) FROM ' + @V_TABLE_NAME + ';'
+ 'SELECT * FROM #temp4 WHERE ROWINT BETWEEN ' + CAST(@V_START_ROW AS VARCHAR) + ' AND ' + CAST(COALESCE(@V_QUERY_CNT, @V_START_ROW) AS VARCHAR)
EXECUTE (@V_QUERY_STR)
Here's the catch...the AHL_Deed table already has an identity column so I get the following error:
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table '#temp4', which already has column 'Deed_ID' that inherits the identity property.
My only other thought would be to incorporate a cursor to handle the insert, but I'm not sure how I could incorporate the row number column. Any suggestions?

Viewing 15 posts - 1 through 15 (of 16 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Did you look into ROW_NUMBER function (assuming you're using SQL2005)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff K SSC-Addicted Points: 417 More actions · Answer 2

I did look into ROW_NUMBER as an option. I could use it to return the correct rows, but the application needs the actual value as well.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Jeff Kring (3/3/2010)
I did look into ROW_NUMBER as an option. I could use it to return the correct rows, but the application needs the actual value as well.

I don't understand what you refer to as "actual value". Please elaborate.

The best way would be to provide ddl and sample data for two sample table including expected result so we have something to play with.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff K SSC-Addicted Points: 417 More actions · Answer 4

CREATE TABLE deed (deed_id INT IDENTITY(1,1), deed_name VARCHAR(25))

GO

INSERT INTO deed (deed_name) VALUES ('Deed F')

INSERT INTO deed (deed_name) VALUES ('Deed C')

INSERT INTO deed (deed_name) VALUES ('Deed I')

INSERT INTO deed (deed_name) VALUES ('Deed G')

INSERT INTO deed (deed_name) VALUES ('Deed B')

INSERT INTO deed (deed_name) VALUES ('Deed L')

INSERT INTO deed (deed_name) VALUES ('Deed D')

INSERT INTO deed (deed_name) VALUES ('Deed A')

INSERT INTO deed (deed_name) VALUES ('Deed J')

INSERT INTO deed (deed_name) VALUES ('Deed H')

INSERT INTO deed (deed_name) VALUES ('Deed K')

INSERT INTO deed (deed_name) VALUES ('Deed E')

GO

DECLARE @V_QUERY_STR VARCHAR(MAX),

@V_TABLE_NAME VARCHAR(50),

@V_START_ROW INT,

@V_QUERY_CNT INT,

@V_ORDER_BY_QUERY VARCHAR(50)

SELECT @V_TABLE_NAME = 'deed', @V_START_ROW = 1, @V_ORDER_BY_QUERY = 'ORDER BY deed_name'

SELECT @V_QUERY_STR = 'SELECT IDENTITY(INT, 1,1) as ROWINT, TN.* INTO #temp4 '

+ ' FROM ' + @V_TABLE_NAME + ' TN '

+ @V_ORDER_BY_QUERY

+ ' DECLARE @V_QUERY_CNT INT;'

+ ' SELECT @V_QUERY_CNT = COUNT(*) FROM ' + @V_TABLE_NAME + ';'

+ ' SELECT * FROM #temp4 WHERE ROWINT BETWEEN ' + CAST(@V_START_ROW AS VARCHAR) + ' AND ' + CAST(COALESCE(@V_QUERY_CNT, @V_START_ROW) AS VARCHAR)

EXECUTE (@V_QUERY_STR)

The result set I need would be:

1 Deed A

2 Deed B

3 Deed C

4 Deed D

...

LutzM SSC Guru Points: 107049 More actions · Answer 5

Would something like the following will do it?

Side note: this will only work if you always provide an ORDER BY clause since this is required for the ROW_NUMBER function....

SELECT @V_QUERY_STR = 'SELECT ROW_NUMBER() OVER(' + @V_ORDER_BY_QUERY +')

as ROWINT, TN.* INTO #temp4 '

+ ' FROM ' + @V_TABLE_NAME + ' TN '

+ @V_ORDER_BY_QUERY

+ ' DECLARE @V_QUERY_CNT INT;'

+ ' SELECT @V_QUERY_CNT = COUNT(*) FROM ' + @V_TABLE_NAME + ';'

+ ' SELECT * FROM #temp4 WHERE ROWINT BETWEEN ' + CAST(@V_START_ROW AS VARCHAR) + ' AND ' + CAST(COALESCE(@V_QUERY_CNT, @V_START_ROW) AS VARCHAR)

EXECUTE (@V_QUERY_STR)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 6

USE dbTools

GO

CREATE TABLE deed (deed_id INT IDENTITY(1,1), deed_name VARCHAR(25))

GO

INSERT INTO deed (deed_name) VALUES ('Deed F')

INSERT INTO deed (deed_name) VALUES ('Deed C')

INSERT INTO deed (deed_name) VALUES ('Deed I')

INSERT INTO deed (deed_name) VALUES ('Deed G')

INSERT INTO deed (deed_name) VALUES ('Deed B')

INSERT INTO deed (deed_name) VALUES ('Deed L')

INSERT INTO deed (deed_name) VALUES ('Deed D')

INSERT INTO deed (deed_name) VALUES ('Deed A')

INSERT INTO deed (deed_name) VALUES ('Deed J')

INSERT INTO deed (deed_name) VALUES ('Deed H')

INSERT INTO deed (deed_name) VALUES ('Deed K')

INSERT INTO deed (deed_name) VALUES ('Deed E')

GO

DECLARE @V_QUERY_STR VARCHAR(MAX),

@V_TABLE_NAME VARCHAR(50),

@V_START_ROW INT,

@V_QUERY_CNT INT,

@V_ORDER_BY_QUERY VARCHAR(50)

SELECT @V_TABLE_NAME = 'deed', @V_START_ROW = 1, @V_ORDER_BY_QUERY = 'ORDER BY deed_name'

SELECT @V_QUERY_STR = 'SELECT ROW_NUMBER() OVER (ORDER BY deed_name) as ROWINT, TN.* INTO #temp4 '

+ ' FROM ' + @V_TABLE_NAME + ' TN '

+ @V_ORDER_BY_QUERY

+ ' DECLARE @V_QUERY_CNT INT;'

+ ' SELECT @V_QUERY_CNT = COUNT(*) FROM ' + @V_TABLE_NAME + ';'

+ ' SELECT * FROM #temp4'

EXECUTE (@V_QUERY_STR)

Converting oxygen into carbon dioxide, since 1955.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

keep in mind that lutz' version will keep the identity on the deed_id column. Given the usage you're describing it shouldn't make a diffierence, but this means the DEED_ID would increment if another row were to be inserted, and not the new rownumber.

If that doesn't fit your usage, you'd have to explicitly pass the columns from the table, using

cast(deed_id as int) deed_int

to "disable" the identity from the existing column.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff K SSC-Addicted Points: 417 More actions · Answer 8

Much, much closer. Still a couple things to work out, but now I understand how ROW_NUMBER works. I completely misunderstood it before.

LutzM SSC Guru Points: 107049 More actions · Answer 9

@steve-2:

The disadvantage of your solution is that you have to have a aclumn named "deed_name" within each table you want to apply that dynamic query to.

Therefore, I decided to use a variable for the OVER clause, too.

It's also easier to control the order of the output values. Example: You could easily run it with

@V_ORDER_BY_QUERY = 'ORDER BY deed_name DESC '

without any changes to the dynamic part.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 10

Yea, I noticed that, but I hard-coded the order by thinking he could adapt that if needed. It was the row_number() usage that was key.

Also, I didn't see your post before I posted, or I would not have done it, as they were similar (except for the variable order by).

Converting oxygen into carbon dioxide, since 1955.

Jeff K SSC-Addicted Points: 417 More actions · Answer 11

Well, I finally got everything to work. As I said before, I completely misunderstood the ROW_NUMBER function. Once I had that done, I was able to get the whole piece working. Not the prettiest code I have ever written, but functional.

Thanks!

LutzM SSC Guru Points: 107049 More actions · Answer 12

Glad it worked out for you 😀

Once you figured how ROW_NUBER and its sibling (RANK and DENSE_RANK) work, all come in really handy. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Jeff Kring (3/3/2010)
I did look into ROW_NUMBER as an option. I could use it to return the correct rows, but the application needs the actual value as well.

Not sure if this is exactly what you mean, but it's quite easy to make a new table with a new identity column whilst copying an old identity column. Read the comments in the code, please...

--===== Original table with an IDENTITY column

SELECT TOP (1000)

IDENTITY(INT,1,1) AS SomeIdentity,

NEWID() AS SomeValue

INTO #ContainsIdentity

FROM Master.sys.All_Columns

--===== New table with new IDENTITY column and old

-- IDENTITY column stripped of IDENTITY property by ISNULL

SELECT IDENTITY(INT,1,1) AS SomeNewIdentity,

ISNULL(SomeIdentity,0) AS StrippedOfIdentity

INTO #NewTable

FROM #ContainsIdentity

--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)

Umer Akram SSC-Insane Points: 22209 More actions · Answer 14

Jeff, Actually OP is using (*) when in the select statement something like this

Select IDENTITY(INT,1,1) AS RowID, *

into SomeTable

From Table

When we use this as the rule of thumb, identity column will be inherit, So only one identity column per table rule violates.

I would suggested OP to use Actual Column Name List and could skipped the identity column, but as he need the 'Actual values', i think ROW_NUMBER() Function will do.