Passing a User-Parameter in a View or SQL Statement

[chouna]

How do I create a view or a SQL statement that will support a user-supplied parameter? When I run the view or the SQL, it should pop up a window that will ask me to provide the parameter. In MS Query, I can do it this way:

Select * from Salesman WHERE Salesman = ?

How is this done in SQL Server?

 

[SQLSister]

It isn't. View do not allow parameters at all. Stored procedures can have parameters, but you have to collect the information for the parameters and include it in the execute statement.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[chouna]

SQLSister,

Thanks for the clarification. Is there no way other than SP for someone like me who is not really familiar with SQL Server? Basically, I work on Excel picking up external data using MS Query. I have been asked to come up with a pivot report that picks data from different SQL Server tables. I thought making different views will render it easier but then I am faced with the dilemma of fetching more than a million records which makes Excel crawl. I was hoping there would be a facility to filter the view before Excel loads it.

If it is only through Stored Procedures, can you direct me to any site or FAQ in this forum that explains the basics of SP to dummies?

 

[chosse]

You can see the help in SQL server or see the internal stored procedure in master db for inspiration.

 

[SQLBill]

Check the BOL for information on Stored Procedures.

Also, SQL Server doesn't have/isn't a 'front-end' application. So, it won't provide 'pop up' windows for you to input data. The variables/parameters have to be inputted via commands. For example, you would run a SP without a variable/parameter as:
EXEC mysp
But with a variable/parameter, you would use:
EXEC mysp 'myparameter'

MS Access and MS Excel are examples of combined front-ends and back-ends. You could use Excel to ask for and pass the parameter to SQL Server. I don't use Excel this way, so I can't help you with the actual method. You should be able to create a SP on SQL Server and have Excel 'run' that SP.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875