Help understanding stored procs

I am having trouble understanding how to correctly use stored procs. Here is what I think the process is. Correct me where I am wrong.

I am developing a read-only program with VB 2005 as a front end and SQL Server back end. The user will enter an ID# for search criteria which will be passed as a parameter to a stored proc. I want to create a stored proc that alters a view by changing the ID# the view filters on. A dataset will be created in the front end from that view.

So in SSMS, I create a new proc like this:

CREATE PROC {blah, blah}

@.IDnum

AS

BEGIN

ALTER VIEW {blah, blah}

AS

SELECT {blah, blah}

FROM {blah}

WHERE blah.ID = @.IDnum

END

I would run the code to create the proc, then modify it to an ALTER PROC. I've tried this without success. What am I missing. Yes, I am new to this.

Thanks for the help

Hi,

why do you want to alter a view, rather than using a filter on the view (although the view isn′t that column rich :-) )

So you could create the View as

CREATE VIEW SomeView
AS

SELECT col1, col2 From SomeTable

and then select using

SELECT * FROM SomeView Where Col1 = 'SomeId'

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. I am entering the world of SQL Server from an Access background. In Access I often change the filtering of a query by changing the sql with code or by referencing a field on a form from the criteria field of a query.

That is the thinking with which I have approached views. The purpose of my post is to check my thinking against the experienced community and hopefully get better ideas and understanding. Your reply helps. Thanks again.

|||You′re welcome :-)