Stupid Problem - Please help

[Martin Feuersteiner]

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???

[Erland Sommarskog]

[posted and mailed, please reply in news]

Martin Feuersteiner (th************ @hotmail.com) writes:

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)

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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

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
--

[Martin Feuersteiner]

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:fN******** ************@gi ganews.com...

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
--

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.

[David Portas]

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

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
--

[Martin Feuersteiner]

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)

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:V9******** ************@gi ganews.com...

I need to evaluate @Table first otherwise it can't find it.
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
--

[David Portas]

> each record is not possible as from a security point, I need to keep

contacts for each login in a separate table.

Row-level security:

http://vyaskn.tripod.com/row_level_s..._databases.htm

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

[Martin]

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!

"David Portas" <RE************ *************** *@acm.org> wrote in message news:<nJ******* *************@g iganews.com>...

each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.

Row-level security:

http://vyaskn.tripod.com/row_level_s..._databases.htm