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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
22864 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
30421 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
30421 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
30421 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
22864 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
22864 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
30421 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
30421 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
22864 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
22864 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.16 seconds. Powered By: Snitz Forums 2000