SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Dynamic SQL problem		  Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/14/2006 :  06:37:34  Show Profile
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

India
22461 Posts
Posted - 06/14/2006 :  06:45:42  Show Profile  Send madhivanan a Yahoo! Message
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/14/2006 :  06:58:32  Show Profile  Visit SwePeso's Homepage
Sometimes dynamic SQL is the only way to go.
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 06/14/2006 07:38:52
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts
Posted - 06/14/2006 :  07:35:41  Show Profile  Visit mr_mist's Homepage
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. :)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/14/2006 :  07:47:04  Show Profile  Visit SwePeso's Homepage 
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)
Call this stored procedure with
EXEC uspGetIslandLimits 't20_type', 'id'


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 06/14/2006 07:50:24
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/14/2006 :  07:48:25  Show Profile  Visit SwePeso's Homepage 
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)
Call this stored procedure with
EXEC uspGetGapLimits 't20_type', 'id'


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 06/14/2006 07:50:12
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/14/2006 :  08:13:53  Show Profile
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
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/14/2006 :  08:16:25  Show Profile
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
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts
Posted - 06/14/2006 :  08:59:22  Show Profile  Visit mr_mist's Homepage
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. :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/14/2006 :  09:03:35  Show Profile  Send madhivanan a Yahoo! Message
>>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
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/14/2006 :  09:14:37  Show Profile
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/14/2006 :  09:16:37  Show Profile  Send madhivanan a Yahoo! Message
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
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/14/2006 :  09:37:52  Show Profile
Every Batch contains around 20000 records

perday we process 3 or 4 batches nearly 75000 records


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

Live while u r Alive
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/14/2006 :  09:48:01  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 06/14/2006 :  13:00:48  Show Profile  Visit jsmith8858's Homepage
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/14/2006 :  13:10:29  Show Profile  Visit SwePeso's Homepage
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
Edited by - SwePeso on 06/14/2006 13:13:48
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts
Posted - 06/15/2006 :  04:19:20  Show Profile  Visit mr_mist's Homepage
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. :)
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/15/2006 :  08:42:21  Show Profile



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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/15/2006 :  09:40:21  Show Profile  Send madhivanan a Yahoo! Message
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
Edited by - madhivanan on 06/15/2006 09:40:55
Go to Top of Page

raam_kimi
Yak Posting Veteran

India
80 Posts
Posted - 06/15/2006 :  10:29:24  Show Profile
Thasnks Madhi.. I solved it...

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

Live while u r Alive
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/16/2006 :  02:22:49  Show Profile  Send madhivanan a Yahoo! Message
But if possible avoid creating tables daily

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000