Substitution Variables to Temporarily Store Value

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

General SQL Server Forums

New to SQL Server Programming

Substitution Variables to Temporarily Store Value

Author

Topic

marjan.sayyad
Starting Member

15 Posts

Posted - 2013-12-13 : 18:53:32

Hi Everybody,
I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but can somebody tell me the equivalent query in sql server?
In oracle query is like this:
select *
from tablename
where deptnumber = &department_number;
Enter Value for department_number

I am looking this query in sql serevr2008.

More appreciated,

Mp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-14 : 00:15:07

[code]
select *
from tablename
where deptnumber = @department_number;
[/code]
declare @department_number as a parameter and use

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

marjan.sayyad
Starting Member

15 Posts

Posted - 2013-12-16 : 12:42:12

Thanks for your answer.I will explain a little more about my problem in the Myscript.sql query:
I am using sqlcmd in command line:
sqlcmd -U username -d dbname -i c:\mypath\myscript.sql -o c:\mypath\Myoutput.txt ( which this works )

Myscripy.sql :
declare @id_number int = 100
select * from tablename
where id = @id_number

My problem is in this query, which it does not give me a situation to enter id number from outside of the query like:
Enter Value for id_number :
because I want to enter the value of id_number from outside of query.
Again thank you for your attention to this matter, Marjan

Mp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 13:21:57

http://technet.microsoft.com/en-us/library/ms188714.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

marjan.sayyad
Starting Member

15 Posts

Posted - 2013-12-16 : 16:23:03

Thanks for the link, but My question is not changing the coulmn name , I want to be able to enter data for column "id" each time, when the user run this command . Actually I put this sqlcmd command in batch file. when the user run this batch file, it suppose to ask for id_number. so, in the where clase , each time id has a diffrent value.
We can do this in oracle like :( select * from tablename where id = &id_number ) which after getting the message "Enter the Value for id_number: " user enter the new parameter. I am looking a query like this with sqlcmd in sql server. Thanks for any suggestion

Mp

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-17 : 02:32:21

i think you confused the way how SQL Server react with Oracle/DB2. As what i know, either SSMS or sqlcmd does not support what you plan to do.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-17 : 02:33:50

or you can refer to previous forum
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109247

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:28:13

quote:
Originally posted by marjan.sayyad

Thanks for the link, but My question is not changing the coulmn name , I want to be able to enter data for column "id" each time, when the user run this command . Actually I put this sqlcmd command in batch file. when the user run this batch file, it suppose to ask for id_number. so, in the where clase , each time id has a diffrent value.
We can do this in oracle like :( select * from tablename where id = &id_number ) which after getting the message "Enter the Value for id_number: " user enter the new parameter. I am looking a query like this with sqlcmd in sql server. Thanks for any suggestion

Mp

Sorry you cant make sql server prompt for values. for that you need to create a front end screen/form

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

marjan.sayyad
Starting Member

15 Posts

Posted - 2013-12-17 : 12:01:03

Thanks for your great solution. Could you give me a good link to do that in sql please?
More Appreciated

quote:
Originally posted by visakh16

quote:
Originally posted by marjan.sayyad

Thanks for the link, but My question is not changing the coulmn name , I want to be able to enter data for column "id" each time, when the user run this command . Actually I put this sqlcmd command in batch file. when the user run this batch file, it suppose to ask for id_number. so, in the where clase , each time id has a diffrent value.
We can do this in oracle like :( select * from tablename where id = &id_number ) which after getting the message "Enter the Value for id_number: " user enter the new parameter. I am looking a query like this with sqlcmd in sql server. Thanks for any suggestion

Mp

Sorry you cant make sql server prompt for values. for that you need to create a front end screen/form

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:35:24

you cant do that in sql. Thats what i told. You need to do it in any front end languages. Post it in some front end language forums like PHP,.NET etc and you'll get assistance.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

marjan.sayyad
Starting Member

15 Posts

Posted - 2013-12-18 : 18:20:26

Thanks , I am not familiar with PHP , .NET. That is way I needed good link to do that. Thanks

Mp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources