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 Suessmeyerhttp://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 :-)