Question

In Oracle, you can write a statement that uses the "&" as a variable,

For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.

How do you do this in transact SQL for SQL Server?

Thanks

Lystrau have to pass as parameter in sql server


--eg:

create procedure sample_sp
(
@.Enter_Operator_Name as varchar(100)
)
as

select * from tbl_Stats where operator = @.Enter_Operator_Name|||For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.
Dunno much about Oracle - but this sounds rather like Access which also is an integrated UI & dbms.

The gist with SQL Server is that the app will have to get the parameter value itself and then pass this to the sproc (a la mallier's answer).|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?

T-SQL is pretty different from Oracle with respect to handling variables. In T-SQL, you are going to end up having to do something like:

DECLARE @.MyVar int

SELECT @.MyVar = 100

SELECT * FROM tblCustomers WHERE CustID = @.MyVar

At runtime, you will have to replace the '= 100' with a value that you supply. Using SQLPlus, I know that you can do things from the command line calling files and replacing variables on the fly with parameters that you supply from the command line. This is doable in SQL Server as well, but you will need to use osql instead of the Query Analyzer to achieve the same effect (and even then I don't know exactly how to do it since I am not an osql guru).

Regards,

hmscott

Edit to add: You said "Procedure". Is this a stored proc you are trying to create? If so, in your stored proce declaration you can do this:

CREATE PROC spGetCustomer (@.CustID int)

AS

SET NOCOUNT ON

SELECT * FROM tblCustomer WHERE CustID = @.CustID|||SQL*Plus is an environment tool as well as a script execution tool. You can compare it to Microsoft Access in that respect, in that both allow easy integration between the UI and the database engine.

A pure database scripting tool can't (and shouldn't) support user interaction. It needs to execute scripts, and only execute scripts. If the data isn't coded into the script, you really need a different tool (such as VBS or Microsoft Access) to properly implement that functionality.

-PatP