select stored proc

2 Sep 2005 9:59 AM

soc

Does it make sense to have a stored proc which selects from one of a number
of tables depending on a variable, as below.

Or should I have a stored proc for each table.
Thanks Soc.

CREATE PROCEDURE [user].[usp_tables]
(
@pg_id int,
@pagesource varchar (30)
)
AS
Select * from @pagesource where pg_id=@pg_id
GO

2 Sep 2005 10:10 AM

Tibor Karaszi

One for each. See:

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

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"soc" <z***@yahoo.com> wrote in message news:%23i7zKU6rFHA.2596@TK2MSFTNGP09.phx.gbl...
> Does it make sense to have a stored proc which selects from one of a number
> of tables depending on a variable, as below.
>
> Or should I have a stored proc for each table.
> Thanks Soc.
>
>
> CREATE PROCEDURE [user].[usp_tables]
> (
> @pg_id int,
> @pagesource varchar (30)
> )
> AS
> Select * from @pagesource where pg_id=@pg_id
> GO
>
>

2 Sep 2005 10:11 AM

John Bell

Hi

It is poor practice to use select * from in production code as you are not
producing optimum code and you may get different results if the table has
been altered in some way.

You may want to look at:
http://www.sommarskog.se/dynamic_sql.html

John

Show quote

"soc" wrote:

> Does it make sense to have a stored proc which selects from one of a number
> of tables depending on a variable, as below.
>
> Or should I have a stored proc for each table.
> Thanks Soc.
>
>
> CREATE PROCEDURE [user].[usp_tables]
> (
> @pg_id int,
> @pagesource varchar (30)
> )
> AS
> Select * from @pagesource where pg_id=@pg_id
> GO
>
>
>

2 Sep 2005 10:28 AM

David Portas

No, it's a waste of time (and in fact your code won't work anyway).

Create separate stored procs for each table. Also, avoid SELECT *.

--
David Portas
SQL Server MVP
--

2 Sep 2005 10:40 AM

R.D

This works but can be used only at the cost of performance. SO DONT USE

CREATE PROCEDURE [user].[usp_tables]
(
@pg_id int,
@pagesource varchar (30)
)
AS
EXEC (' Select * from ' + @pagesource + ' where pg_id ' = + @pg_id)
GO

Show quote

"soc" wrote:

> Does it make sense to have a stored proc which selects from one of a number
> of tables depending on a variable, as below.
>
> Or should I have a stored proc for each table.
> Thanks Soc.
>
>
> CREATE PROCEDURE [user].[usp_tables]
> (
> @pg_id int,
> @pagesource varchar (30)
> )
> AS
> Select * from @pagesource where pg_id=@pg_id
> GO
>
>
>

5 Sep 2005 8:53 AM

soc

Thanks all....
Soc.
Show quote

"R.D" <R*@discussions.microsoft.com> wrote in message
news:653E073E-923A-44FA-A154-9CA06AA6AD2B@microsoft.com...
> This works but can be used only at the cost of performance. SO DONT USE
>
> CREATE PROCEDURE [user].[usp_tables]
> (
> @pg_id int,
> @pagesource varchar (30)
> )
> AS
> EXEC (' Select * from ' + @pagesource + ' where pg_id ' = + @pg_id)
> GO
>
>
>
>
>
> "soc" wrote:
>
>> Does it make sense to have a stored proc which selects from one of a
>> number
>> of tables depending on a variable, as below.
>>
>> Or should I have a stored proc for each table.
>> Thanks Soc.
>>
>>
>> CREATE PROCEDURE [user].[usp_tables]
>> (
>> @pg_id int,
>> @pagesource varchar (30)
>> )
>> AS
>> Select * from @pagesource where pg_id=@pg_id
>> GO
>>
>>
>>