Stored procedure with parameters

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

Stored procedure with parameters

Author

Topic

nicoart
Starting Member

11 Posts

Posted - 2006-12-06 : 23:35:18

Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?

Example, my stored procedure is:

create my_sp @inputvar varchar(12)
as
select @inputvar
go

then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter

next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt

is it possible to do that way? Or is there any other way to do so?

Thanks a bunch,
nicoart.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-07 : 00:56:17

quote:
Originally posted by nicoart

Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?

Example, my stored procedure is:

create my_sp @inputvar varchar(12)
as
select @inputvar
go

then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter

next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt

is it possible to do that way? Or is there any other way to do so?

Thanks a bunch,
nicoart.

Yes its possible to recieve input the way you did above, and yes you can execute your SP the way you did above

I dont think i understand the other questions

Afrika

nicoart
Starting Member

11 Posts

Posted - 2006-12-07 : 01:38:40

Hi Afrika,

Sorry to make you confusing.

OK, I will try to elaborate my questions.

1. A stored procedure is created, namely mysp:

create proc mysp @intparm varchar(12)
as
select @intparm

2. A .sql file is created, namely myscript.sql. It contains only one line:
exec mysp I should put value for @intparm here, shouldn't I? But I don't want to hardcoded the parameter, it should be getting the value from outside

3. A .cmd file is created. One of its lines is:
isqlw.... -i myscript.sql can I put value here, so it will be grabbed by exec mysp in myscript.sql and eventually will be passed it on to @intparm? -o output.txt

I hope I have made it clear...

Thanks very much,
nicoart

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-07 : 02:18:59

more like select @inputvar = 'some value'

about
2. yes you are correct exec mysp @intparm
3. I really dont know. Sorry

Afrika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 02:41:52

isqlw?

Don't you mean osql?

Peter Larsson
Helsingborg, Sweden

nicoart
Starting Member

11 Posts

Posted - 2006-12-07 : 02:56:07

I use isqlw, sorry, I am fairly new in SQL Server... I just found isqlw first from the manual while I was looking for the way to run sql scripts from command prompt and didn't look further... What is the difference between isqlw and osql?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:05:44

osql is a command line utility, while isqlw is a graphical tool for the T-SQL language.

Peter Larsson
Helsingborg, Sweden

nicoart
Starting Member

11 Posts

Posted - 2006-12-07 : 22:20:49

Thanks Peter. I have made experiment with osql and yes, I succeeded manipulating osql command that is saved in .cmd file.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-08 : 04:23:20

mind sharing your script ?