Dynamic SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Dynamic SQL problem

Author

Topic

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-14 : 06:37:34

I have some doubts using Dynamic SQL.....

how to write a stored procedure that takes a table name as its input...?
but many articles states that Dymaic sql is a bad way to solve this issue.. But i am using this approach in my SP.

Is there any one know any aletrnatives available for this one...

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 06:45:42

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 06:58:32

Sometimes dynamic SQL is the only way to go.
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-14 : 07:35:41

The reason why generally people say that dynamic SQL is a bad thing, is because it is generally a symptom of either poor design (of the database) or trying to do something that, in itself, is a bad idea.

Why, for example, do you need to pass in the table name as an input, what are you trying to do?

-------
Moo. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 07:47:04

[code]CREATE PROCEDURE uspGetIslandLimits
(
@TableName VARCHAR(100),
@FieldName VARCHAR(100)
)
AS

SET NOCOUNT ON

SELECT @TableName = QUOTENAME(@TableName),
@FieldName = QUOTENAME(@FieldName)

DECLARE @SQL VARCHAR(2000)

SELECT @SQL = 'SELECT LowLimit.' + @FieldName + ' FromID, (SELECT TOP 1 ' + @FieldName + ' FROM ' + @TableName + ' HighLimit WHERE NOT EXISTS (SELECT B.' + @FieldName + ' FROM ' + @TableName + ' B WHERE HighLimit.' + @FieldName + ' + 1 = B.id) AND HighLimit.' + @FieldName + ' > LowLimit.' + @FieldName + ' ORDER BY ' + @FieldName +') ToID FROM ' + @TableName + ' LowLimit WHERE NOT EXISTS (SELECT B.' + @FieldName + ' FROM ' + @TableName + ' B WHERE LowLimit.' + @FieldName + ' - 1 = B.' + @FieldName + ') ORDER BY LowLimit.' + @FieldName

