[SQL Server] parameterized FROM clause in Stored Procedure

Thread View: comp.databases.ms-sqlserver

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Pol Sat, 17 May 1997 00:00

#23

Author: Pol
Date: Sat, 17 May 1997 00:00

29 lines
1367 bytes

THE SITUATION
-------------
I have a small-database that I'm upsizing to SQL Server.
One of its stored procedures takes a parameter to tell it what file to
SELECT from.

THE PROBLEM
-----------
I can't define a stored procedure in SQL Server that takes a parameter
in the FROM clause.  For example:

CREATE PROCEDURE sp_GetRecord (@pcTableName varchar(20), @pnID
Numeric(15))
WITH RECOMPILE
AS
SELECT * FROM @pcTableName 
WHERE VM_ID = @pnID

THE QUESTION
------------
Is there any way to include a Stored Procedure parameter in the FROM
clause of a SELECT statement in the Stored Procedure?

Thanx.

If you could also reply by Email, that'd be great.

pol@ix.netcom.com

Re: [SQL Server] parameterized FROM clause in Stored Procedure

#24

Author: Bob Pfeiff
Date: Sat, 17 May 1997 00:00

21 lines
1090 bytes

You need to build the sql statement in the procedure then execute it:

create proc param
	@table varchar(30),
	@where varchar(50)
as

declare @sql varchar(50)

select @sql = 'select * from ' + @table + ' ' + @where

exec (@sql)

You can then run the SP like this:

param authors, 'where au_lname = "green"'

Bob Pfeiff
Spectrum Technology Group, Inc.
bobp@spectrumtech.com

Re: [SQL Server] parameterized FROM clause in Stored Procedure

#25

Author: finexprt
Date: Sun, 18 May 1997 00:00

38 lines
1635 bytes

 

On Sat, 17 May 1997, Pol wrote:

> THE SITUATION
> -------------
> I have a small-database that I'm upsizing to SQL Server.
> One of its stored procedures takes a parameter to tell it what file to
> SELECT from.
> 
> THE PROBLEM
> -----------
> I can't define a stored procedure in SQL Server that takes a parameter
> in the FROM clause.  For example:
> 
> CREATE PROCEDURE sp_GetRecord (@pcTableName varchar(20), @pnID
> Numeric(15))
> WITH RECOMPILE
> AS
 declare @sql varchar(255)

> select @sql="SELECT * FROM "+@pcTableName+ 
> "WHERE VM_ID = "+convert(varchar(30),@pnID)
exec @sql
> 
> THE QUESTION
> ------------
> Is there any way to include a Stored Procedure parameter in the FROM
> clause of a SELECT statement in the Stored Procedure?
> 
> Thanx.
> 
> If you could also reply by Email, that'd be great.
> 
> pol@ix.netcom.com
> 
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads