| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-17 : 09:02:45
|
| Chip writes "I am trying to execute a stored procedure as part of a SQL query's where clause.I have a stored procedure that you pass a IP, Subnet mask, and IP network and it validates (through some bitwise operations) that your current IP is valid on the network.Then the SQL quuery which looks something like this:SELECT u.UserIDFROM Users [u] INNER JOIN ... ...WHERE u.Username = @usernameI want to be able to put in something likeWHERE u.Username = @username AND (EXEC sp_IsValidIP(@ip, u.SubnetMask, u.IPNetwork)I was told I have to use dynamic SQL to do this but I can figure out how since most of the values passed to the stored procedure sp_IsValidUser are created while the query is running.Any ideas?Thanks so much!" |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2001-12-17 : 09:10:58
|
| Hello ChipIf you are using SQL 2000 I would recommend creating a function instead of a procedure. That should work. |
 |
|
|
tinks
Starting Member
34 Posts |
Posted - 2001-12-18 : 04:07:01
|
You could always run the sproc beforehand and put the result in a variable ... eg :declare @result varchar(25) /*whatever the result set val will be ret as.*//* assuming */EXEC @result sp_IsValidIP(@ip, u.SubnetMask, u.IPNetwork)...WHERE u.Username = @username AND (@result) Hope that helps  Taryn-Vee@>-'-,--- |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2001-12-18 : 04:27:25
|
| Chip, you can use OPENQUERY in your SELECT statement to execute your procedure. For this approach to work, you need to have a linked server pointing to your server, with 'data access' enabled. Also, your stored procedure must return a result set.Check out BOL. In fact, I typed an example for you and pressed "Post new reply", but since I forgot to enter my username and password, my post got rejected and I lost the original message that I typed. I don't understand, why I have to type my user name and password everytime.--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
|
|
|
hande
Starting Member
8 Posts |
Posted - 2001-12-18 : 07:13:26
|
| that's correct, but if you had BOL with you, you would have noticed that you need something else than procs. I had one 'colleqa' who was so 'hätä' (=hurry) that he did so much umpleasant work without any planning and then he came to ask from me...But it is ectremely helpful if you use jdbc -connection and appropriate j -styled class definitions. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-18 : 13:53:31
|
quote:
I don't understand, why I have to type my user name and password everytime.
Vyaskn, you shouldn't have to. Are you rejecting the SQLTeam cookie? (note IE 6 by default rejects a LOT of cookies unless you tell it otherwise). Did you click LOGOUT the last time you were here? It will kill your cookie. Otherwise, SQLTeam should see your cookie every time you come in and you don't have to enter your user name and password.HTH-------------------It's a SQL thing... |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2001-12-18 : 15:41:31
|
| Alright, I'm on IE 5.5 with Cookies enabled.But looks like, I have to explicitly login using the form at the top. Just typing username and password while answering a post is not enought for the site to remember me :)--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-18 : 15:49:44
|
| Hate to state the obvious, but did you check the checkbox for it to remember your password?Edited by - robvolk on 12/18/2001 15:50:20 |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2001-12-18 : 15:54:44
|
| Of course. But you didn't get what I was trying to say.I thuouht, typing the username and password in the answer form is enough for the forum to remember me. But that's not correct. I need to login explicitly using the form located at the top of the page.--HTH,VyasCheck out my SQL Server site @http://vyaskn.tripod.com |
|
|
|
|
Executing a stored procedure as part of a WHERE clause