Substituting 'Database.Table Name' with the Parameter Passed 1

[ariftvt]

I'm trying to do the following:-

****
'Create Procedure Test @table_name varchar
as
select * from table_name'
****

but it gives me an Error.

How to substitute Table_name with the string Paramater Passed?.

 

[markros]

This can be done only through dynamic SQL. In other words, you need to consrtuct your query as a string and then execute with execute statement. Are you sure you need to do that?

 

[hmckillop]

As Markros says, you can do this through dynamic SQL but there are so many reasons why this is a bad idea,

here are some for starters:
- if you plan to use this in an application, e.g. it calls the stored proc and passes the table name parameter you will then need to implement logic in your app to handle all the different combinations of columns per table (also any changes in DB will break the code and need reflected in Code)
- It makes a mockery of the purpose of the stored procedure, e.g. a pre-compiled, more secure, speedier method of accessing data


If I had ten tables and all you want to do is access the information in each table, i would create 10 stored procs and make sure you name each field and table in each proc
e.g. SELECT Field1, Field2 from TableNameA

If you can explain why you wish to do this, then maybe someone could make a better suggestion, and if you really wish to persist then sample code can be posted.

I am off my soapbox now, sorry!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

If you are at all considering doing something as foolish as this, then you must read this article first (which will help you understand why it is a bad thing). You should never in my opinion have a proc that general, it is not secure and it cannot be properly tested and it can have negative performance impact. Also you should never return any column you don't need at that particular time to the user intereface, this is just wasteful of both server and network resources. Ths approach makes performance tuning impossible as well. Generalizing a relational database is a poor practice!

http://www.sommarskog.se/dynamic_sql.html

"NOTHING is more important in a database than integrity." ESquared

 

[ariftvt]

Thanks a lot markros...
done..
i badly wanted to do that..coz i need to access the back end sql DB of a thirty party software which carry forwards it's datas to a new DB every year.
i ve put the DB name in a text file instead of changing the VFP code when the DB name changes evry year.

thanks..

****VFP Code *******
db_name=''
db_name=find_config('focusdb=')

stt=0
stt=SQLEXEC(hr_handle,"exec get_foc_bal &db_name",'foc_bal')

******************
Function find_config
Parameters find_what
row_no=Ascan(config_string,find_what)
If row_no>0
return_str=Right ( config_string(row_no),Len(config_string(row_no))-At('=',config_string(row_no)))
Endif
If Len(return_str)>1
Return return_str
Else
Return ''
Endif
Endfunc

*****SQL Proc******************

CREATE PROCEDURE get_foc_bal @table_name varchar(50)
as declare @cmd_exec varchar(1000)
select @cmd_exec="SELECT isnull(SUM(a.drbal),0) AS drbal,"+"isnull(SUM(a.crbal),0) AS crbal,"+@table_name+".dbo.mr000.Name, "+
@table_name+".dbo.mr000.L2 AS grp,"+ @table_name+".dbo.mr000.MasterId FROM (SELECT SUM(CASE WHEN Amount1 < 0.0 THEN Amount1 ELSE 0.0 END) drbal,
SUM(CASE WHEN Amount1 > 0.0 THEN Amount1 ELSE 0.0 END) crbal, Code acc FROM "+@table_name+".dbo.data WHERE (Flags & 0x400) = 0x400
GROUP BY Code
UNION
SELECT SUM(CASE WHEN Amount2 < 0.0 THEN Amount2 ELSE 0.0 END) drbal, SUM(CASE WHEN Amount2 > 0.0 THEN Amount2 ELSE 0.0 END)
crbal, BookNo acc FROM "+@table_name+".dbo.data
WHERE (Flags & 0x400) = 0x400 GROUP BY BookNo) a RIGHT OUTER JOIN
"+@table_name+".dbo.mr000 ON a.acc = "+@table_name+".dbo.mr000.MasterId
GROUP BY a.acc,"+ @table_name+".dbo.mr000.Name, "+@table_name+".dbo.mr000.L2,"+ @table_name+".dbo.mr000.MasterId ORDER BY "+@table_name+".dbo.mr000.Name"
exec(@cmd_Exec)
GO
***********************************