select znd execute a "select" statement

[eramgarden]

Our vendor app saves sql statements in a table, field TEXT. Along with ID for each row...

 

I want to select that "select" statement , in a stored proc, and execute it...

 

For example, the "select" is like this:

select c_firstname as 'First Name', c_lastname as 'Last Name' .....

 

the select can be very long, maybe more than 8000 chars ...

 

 

----

 

Then I found an example in BOL and i tired this:

execute sp_executesql

N'select sql_statement from ad_query where query_id = @level',

N'@level int',

@level = 1679

 

But that gets me the select statment, BUT how can I run that select??

 

any ideas?

[Robby]

Here's a snippet of code that works....

 

BEGIN TRANSACTION

 

DECLARE @Sql VARCHAR(8000)

DECLARE @Crlf NCHAR(2)

DECLARE @Tab NCHAR(1)

SET @Crlf = CHAR(13) + CHAR(10)

SET @Tab = CHAR(9)

 

 

SET @Sql = 'BEGIN TRANSACTION' + @Crlf +

'INSERT INTO' + @Crlf +

'AIM_SSPLVS_Dosage_Factor_Cost' + @Crlf +

@Tab + '(Producer_Type,' + @Crlf +

@Tab + 'Producer_Type_Id,' + @Crlf +

@Tab + 'Level2_Name,' + @Crlf +

@Tab + 'Level2_Id,' + @Crlf +

@Tab + 'Level3_Name,' + @Crlf +

@Tab + 'Level3_Id,' + @Crlf +

@Tab + 'Cost_Per_Dose,' + @Crlf +

@Tab + 'Cost_Per_Year,' + @Crlf +

@Tab + 'Doses_Per_Year)' + @Crlf +

'VALUES (' +

'''' + @Producer_Type + ''',' + @Crlf +

@Tab + convert(nvarchar(3),@Producer_Type_Id) + ',' + @Crlf +

@Tab + '''' + @Level2_Name + ''',' + @Crlf +

@Tab + convert(nvarchar(3),@Level2_Id) + ',' + @Crlf +

@Tab + '''' + @Level3_Name + ''',' + @Crlf +

@Tab + convert(nvarchar(3),@Level3_Id) + ',' + @Crlf +

@Tab + convert(nvarchar(3),0) + ',' + @Crlf +

@Tab + convert(nvarchar(3),0) + ',' + @Crlf +

@Tab + convert(nvarchar(3),0) + ')' + @Crlf +

'COMMIT TRANSACTION'

 

PRINT @SQL + @Crlf + @Crlf

--EXEC (@SQL) --uncomment this line to execute the above code

COMMIT TRANSACTION

[Cassio]

Declare @select_text Varchar(8000)
Select @select_text = Myfield From Mytable
Exec(@select_text)

[Cassio]

Sorry Robby, didn´t see your post.

[Robby]

At least yours got to the point quicker then mine. :)

[eramgarden]

what if the text is greater than 8000? then it's gonna be cut ...

 

wonder if this would work without cutting the sql:

 

declare @sql1 as varchar(8000)

declare @sql2 as varchar(8000)

select @sql1 = CAST(sql_statement AS VARCHAR(8000)),

@sql2 = CAST(SUBSTRING(sql_statement, 8001, 8000) AS VARCHAR(8000))

from ad_hoc_query

where query_id = 1679

print @sql1 + @sql2

execute(@sql1 + @sql2)

[Robby]

Yes it would work