I have a table called SearchStrings that stores SELECT statements as nvarchar(250). The SearchStrings table has an ID field and a SearchString field.

What is the syntax to create a View/SP/Function to execute the last SearchString in the above table?

I wrote a Function that gives me the last string but I still can't figure out how to execute that string.

I have not tried this but this will give you an idea

CREATE PROCEDURE ProcGetSearchString
AS
BEGIN

DECLARE @SQL nvarchar(250)
SELECT TOP 1 @SQL = SearchString
FROM TableName
Order By ID DESC

EXEC sp_executesql @SQL

END

CREATE PROCEDURE ProcExecSearchString
AS
BEGIN

DECLARE @strSQL nvarchar(250)

SELECT TOP 1 SearchString AS [@strSQL]
FROM dbo.SearchStrings
ORDER BY ID DESC

EXEC sp_executesql @strSQL

END

The SELECT statement is all that is being returned. The EXEC statement doesn't seem to do anything. Any ideas?? I had to substitute the AS instead of the "=" assignment because of an error that was generated.

can you confirm that you have all the rights on your database tables?
******
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
********

You were right on target with that. As soon as I assigned myself to the db_owner role, the code worked. I had upsized this db from Access and I assumed I was owner, but I was only assigned to the Public role. I wish the error I had received would let me know it is a permission issue.

-- Thanks -