quote:

---

Originally posted by ed_ward_graham

Actually, they weren't -- I understand that you cannot execute dynamic SQL in a UDF, is this right? I am now looking at OPENQUERY instead but it doesn't seem to be ideal either, so my question is still open!

---

IF OBJECT_ID('CS_ColumnTest') IS NOT NULL DROP PROCEDURE CS_ColumnTest
GO

CREATE PROCEDURE CS_ColumnTest AS BEGIN

DECLARE @sql NVARCHAR(4000)

ALTER TABLE #t ADD [col2] VARCHAR(255)

SET @sql = 'UPDATE #t SET [col2] = LEFT([col1], 1)'

EXEC sp_executeSql @sql

END
GO

IF OBJECT_ID('tempDb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t (
	[col1] VARCHAR(50)
	)

INSERT #t
      SELECT 'A'
UNION SELECT 'Really'
UNION SELECT 'Bad'
UNION SELECT 'Idea'

SELECT * FROM #t

EXEC dbo.CS_ColumnTest

SELECT * FROM #t
GO

IF OBJECT_ID('CS_ColumnTest') IS NOT NULL DROP PROCEDURE CS_ColumnTest
IF OBJECT_ID('tempDb..#t') IS NOT NULL DROP TABLE #t

quote:

---

Originally posted by ed_ward_graham

It seems that there is no way to do this. Can anyone verify this fact for me?

To recap: I am trying to execute an arbitrary SQL statement and store the results in a table (temporary, permanent, variable -- I don't mind). It doesn't appear to be possible to do this in SQL 2005, which is astonishing.

---

quote:

---

Originally posted by ed_ward_graham

Thanks for the tip, Charlie; I'll give that a try.

Visakh16, in a stored procedure I am selecting a string field from a table which itself contains a SELECT statement. I then need to dynamically execute that statement (hence "arbitrary") and store the results somewhere, for further processing.

All suggestions and alternatives gratefully received.

---

quote:

---

Thanks for the tip, Charlie; I'll give that a try.

Visakh16, in a stored procedure I am selecting a string field from a table which itself contains a SELECT statement. I then need to dynamically execute that statement (hence "arbitrary") and store the results somewhere, for further processing.

All suggestions and alternatives gratefully received.

---

'SELECT * FROM employee WHERE 1=1; DROP TABLE wages'

quote:

---

thats not a good approach. why are you storing sql statements itself inside a table?

---

quote:

---

It's part of a CMS for a database-driven web-site. The administrators can add new pages or new controls to existing pages, the content of which is stored in an XML field in the database. They may add a new table, say, with an accompanying SELECT statement that defines the data returned. For example, in the PageContent XML field of my table, I have things like:

---

quote:

---

OK in the spirit of helpful suggestions: That sounds insane.

Assuming that you do mean completely arbitrary (with dynamic column names etc) then the results would be meaninless to store -- how do you plan to query the results when you can't tell what the column names will be?

---

quote:

---

Also, how are you planning to cope when 2 ore more people execute the code at the same time (dynamic table names as well???)

-- Do you really need completely arbitrary code? How are you planning on making this safe or are you happy that someone will be able to pass you a select statement that says

'SELECT * FROM employee WHERE 1=1; DROP TABLE wages'

You must have some sore of requirement to only query some data? I think you are going to have to give an example of a typical use case that you envisage for this. Otherwise the plan just sounds like a disaster waiting to happen.

---

quote:

---

The administrators (users) can add a new *table* to your schema? or an entry in some sort of lookup table? vastly different.

---

EXAMPLE_TABLE (holds data) (
    [Id] (Identity primary key)
    , [ownerId] (foreign key)
    , [batchId] INT
    , [1] VARCHAR(4000)
    , [2] VARCHAR(4000)
    , [3] VARCHAR(4000)
    , [4] VARCHAR(4000)
    , [5] VARCHAR(4000)
    , [6] VARCHAR(4000)
    )

LOOKUP_EXAMPLE_TABLE (
    , [Id] (identity primary key)
    , [Name] VARCHAR(255)
    , [batchId] (foreign Key) (to Example Table)
    , [col1Alias] VARCHAR(255)
    , [col1DataType]
    , [col2Alias]  VARCHAR(255)
    , [col2DataType] 
    ....
    ....
    ....

quote:

---

Originally posted by Transact Charlie

I think you need to rethink this. Maybe some sort of Lookup table approach? Say something like

    ...

---

EXEC(@ArbitrarySelectStatement)