Hi! I would be grateful for any advise regarding what I'm doing wrong.. My
brain is stuck. Probably some stupid simple mistake I don't see. Thanks very
much for your efforts!

Martin

I have this code:

DECLARE
@ContactID varchar(10),
@AddressID int,
@cmdSQL varchar(500)

Set @ContactID = '12'

SET @cmdSQL = 'SELECT AddressID FROM i2b_ash_contact WHERE ContactID = ' +
@ContactID

EXEC (@cmdSQL)

But how do I store the value returnd by EXEC (@cmdSQL) in the variable
@AddressID????

Tried several things unsucessfully e.g.:

EXEC @AddressID = @cmdSQL
SET @ AddressID = EXEC(@cmdSQL)

Am I really so stupid that I can't see what I'm doing wrong???

[posted and mailed, please reply in news]

Martin Feuersteiner (theintrepidfox@hotmail.com) writes:
[quoted text, click to view]

The first question is: why use dynamic SQL at all for the above?

SELECT @adrid = AddressID FROM i2b_ash_contact WHERE ContactID = @ContactID

is the simple way.

In case you really need to use dynamic SQL, sp_executesql is the way
to go. But there is a fair chance that even if your actual problem is
more complex, that you should not be using dynamic SQL at all.

Anyway, if you want to learn more about dynamic SQL, I have an article
on my web site, http://www.sommarskog.se/dynamic_sql.html. If you are
in a hurry, the solution to your problem is at
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

You don't need EXEC to do this.

SET @AddressID =
(SELECT AddressID
FROM i2b_ash_contact
WHERE ContactID = @ContactID)

--
David Portas
------------
Please reply only to the newsgroup
--

[quoted text, click to view]

Thanks everyone for replying. Well, I haven't mentioned that my table name
is dynamic, something like:

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)

SET @Table = 'i2b_ + @ProgClient + '_contact

SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)


I need to evaluate @Table first otherwise it can't find it.

[quoted text, click to view]

Unless you make it just one table and put the Progclient value in as a
column. Seems like an unusual design to have multiple tables of Contact
details.

CREATE TABLE Contacts (progclient VARCHAR(10), contactid INTEGER, addressid
INTEGER NOT NULL, ... PRIMARY KEY (progclient, contactid))

SET @AddressID =
(SELECT AddressID
FROM Contacts
WHERE ContactID = @ContactID
AND progclient = @ProgClient)

--
David Portas
------------
Please reply only to the newsgroup
--

Thanks for your help Dave.
I've to admit, it's an unusual design but I've multiple contact tables named
e.g. i2b_ash_contact or i2b_ted_contact.
i2b_ and _contact are static but the middle part is dynamic. Reason for this
design is that I've a web application in which someone logs in and depending
on the login, a specific table for this login is used.
Storing all contacts in one table with an identifier of e.g. ash ted for
each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.

So here we are again at the beginning. How can I do something like:

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)

-- @Prog is e.g. 'ash'

SET @Table = 'i2b_ + @ProgClient + '_contact

SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)





[quoted text, click to view]

[quoted text, click to view]

Row-level security:

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

--
David Portas
------------
Please reply only to the newsgroup
--

Thanks guys for answering my question.
I've spent some time on Erland's site and came to the conclusion that
I better follow a wise man's advice!

Have a nice day!

[quoted text, click to view]