how to create common procedure for ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to create common procedure for ...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-09-07 : 11:46:46

Hello,

I have more than 20 tables,

for this i want to create a common Stored procedure which fetches data by id Column

So I tried like this,but it doesnt work

ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID]
-- Add the parameters for the stored procedure here
@TableName nvarchar(100),
@ColumnName nvarchar(100),
@ColumnVal nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @ExecQuery nvarchar(100)
SET @ExecQuery =@TableName
-- Insert statements for procedure here
select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal
exec (@ExecQuery)
END

please help me out

Regards,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-07 : 12:11:02

Doesn't work isn't helpful for us! Any error messages?

A left bracket has to be masked like this: [[]

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-07 : 16:56:43

>> to create a common Stored procedure which fetches data by id Column <<

Your approach is wrong. Each table is a separate and totally diffrent set of entities if your DB is properly designed. There is no such thing as a magical, universal "id" in RDBMS; each set has its own identifiers.

Your magic procedure works on Squids, automobiles and anything else in creation.

So you will have to use Dynamic SQL to do this, or look at the metadata tools that come with SQL Server. This is poor programming; you ought to know what you want to see before you query a table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources