Parameters used as tablenames in SPs

[D Martin]

Does any one know how to pass in a string to a SP and use the string as a table name in query or as a field name????

[Duncan Maddox]

If the command is fairly simple you can use the following method ...

CREATE PROCEDURE TestVar1 @table VARCHAR(32)
AS
EXEC ("select TOP 10 * FROM " + @table )

The following would select the TOP 10 rows from Table1

EXEC TestVar1 Table1

Duncan

------------
D Martin at 9/6/99 7:47:09 AM

Does any one know how to pass in a string to a SP and use the string as a table name in query or as a field name????

[D.Martin]

Duncans method is a cunning and has sloved most of my problems.. cheers.
However I have had problems getting this method to work when trying to create cursors to use the fetch next statement. Any more suggestion??


In ingris there is a synonym function which allows you to give a table name an alias throughout the code until it is droped. This could be used but SQL does not support the synonym does any one know of any other similar method that could be used.

Finally does any one have an answer to refrencing fields with a parameter?


------------
Duncan Maddox at 9/6/99 8:08:03 AM


If the command is fairly simple you can use the following method ...

CREATE PROCEDURE TestVar1 @table VARCHAR(32)
AS
EXEC ("select TOP 10 * FROM " + @table )

The following would select the TOP 10 rows from Table1

EXEC TestVar1 Table1

Duncan

------------
D Martin at 9/6/99 7:47:09 AM

Does any one know how to pass in a string to a SP and use the string as a table name in query or as a field name????

Hi,

You can use af field-name as a parameter the exact same way as Duncan allready showed you:

CREATE PROCEDURE TestVar1 @table VARCHAR(32), @FieldName VARCHAR(32)
AS
EXEC ("select " + @FieldName + " FROM " + @table )
EXEC TestVar1 Table1

In regard for your request to have a synonym for a table. You could create a View on that table. This has many advantages, among others that you can grant permissions to this view as you want. The syntax for creating a view is:

CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]

fx.

CREATE VIEW AliasTabX AS SELECT * FROM TabX

Please notice, that if you want to perform UPDATE or INSERT on a VIEW the VIEW has to be compiled of only one table. No subqueries or SELECTs from multiple tables are allowed.

Hope this helps you.

Regards,
Allan Schmidt

------------
D.Martin at 9/6/99 11:27:15 AM

Duncans method is a cunning and has sloved most of my problems.. cheers.
However I have had problems getting this method to work when trying to create cursors to use the fetch next statement. Any more suggestion??


In ingris there is a synonym function which allows you to give a table name an alias throughout the code until it is droped. This could be used but SQL does not support the synonym does any one know of any other similar method that could be used.

Finally does any one have an answer to refrencing fields with a parameter?


------------
Duncan Maddox at 9/6/99 8:08:03 AM


If the command is fairly simple you can use the following method ...

CREATE PROCEDURE TestVar1 @table VARCHAR(32)
AS
EXEC ("select TOP 10 * FROM " + @table )

The following would select the TOP 10 rows from Table1

EXEC TestVar1 Table1

Duncan

------------
D Martin at 9/6/99 7:47:09 AM

Does any one know how to pass in a string to a SP and use the string as a table name in query or as a field name????