EXEC (@SQL)[/code]Call this stored procedure with[code]EXEC uspGetIslandLimits 't20_type', 'id'[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 07:48:25

[code]CREATE PROCEDURE uspGetGapLimits
(
@TableName VARCHAR(100),
@FieldName VARCHAR(100)
)
AS

SET NOCOUNT ON

SELECT @TableName = QUOTENAME(@TableName),
@FieldName = QUOTENAME(@FieldName)

DECLARE @SQL VARCHAR(2000)

SELECT @SQL = 'SELECT LowLimit.' + @FieldName + ' + 1 FromID, (SELECT TOP 1 ' + @FieldName + ' FROM ' + @TableName + ' HighLimit WHERE NOT EXISTS (SELECT B.' + @FieldName + ' FROM ' + @TableName + ' B WHERE HighLimit.' + @FieldName + ' - 1 = B.id)'
SELECT @SQL = @SQL + 'AND HighLimit.' + @FieldName + ' > LowLimit.' + @FieldName + ' ORDER BY ' + @FieldName +') - 1 ToID FROM ' + @TableName + ' LowLimit WHERE NOT EXISTS (SELECT B.' + @FieldName + ' FROM ' + @TableName + ' B WHERE LowLimit.' + @FieldName + ' + 1 = B.' + @FieldName + ') AND LowLimit.' + @FieldName + ' < (SELECT MAX(' + @FieldName + ') FROM t20_type) ORDER BY LowLimit.' + @FieldName

EXEC (@SQL)[/code]Call this stored procedure with[code]EXEC uspGetGapLimits 't20_type', 'id'[/code]

Peter Larsson
Helsingborg, Sweden

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-14 : 08:13:53

Thanks pals
everyone asking me this question like mist... why r u passing table name as input...
In my application one table is created per day with the particular date.. and i gonna maintain t he large volume of data everyday and i need to Export these datas in a day manner to the Different DB....

and the table name is like this Tran_061406_Master and Batch_061406_master...

The Frond end application only be able to pass the date to manipualte the data..
so my SP only get the processdate and form the tablename inside.. so i think, in this situation D-SQL is the only solution for data manipulation...

=====================
Ram

Live while u r Alive

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-14 : 08:16:25

Thanks Madhi
I have already gone through that link... But i dont think he is provide any replacement for this approach.......

=====================
Ram

Live while u r Alive

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-14 : 08:59:22

See.. Poor app design -> poor database structure --> dynamic sql.

Your app shouldn't be creating a table every day, that'll be a royal PITA to start with. Imagine in five years when you have 1825 tables what a ball ache writing a query to extract the data will be.

-------
Moo. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 09:03:35

>>In my application one table is created per day with the particular date

Why?
Istead, store the data in the same table provided you use DateTime column to track day-to-day data

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-14 : 09:14:37

no yaar.. thats impossible
it ll hold thousnands of record per day...so that i need to have a seperateTable per day..

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 09:16:37

quote:
Originally posted by raam_kimi

no yaar.. thats impossible
it ll hold thousnands of record per day...so that i need to have a seperateTable per day..

=====================
Ram

Live while u r Alive

How many data do you have per day?

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-14 : 09:37:52

Every Batch contains around 20000 records

perday we process 3 or 4 batches nearly 75000 records

=====================
Ram

Live while u r Alive

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 09:48:01

quote:
Originally posted by raam_kimi

Every Batch contains around 20000 records

perday we process 3 or 4 batches nearly 75000 records

=====================
Ram

Live while u r Alive

It seems to me that madhivanan has the solution. In a year, this only gives around 27.5 million records. Piece of cake.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-14 : 13:00:48

I want to also throw in my 2 cents that this is a horrible design. Every sql statement that you execute will need to be dynamically constructed this way. Put your data in 1 table.

Will you ever need to query data for a range of days, say for a month? Think about that SQL statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 13:10:29

quote:
Originally posted by jsmith8858

I want to also throw in my 2 cents that this is a horrible design. Every sql statement that you execute will need to be dynamically constructed this way. Put your data in 1 table.

Will you ever need to query data for a range of days, say for a month? Think about that SQL statement.

Not if he also is managing a VIEW where all the tables are UNIONed together, with one column hardwired as the date for the table

Peter Larsson
Helsingborg, Sweden

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-15 : 04:19:20

quote:
Originally posted by Peso

Not if he also is managing a VIEW where all the tables are UNIONed together, with one column hardwired as the date for the table

(That was probably tongue-in-cheek but..)

In which case every day he would have to manually change the view.

It's a poor solution, end of. Which goes back to my first post in this thread, where I said that dynamic SQL often comes about because of a poor design.

-------
Moo. :)

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-15 : 08:42:21

Hi all
I have a problem with this D-Sql script. when i try to run this script it throws an error like this
Incorrect syntax near the keyword 'exec'.

SET @stmt= ' UPDATE
Temp_'+@processDate+
' SET
temp=b.Status
FROM
Temp_'+@processDate+' a,
ExMaster b
WHERE '+
a.col1 +' ='+ltrim(rtrim(substring(b.TempStr,7,10))) + ' and '+
a.col2 +' ='+ltrim(rtrim(substring(b.TempStr,21,5) + ' and '+
a.col3 +' ='+ltrim(rtrim(substring(b.TempStr,26,5) + ' and '+
b.processDate +' ='+@processDate + ' and
userflag4<>''N'''

exec(@stmt)

i dont know whats the problem hers..... anyone help me out to fix this pls yaar.........

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-15 : 09:40:21

Try this


SET @stmt= ' UPDATE Temp_'+@processDate+' SET temp=b.Status FROM Temp_'+@processDate+' a,
	ExMaster b 
WHERE 
	a.col1 =ltrim(rtrim(substring(b.TempStr,7,10)))  and 
	a.col2 = ltrim(rtrim(substring(b.TempStr,21,5)  and 
	a.col3 =ltrim(rtrim(substring(b.TempStr,26,5) and 
	b.processDate  ='''+@processDate + ''' and 
userflag4<>''N'''
EXEC(@stmt)

But consider redesigning the structure

Madhivanan

Failing to plan is Planning to fail

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-06-15 : 10:29:24

Thasnks Madhi.. I solved it...

=====================
Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 02:22:49

But if possible avoid creating tables daily

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources