Prompt variables in SQLServer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Prompt variables in SQLServer

Author

Topic

powellbound
Starting Member

23 Posts

Posted - 2008-08-21 : 10:01:21

In Oracle, I can write something like this:

select * from any_table where field1 = '&&1';

Upon execution, sqlplus or other compliant clients will prompt me for the value of &&1 and then run the query.

How do I do something similar in ManagementStudio 2005?

TIA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 10:11:26

you don't.
you must do:
declare @yourParam int -- or other datatype
select @yourParam = 1 -- or value you need
select * from any_table where field1 = @yourParam;

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-21 : 10:14:41

Somethhing like this:

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Test] Script Date: 08/19/2008 18:14:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Test]
@FirstName varchar (30)
AS

BEGIN

SELECT USERID, FirstName, LastName FROM UserDetails Where FirstName IN (@FirstName)

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 10:17:55

quote:
Originally posted by harlingtonthewizard

Somethhing like this:

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Test] Script Date: 08/19/2008 18:14:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Test]
@FirstName varchar (30)
AS

BEGIN

SELECT USERID, FirstName, LastName FROM UserDetails Where FirstName IN (@FirstName)

END

Please note that this wont prompt you for value of parameter. you need to do that part in your front end app. what this does is to give results when you pass a value to it explicitly.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:22:51

Don't know if this will help you...

If you do have a stored proc you can (in management studio 2005) go to it in object explorer, right click and "execute" -- it will then bring up a dialogue box where you can set the parameters before executing

-------------
Charlie

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-21 : 10:25:43

Can I ask where I went wrong here. I ran this in Management Studio 2005 and it did prompt me to enter a value? As mentioned above.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 10:27:24

for stored procedures it will prompt for input values but not for random query. i guess you could hack something up with dynamic sql and sproc method but that's a bit ugly

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 10:38:20

quote:
Originally posted by harlingtonthewizard

Can I ask where I went wrong here. I ran this in Management Studio 2005 and it did prompt me to enter a value? As mentioned above.

I think OP means that if you run the script from query analyser (or whatever it's called these days in management studio).

If you do an EXEC <yourproc> it's not going to prompt you it's just going to complain about a missing parameter.

-------------
Charlie

powellbound
Starting Member

23 Posts

Posted - 2008-08-21 : 11:06:02

Well hell, what good is a variable in the query analyzer thing if I have to declare it up front every time. I might as well hard code the var. Grrr.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-21 : 11:18:52

quote:
Originally posted by powellbound

Well hell, what good is a variable in the query analyzer thing if I have to declare it up front every time. I might as well hard code the var. Grrr.

Bit of a paradigm shift eh? I can imagine if you are used to something else this might annoy you but I've never resented having to change a variable value declared at the top of a script that much.

All the best,

-------------
Charlie

powellbound
Starting Member

23 Posts

Posted - 2008-08-21 : 11:37:37

Yeah, but imagine how nice it would be if it prompted you at runtime to choose the firstname you are looking for. Run 1 look for Dave, run 2 look for Charlie, etc. Just run the query over and over. Or you can use a different type of var and you can set it up front so you are not bothered by the prompt.

I will get around it but it sure would be nice to have.

Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources