Store Procedure Parameters - 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 2000 Forums

SQL Server Development (2000)

Store Procedure Parameters

Author

Topic

arturos
Starting Member

7 Posts

Posted - 2006-10-18 : 10:30:36

Using VBNet I built a string like this "Select * from table where option = 1 and option2 = 2". I want to pass this string to a Store Procedure using tipical parameters but I don't know how to excute this statement into Store Procedure.

Please help me..

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-18 : 10:37:23

create procedure spname(@option1 int, @option2 int)
as

select * from tableA where option1=@option1 and option2=@option2

to execute call the sp or create an object in .net, search the internet for sample codes

HTH

--------------------
keeping it simple...

arturos
Starting Member

7 Posts

Posted - 2006-10-18 : 10:53:41

Tks HTH but, I want to use this string that I built in vbnet code into the Store Procedure passing it as a parameter and execute from Store Procedure

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-18 : 11:02:47

define this string you are talking about... is it part of the query like a where clause or an order by, or heaven forbid, dynamically choosing which table to select from?

--------------------
keeping it simple...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 11:29:47

quote:
but I don't know how to excute this statement into Store Procedure.

Inside the stored procedure, execute the string with the EXECUTE() statement or the sp_executesql stored procedure, you can read up and find examples of both in Books Online.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 11:33:02

And then once you've figured out how to execute that SQL with EXECUTE() or sp_executesql, make sure you read up on SQL Injection!
http://www.sqlteam.com/redir.asp?ItemID=8406

arturos
Starting Member

7 Posts

Posted - 2006-10-18 : 15:27:17

Thanks a lot to everybody, using sp_executesql I solved my problems.
Regards

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-19 : 07:19:00

quote:
Tks HTH but, I want to use this string that I built in vbnet code into the Store Procedure passing it as a parameter and execute from Store Procedure

You are using dynamic SQL when you dont have to. Horrible, terrible, abysmal programming!!!!

Yes, its easy to build the string you need in your front end, but that does NOT make it the right way to do it!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-10-19 : 09:12:41

pass this string to ur stored procedure and execute it.

like.

EXEC (@stsParameter)

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-21 : 07:13:55

quote:
Originally posted by arturos

Tks HTH but, I want to use this string that I built in vbnet code into the Store Procedure passing it as a parameter and execute from Store Procedure

Passing entire sql statement is too bad
Read this fully

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources