Using a variable in a STORED PROCEDURE

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)

Using a variable in a STORED PROCEDURE

Author

Topic

bartman_z
Starting Member

11 Posts

Posted - 2005-04-15 : 12:33:44

Hi there,

I am writing a stored procedure where a build up a variable that I would like to use in a SELECT statement later on.

e.g.
Declare MySQLText varchar(200)
@MySQLText = "SELECT UserID FROM USERS WHERE TOWN='My Town'"

Then later on in the procedure I want to say

SELECT * FROM SomeTable WHERE UserID IN(@MySQLText)

However I get an error back saying that it could not convert the varchar value to a column of data type int

Any ideas on how I can do this ?

Many thanks in advance.

www.meridiantd.co.uk

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-15 : 13:48:22

http://www.sqlteam.com/item.asp?ItemID=11499

Be sure to check out the comments of that article.

Tara

bartman_z
Starting Member

11 Posts

Posted - 2005-04-16 : 07:41:31

Tara,

Thanks for that response which is interesting, but the problem is that my @MySQLText variable HAS to be a select statement because the list of valuus would be too huge.

Any other suggestions welcome

Bart

www.meridiantd.co.uk

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-16 : 07:54:33

then your only option is dynamic sql.

Go with the flow & have fun! Else fight the flow

bartman_z
Starting Member

11 Posts

Posted - 2005-04-16 : 07:55:55

Spirit,

Thanks, but does anyone have any pointers of how to do it with Dynamic SQL ?

I am relatively new to the whole Stored Procedure thing

Regards

Bart

www.meridiantd.co.uk

bartman_z
Starting Member

11 Posts

Posted - 2005-04-16 : 07:59:15

Don't worry I see what you mean, i.e. write the SQL statement in the host application.

Thanks again for the help

Bart

www.meridiantd.co.uk

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-18 : 12:40:43

quote:
Originally posted by bartman_z

Tara,

Thanks for that response which is interesting, but the problem is that my @MySQLText variable HAS to be a select statement because the list of valuus would be too huge.

How big though? Since you are new to SQL, I wouldn't recommend starting to learn the bad way to do things, which in this case is dynamic SQL.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-18 : 12:52:03

you are taking the wrong approach. instead of:

>>Declare MySQLText varchar(200)
>>@MySQLText = "SELECT UserID FROM USERS WHERE TOWN='My Town'"

your stored procdure should accept a Town argument:

CREATE PROCEDURE Example (@Town varchar(100))
AS
BEGIN
SELECT * FROM YourTable WHERE Town = @Town
END

and so on ... take the parameters that need to be passed in as a SELECT and incorporate them into your stored procedure. It is really bad practice for a stored proc to accept SQL statements as parameters.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources