DECLARE @sql varchar(1000)
set @sql='SELECT * FROM '+@aTableName
EXEC (@sql)

quote:

---

Originally posted by mynkow



"but question is why do you want to pass the table name dynamically?"
I have to create big website. All data from the DB will be retrieved by stored procedures. I want to create one stored procedure that gets all fields from a table and that table is actually the param. Doing this I will decrease the number of the stored procedures. And here comes my next question.

---

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by mynkow



"but question is why do you want to pass the table name dynamically?"
I have to create big website. All data from the DB will be retrieved by stored procedures. I want to create one stored procedure that gets all fields from a table and that table is actually the param. Doing this I will decrease the number of the stored procedures. And here comes my next question.

---

This is very bad programming in SQL Server. It will suffer from weak security and bad performance.

We have thousands of stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Originally posted by mynkow

"It is pointless to use stored procedures if you design a system like you are envisioning. You might as well use inline SQL (with parameterized queries, of course). There is no benefit to stored procedures whatsoever with your approach."
You are wrong here :) The database server will be separate machine. One year ago I created a maintenance program and all SQL statements were within the program. When we start it the CPU goes 90-100%. (the program is running on Server1 and the DB is on Server2). Then we transform all SQL statements in the program to Stored Procedures. The CPU now is 20-30%.

I do not understand how source control can organize my Stored Procedures?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by mynkow

"It is pointless to use stored procedures if you design a system like you are envisioning. You might as well use inline SQL (with parameterized queries, of course). There is no benefit to stored procedures whatsoever with your approach."
You are wrong here :) The database server will be separate machine. One year ago I created a maintenance program and all SQL statements were within the program. When we start it the CPU goes 90-100%. (the program is running on Server1 and the DB is on Server2). Then we transform all SQL statements in the program to Stored Procedures. The CPU now is 20-30%.

I do not understand how source control can organize my Stored Procedures?

---

By using a source control mechanism you can
1.create a folder structure based on how you want to organise the stored procedures and add the procedure scripts to it
2.you can adopt a policy wherein stored procedures created should be only added to appropriate folder in source control (like alphabetically,as per db where its created...)
3. you can also control access to appropriate folders in source control to required people alone to prevent some others from adding or modifying scripts in it.

---

quote:

---

Originally posted by mynkow

[quote]Originally posted by visakh16
I know what the source control can do. But the SC only keeps versions of my code. When I want to change one stored procedure or I want simply to find it I will search in MS SQL Server, not in source control. Source Control is something like backup system not managing system.

---