SPROC and VIEW, variable problem

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 2000 Forums

Transact-SQL (2000)

SPROC and VIEW, variable problem

Author

Topic

kloepper
Yak Posting Veteran

77 Posts

Posted - 2003-12-16 : 09:40:51

I have a SPROC that uses various VIEWs (passed into the SPROC from an ASP.NET dropdownlist), but I'm getting "Invalid Syntax near 'View name' " errors from the browser (IE) when I execute the dropdownlist.

It appears to me that it should work. The VIEWs run fine by themselves.

When I run the SPROC in Query Analyzer, I get the following error

Server: Msg 201, Level 16, State 4, Procedure Company_List_by_Filter, Line 0
Procedure 'Company_List_by_Filter' expects parameter '@Filter', which was not supplied.

I completely removed the control and rebuilt it (Using VisualStudio.NET 2003), but still have the same error.

VS builds the code without error.

Here's the code for the event handler:

private void Select_Filter_SelectedIndexChanged(object sender, System.EventArgs e)
{
SqlConnection myConnection = new SqlConnection("server=AMD;database=imSMART;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter("Company_List_by_Filter", myConnection);

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@Filter", SqlDbType.VarChar, 150));
myCommand.SelectCommand.Parameters["@Filter"].Value = Select_Filter.SelectedValue;

//Arbitrary table name Company_List
DataSet ds = new DataSet();
myCommand.Fill(ds, "Company_List");

Select_Company.DataSource=ds.Tables["Company_List"].DefaultView;
Select_Company.DataBind();
}
------------------------------------------------------------------
My code for my ASP.NET page has a variable named @Filter. The variable contains the name of a VIEW. This variable gets passed to the SPROC. This sproc is seen below. There are no varables in the VIEW. Unfortunately, the sproc does not seem to be finding the variable and I can't find why.

CREATE PROCEDURE Company_List_by_Filter (@Filter VARCHAR(150)) AS

DECLARE @sql VARCHAR(255)

SET @sql = 'SELECT Name FROM '''+ @Filter +''' '

EXEC (@sql)

return
GO

Anyone have any ideas why this is happening?

Thanks,

Paul

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-16 : 12:33:18

I think that your stored proc should say this:

SET @sql = 'SELECT Name FROM ' + @Filter

instead of what you have.

Tara

kloepper
Yak Posting Veteran

77 Posts

Posted - 2003-12-16 : 20:24:46

quote:
Originally posted by tduggan

I think that your stored proc should say this:

SET @sql = 'SELECT Name FROM ' + @Filter

instead of what you have.

Tara

Yes, you must be a Princess because you are the only person in 2 days of trying that has come up with a working solution to this problem which has been bugging me for over a week

Thanks Much,

Paul

May your Tiara never lose its sparkle!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources