declare variable by user in Sql server

Question

Is it possible to get input from a user for a variable?

For an example: How do i get the firstname of a user to use it in my script to select it from my employee table.

I now how i declare a variable but not how to get it typed in by the user of the script

Answer 1

  SELECT @YourVariable = FirstName
  FROM Employee
  WHERE Id = @SomeId

You can use sqlcmd http://msdn.microsoft.com/en-gb/library/ms162773.aspx, so you can specify variables in command line

sqlcmd -iyourscript.sql -v yourvar=yourvalue

and in script

 SELECT @YourVariable = FirstName
  FROM Employee
  WHERE Id = ${yourvar}

Answer 2

You can do something like what you're asking with stored procedures. Start them like:

CREATE PROCEDURE [dbo].[NameOfStoredProcedure]
(
   @cTableName      varchar(75),    -- name of the table
   @iTopLevelId         int = 0     -- Id parameter
)
AS
etc

Then when executing the stored procedure from Management Studio a dialog box will appear requesting the parameters.

Here's a walkthrough of the process: http://www.mssqltips.com/tip.asp?tip=1375