how to exec a query stored in table field and the field is TEXT

[nikstech007]

hi,
I want to use a query which is stored in the temp table in the text field
the table defination is

Create table #print  (ID varchar(100),prttext text)

--use text because the query is to create procedure which is having more than 10000 character

in ptrtext the query the query is stored. and
i what to exec that query
i try lot with EXEC and sql_exectuesql
but its gives error every time

** SNIP **
thanks

[yarbrough40]

Try this...

if you want to do it all within the stored procedure do something like:

DECLARE @SQL VARCHAR(200)
SET @SQL = (SELECT prttext FROM #print)

EXEC(@SQL);

if you want to handle it with front-end app code:
1) open a connnection to your sqlserver and fire your select statement"
2) read through the results (using a datareader) into a string.
3) fire the string as a sql statement using the same open connection

[ck9663]

EXEC takes string variable, not TEXT. Try using a MAX size the entire CREATE PROC string might fit.

Also, you might want to consider redesigning your process. Creating the proc every time means the statistics on the stored proc might not be updated as you would want it. So it will run as if it's the first time, making it slower compared with an SP that the server have stored stats for.

Good Luck!!!


~~ CK