Using a Variable to Determine the Table to SELECT From

1
votes

this is new error I get:

IF object_id('Osvezi') IS NULL

EXEC sp_executesql N' 
CREATE PROCEDURE Osvezi
    -- Add the parameters for the stored procedure here
    @tablica nvarchar(200)
AS
BEGIN
   IF @tablica = 'Uporabniki' THEN SELECT * FROM Uporabniki;

   IF @tablica = 'Dokumenti' THEN SELECT * from Dokumenti;
END'
GO
2
sqlsql-serversql-server-2008procedure
You can't switch data sources that way. - John Saunders
Yea I tried without declaring new variable but I get same error - user1116851
You should give up on this entire idea. SELECT * by itself is a bad idea. Combining it with dynamically choosing the table is a worse idea. What are you trying to accomplish? - John Saunders
What I am trying to do is next. I am writing application in c# that call's certain procedure in Sql server database. As parameter this procedure is receiving variable which has name of table in it. And with that parameter I want to refresh only certain table. Because I have like 10 procedures and I don't want to refresh all table's I want only ones that is changed depending on sended parameter... I hope you understand. Thank's in advance - user1116851
It's too late at night for me to get the syntax right, but use IF @tableName = 'Table1' THEN SELECT col1, col2 FROM Table1; IF @tableName = 'Table2' THEN SELECT col1, col2, col3 from Tabel2;` - John Saunders

2 Answers

-1
votes

You need to use double '' in the string to get one ' and you need to remove then.

IF object_id('Osvezi') IS NULL
EXEC sp_executesql N' 
CREATE PROCEDURE Osvezi
    -- Add the parameters for the stored procedure here
    @tablica nvarchar(200)
AS
BEGIN
   IF @tablica = ''Uporabniki'' SELECT * FROM Uporabniki;

   IF @tablica = ''Dokumenti'' SELECT * from Dokumenti;
END'
GO
0
votes

You can do it like this but it is NOT RECOMMENDED

CREATE PROCEDURE Osvezi
    -- Add the parameters for the stored procedure here
    @tablica nvarchar(200)
AS
BEGIN
    EXECUTE('SELECT * FROM' + @tablica);
END

Also it would depend on which DBMS you are using; SQL SERVER? MYSQL? POSTGRESQL?

Edit:

If you need this kind of flexibility, try the Entity-Attribute-Value Model