CODEWITHSUNDEEP
sql-server-2005t-sql
GurdeepS
GurdeepS

Reputation: 67223

Dynamic sql join in sql server

I have a relationship between two tables. The two tables PKs are int types.

In one table (UserS), I need to supply the Username and get the corresponding ID (which is the PK). This is the standard ASP.NET user table when using forms authentication.

However, in a related table, I want to supply the ID I find from the Users table to get a value out.

Something like:

Run query to get ID for a username (simple select/where query) Return the result Run a subquery where I can pass in the above result - Get value where ID = result

This sounds a lot like dynamic sql. However, there might be a better suited and appropriate way of writing this query (on Sql Server 2k5).

How can I go about doing this and what gotchas lurk?

EDIT: I will try something along the lines of http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

EDIT: Thanks for the tips everyone, I wrote this:

SELECT Discount.DiscountAmount FROM Discount INNER JOIN aspnet_Users ON Discount.DiscountCode = aspnet_Users.UserId And aspnet_Users.Username = 's'

Where 's' is to be replaced by a parameter.

Thanks

Upvotes: 0

Views: 2048

Answers (6)

John Gietzen
John Gietzen

Reputation: 49544

You don't have to use dynamic SQL for that.

You can use a lookup instead:

DECLARE @ID bigint
SELECT @ID = ID FROM Users WHERE Username = @Username

SELECT
  *
FROM
  TableB
WHERE
  ID = @ID

Then, you could add the PARAMETER @Username to your SqlCommand object, preventing the risks of SQL Injection.

Also, doing the lookup is preferable to a join, since the index is scanned a single time, for TableB.

Upvotes: 2

cdonner
cdonner

Reputation: 37668

Regarding lookup vs joins - while it may seem more intuitive for the novice to use the lookup, you should go with a join. A lookup needs to be evaluated for every row in your primary result set, while a join is evaluated once. Joins are much more efficient, which means that they are faster.

Upvotes: 1

gbn
gbn

Reputation: 432271

SELECT
   *
FROM
   table2 t2
   JOIN
   UserS t1 ON t2.IDKey = t1.IDKey
WHERE
   UserS.Username = @Input

Upvotes: 0

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

Right, i just would do this:

SELECT *
FROM TableB
JOIN Users ON Users.Id = TableB.ID 
WHERE Users.Username = @Username

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425391

SELECT  values.value
FROM    form_users, values
WHERE   form_users.username = 'John Doe'
        AND values.user = form_users.userid

Upvotes: 0

David M
David M

Reputation: 72870

This is just a simple join isn't it?

SELECT  x.*
FROM    user_table u
        INNER JOIN
                other_table x
                ON u.id = x.user_id
WHERE   u.name = @user_name

Upvotes: 0

Related Questions