SELECT FROM parameter as viewName?

[Techno]

in SQL when using say a SPROC (Stored Procedure), is there a way to use a parameter for the "FROM" part of the statement?

for example:


SELECT @fieldName FROM @viewOrTableName


?

if so how? as currently the syntax error is that @viewOrTableName must be declared, but they are as parameters....in addition what about the fieldname parameter?

[si_the_geek]

Do you mean SQL Server?

If so, you can't do that unfortunately.. you need to create a string in the SP, and put the SQL statement into it - then you can run it using (I think) Exec.

[kaffenils]

If there is a limited number of view names you could use IF...ELSE to check the value in the variable, and write separate SELECT statements for each value.

On the other hand, if the name of the views are unknown, the only solution I can think of is building dynamic SQL. Keep in mind that you should check for SQL injections when using the dynamic sql option. E.g. use the QUOTENAME() function and check that the variables doesn't contain -- or /*.
Add the SELECT statement to a variable and execute it with the sp_executesql stored procedure.

declare @sql nvarchar(4000)

set @sql=N'SELECT '+QUOTENAME(@fieldName)+N' FROM '+QUOTENAME(@viewOrTableName)

exec sp_executesql @sql

[Techno]

yes I mean SQL Server, sorry shouldve cleared that up initially

thanks