Question on procedure syntax with using one variable 1

[dharmer1975]

I'm trying to create the following procedure in MS SQL Server 7.0 I get an error "Error 170: Line 3: Incorrect syntax near '@selectview'. Line 3: Incorrect syntax near '@selectedview'. I want @selectedview to be a parameter name (for the name of a view) I want to pass to the procedure using a callable statement in Java. Is it not possible to use a variable name in this way? Here's the procedure:

CREATE PROCEDURE FERPview
@selectedview Varchar (20)
AS ( SELECT distinct((SELECT cast(sum(Number_of_Responses)as float) from harmerd.@selectedview) / (SELECT cast(count(distinct(imc_trackingnumber))as float) FROM harmerd.@selectedview)) as '# of Responses to Resolution'
from harmerd.@selectedview)

Any assistance would be greatly appreciated!

 

[Cagliostro]

You should follow this syntax:

CREATE PROCEDURE FERPview @selectedview Varchar (20)
as

declare @x as varchar(300)
set @x = 'select * from '
set @x = @x + @selectedview
execute( @x ) John Fill
ivfmd@mail.md

 

[dharmer1975]

Thanks for the response--I have another question. Now assuming that I have a view named mar01 (harmerd is it's owner), how do I execute FERPview and pass it the value mar01? I tried this:

execute FERPview mar01

and this

execute FERPview mar01

I want to somehow pass the value mar01 (or anything else) and have the stored procedure execute like this:

"execute( @x )" would become "execute ( select * from harmerd.mar01)"

Thanks for helping!

Dan Harmer

 

[Cagliostro]

Is a copy/paste from SQL Scripts Generate:

if exists (select * from sysobjects where id = object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VIEW1] John Fill
ivfmd@mail.md

 

[dharmer1975]

Forgive me John, but I don't understand your last reply? I don't want to drop a view, what I want the procedure to do is let me pass it the name of a view and return it's contents. I thought that having a procedure to generate a select statement that would return all rows would accomplish this. I was expecting to be able to type something like 'execute FERPview harmer.mar01' and have the @selectedview variable in the FERPview procedure set to harmerd.mar01. Therefore, effectively when typing 'execute FERPview harmer.mar01' the procedure would run and return all rows from the view harmerd.mar01

Are we still on the same page, or did I get lost on your last reply. I appreciate your time and efforts.

-Dan

 

[Cagliostro]

select * from sysobjects where id = object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1 John Fill
ivfmd@mail.md

 

[dharmer1975]

I think I just realized my stupid mistake--I was typing this:

execute harmerd.FERPview harmerd.mar01
instead of this
execute harmerd.FERPview "harmerd.mar01"

I forgot the stupid double-quotes (I feel so foolish

Anyway, thanks for helping a newbie.

-Dan