Dynamic SQL - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL

Author

Topic

Vj
Starting Member

8 Posts

Posted - 2005-10-27 : 08:34:09

Hi,

I am trying to execute a Dynamic SQL which is a huge string. Approx.. 80000 Characters.
This string is generated dynamically/programatically.(Concatenation of multiple sql statments).
The number of sql statements is dynamic. So I cant declare local variable also. "Exec" expects
a varchar as sql string.So I am not able use ntext also. Any workaround for this limitation ?????

Please help..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-27 : 08:54:49

exec (@str1 + @str2....)

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-27 : 08:55:03

you can try somthing like this ..

Declare @Sql1 Varchar(8000),@sql2 Varchar(8000).. .

-- your dynamic sql..

Exec(@Sql1 + @Sql2 +.. )

Hope this helps.

Complicated things can be done by simple thinking

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-27 : 08:56:52

Try using sp_executesql. This accepts a ntext data only. Please check BOL for more information.

Regards
Sachin Samuel

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-27 : 08:58:28

by the way... a dynamic string that large is almost guaranteed not to be the ideal solution. surely there is a better way to accomplish your task

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 05:31:22

Refer this also
http://sqlteam.com/forums/topic.asp?TOPIC_ID=52274

Madhivanan

Failing to plan is Planning to fail

Vj
Starting Member

8 Posts

Posted - 2005-10-28 : 05:46:38

quote:
Originally posted by sachinsamuel

Try using sp_executesql. This accepts a ntext data only. Please check BOL for more information.

Regards
Sachin Samuel

Thanks for your reply. I cannot use mutiple variables as I am not sure of how many is needed.
Nearly thousands of queries are generated on the fly which I need to concatenate. This (huge)
concatenated string needs to be executed. I tried creating a #temp table with ntext column.
I kept on updating the ntext column. But sp_executesql expects a statement. If i try to retrive the
column and pass it to sp_execute ("EXEC sp_executesql N'select qry_string from temp_sep'") it returns
the ntext. Rather I need the query in that next to be executed. I also tried READTEXT
BEGIN
DECLARE @ptrvalue binary(16)
DECLARE @VAL INTEGER
select @ptrvalue = textptr(qry_string) from temp_sep
set @VAL = (select DATALENGTH(qry_string) from temp_sep)/2
EXEC sp_executesql READTEXT TEMP_SEP.qry_string @ptrvalue 0 @VAL
END
But this also didnt help me.
Any clues...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-28 : 08:12:08

I still propose:

A) you are going about this the wrong way

B) you can use variables... as long as you can define an upper bound....

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-28 : 08:14:43

also... review this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420

look at kristen's approach to handle long varchar sql strings...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 08:35:02

Blimey, I've managed to reinvent that code in both the links posted. More senile than I thought

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-28 : 08:43:49

at least your consistent

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources